A Tip for Quickly Switching Between Relative and Absolute Cell References in Microsoft Excel

When you create a cell reference in an Excel formula that refers to another cell, that cell reference can be relative (the default) or absolute. A relative cell reference adjusts to its new location when the formula is copied or moved. An absolute cell reference does not change when the formula is moved.

Consider this example: Starting in A1 you have a 3×3 table with some figures in it. Cell D1 contains this formula: =A1+(B1*C1). If copy this formula to cell D2, the cell references will change relative to the new location and the formula will automatically change to =A2+(B2*C2).

Now consider this example where you have a constant in a formula: You have a column of costs in Canadian dollars and you want to add a second column that gives the US dollar equivalent. And in particular you want to set it up so you can adjust the US conversion rate in one location on the sheet so you don’t have to do it multiple times if the exchange rate changes.

So in the first column you have your $Cdn figures (A1, A2, A3…). Your conversion rate is in cell D1 (e.g., 1.10). In cell B1 at the top of the second column you have this formula: =A1*$D$1. The $ in front of the row and column references indicates an absolute cell reference. So if you copy the formula in B1 to B2, it will become =A2*$D$1. The first part of the formula is relative and is changed, but the second part is absolute and did not change (if it had done so, it would have referred to the cell below the cell with your conversion rate.

Absolute and relative cell references can twist your brain, but they are real handy in the right circumstances. Note that you can also have a cell reference in a formula that is a mix relative and absolute. Will save an example of that one for another day.

So recognizing that a $ sign is used to indicated an absolute row or column reference, here are some examples of relative and absolute cell references:

• A1 (relative column and relative row)
• $A$1 (absolute column and absolute row)
• A$1 (relative column and absolute row)
• $A1 (absolute column and relative row)

And now for the tip. It should be obvious that typing $ signs in one or more cell references in a formula will be tedious and error prone. The brilliant programmers at Microsoft save you a bunch of grief with some keyboard shortcuts: Click on a cell references in a formula and press F4 on a PC, or Command+T on a Mac, to cycle through the 4 possible relative and absolute combinations for that cell reference. Very helpful indeed!