Excel Class Blog – Free Excel Tutorial – Excel Relative versus Absolute References

One of the most commonly asked questions about Excel, even at the advanced level, is what is an absolute reference, when do I need it?

As you work in Excel, for the most part, you’ll be fine using relative references. Relative references are references to cells that adjust relative to the direction of the movement of the formula. If you copy and paste or autofill your formula elsewhere, relative references will adjust relative to the direction of the movement.

In our first example, we want to add our USA expenses in cell B15. When we use the autosum function (keyboard shortcut is Alt = ) we create a formula

=SUM(B7:B14)

The references to B7 and B14 are both relative. When we copy the formula from column b (US) to column c (Canada) and column d (Europe), the cell references will change to C7:C14 and D7:D14

In row 16 we’ll calculate the flat rate tax. Rather than type the 10% tax rate into each formula, it’s more efficient to reference it in a single cell. By referring to the tax in a single cell, if we change the tax rate once, all the formulas that use that value will update accordingly.

In cell B16 we want a formula that multiplies the subtotal in B15 by the tax in B4. We begin a formula by typing an = symbol, then click on B15, type an asterisk (*) for the multiplication symbol, then click on B4 (but don’t press enter yet).

=B15 * B4

When we copy the formula from B16 (US) to column C and D, we don’t want the reference to cell B4 to change to C4 and D4.

To convert a relative reference to an absolute reference press the F4 function key (Windows) or Command+t (Macintosh). Note the B4 reference changes to $B$4 (note that the dollar sign always appears to the left of the column or row reference; you could type the $ symbol as well, but F4 is easier in my opinion.

To practice when to use relative or absolute references, download a practice file here;. In order to use this file you will need to enable macros or lower your macro security.

Please feel free to repost this with credit to my Excel Class blog, or just link in.

You will also find this tutorial at our VBA Class Blog