A handy shortcut that switches between relative and absolute references

You can specify relative and absolute references manually... but why not let Excel do it for you?

You probably know the difference between a relative and an absolute cell reference:
  • A relative reference adjusts when you copy the formula.
  • An absolute reference never changes.
In other words, if you copy the relative formula =A1 to cell B1, Excel updates the reference to reflect the move one column to the right and enters the formula =B1. Copy the formula in cell A1 to A2, and it updates the row reference to =A2. On the other hand, copy the absolute formula =$A$1 to cell B1 or A2 and Excel inserts the same formula, =$A$1. When entering formulas, you can specify an absolute row or column by manually typing the $ sign. An easier way is to let Excel do it for you by pressing F4. Enter the formula using relative references (don't enter the $ signs). Then, press F4 to cycle through the different forms: =A1 =$A$1 =A$1 =$A1 If the formula contains more than one cell reference (and most do), click in the formula bar inside the cell reference you want to update. You can quickly work your way through the most complex formulas this way.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks