Excel's Relative Reference Handler

In Excel, a formula reference can be either relative or absolute, but sometimes you want to move cells that use relative references without making the references absolute. Here's how.

When a formula needs to be made absolute, you use the dollar sign ($) in front of the column letter and/or rownumber of the cell reference, as in $A$1. Once you do this, no matter where you copy your formula, the formula will reference the same cells. Sometimes, however, you may have already 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.

If you already set up your formulas using only relative references, or perhaps a mix of relative and absolute references, you can reproduce the same formulas in another range on the same worksheet, another sheet in the same workbook, or perhaps even another sheet in a different workbook.

To do this without changing any range references inside the formulas, select the range of cells you want to copy and then select the Home tab → Find & Select → Replace (pre-2007, Edit → Replace...). In the Find What: box, type an equals sign (=) and in the Replace With: box, type an ampersand (&). (Of course, these could be any symbols you are sure are not being used in any of the formulas.) Click Replace All. The equals sign in all the formulas on your worksheet will be replaced with the ampersand sign.

You can nowsimply copy this range, paste it to its desired destination, select the range you just pasted, and select the Home tab → Find & Select → Replace (pre-2007, Edit → Replace...). This time replace the ampersand sign with an equals sign (don't forget to do this with the source range you just copied). Your formulas nowshould be referencing the same cell references as your originals.