Move Relative Formulas Without Changing
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 rownumber of the cell reference, as in $A$1 (or you can use the F4 key to toggle through the different types of reference style). 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 by following these steps:
- Select the range of cells you want to copy.
- Go to the Home tab and choose Editing → Find & Select → Replace (pre-2007, Edit → Replace...).
- In the Find What: box, type = (an equals sign).
- In the Replace With: box, type & (an ampersand), or any other symbol you are sure is not being used in any of the formulas.
- Make sure the "Match Entire cell contents" option is not enabled, and click Replace All.
- All the formulas will appear on your worksheet with an & in place of an =, so you can now copy your cells to any locations you wish.
- After moving the cells, select Home → Editing → Find & Select → Replace (pre-2007, Edit → Replace...). This time, replace the & with an =.
When you're done, your formulas will reference the same cell references as the originals.
