Copy Formulas without changing the cell references

Normally when you copy formulas in Excel the cell reference in the formula will change.

This applies to formulas with relative references (e.g. B2). If the formula has absolute references (e.g. $B$2), these will not change. In formulas with mixed references (e.g. $B2) , the absolute part will not change and the relative part will change.

The change is based on how many rows or columns you move the formula.

To avoid changing the cell references in formulas follow these steps.

Select the range that you want to copy.

Press Ctrl + H

Search for the = at the start of the formula and replace it with =equals

This will cause Excel to show an error of #NAME? for the formulas.

Copy the selection and paste it into a new location.

Press Ctrl + H

Search for the =equals at the start of the formula and replace it with =

Repeat the find and replace for the range you copied.