Microsoft Excel

Move Relative Formulas Without Changing References

In Excel, a formula reference can be either relative or absolute. Sometimes, however, you might want to reproduce the same formulas somewhere else in your worksheet or workbook, or on another sheet.

When a formula needs to be made absolute, type $ (a dollar sign) in front of the column letter and/or row number of the cell reference, as in $A$1. Once you do this, no matter where you copy your formula, it will reference the same cells.

Sometimes, however, you might set up a lot of formulas that contain not absolute references, but relative references. You would usually do this so that when you copy the original cell formula down or across, the row and column references change accordingly.

Yet other times you might set up your formulas using a mix of relative and absolute references, and you want to reproduce the same formulas in another range on the same worksheet, another sheet in the same workbook, or perhaps another sheet in another workbook. You can do all these things without changing any range references inside the formulas.

Select the range of cells you want to copy. Select Edit » Replace..., and in the Find What: box, type = (an equals sign). In the Replace With: box, type @ (an at sign), or any other symbol you are sure is not being used in any of the formulas. Click Replace All.

All the formulas will appear on your worksheet with an @ in place of an =.

Now you can simply copy this range, paste it to the destination you desire, select the range, and select Edit » Replace.... This time, replace the @ with an =. Your formulas will reference the same cell references as your originals.