Absolute References vs. Relative Addressing

Absolute References vs. Relative Addressing

The terms Absolute Reference and Relative Addressing refer mainly to the way formulas and functions work when used with Paste Fill – to repeat a formula down a column, for example – or when copied and pasted from one cell to another.

This invoice worksheet will demonstrate both quite nicely, showing how the default process – using relative addressing to automatically update cell addresses as a formula is pasted down a column – can also be controlled by the user, applying an absolute reference to part of the formula, so that not all of the cell addresses in the formula update when pasted.

To demonstrate, let’s look at the invoice.

We have 5 items purchased, and the unit price and quantity purchased are already in place. The first thing we need to do is calculate the total for each line item, multiplying the quantity purchased by the unit price. This will be a perfect example of the default, known as Relative Addressing, wherein each iteration of the formula is updated to reflect its new location as we paste down 4 rows after performing the first calculation.

In other words, the cell addresses in each repetition of the formula update relative to their new location – thus the term, Relative Addressing.

---

So, in the first formula, we’re multiplying cell D10 times cell E10. Simple enough.

When I paste fill that down the column, the second line item’s formula multiplies D11 times E11, and the third multiplies D12 times E12, and so on. As the formula moves down a row, the row numbers automatically update. The same would happen if we were pasting the formula across a row – but the column letters would update instead.

Relative Addressing is the default, of course, because it’s what we want to happen most of the time as we use Paste Fill to repeat a formula or function in a paste-filled series or when copying from place to place.

But when we don’t want that to happen, that’s when Absolute References kick in.

To calculate the tax, I’m going to reference a specific cell, which contains the sales tax percentage, and tell Excel to use that cell in every iteration of the formula when we paste it down the column for each line item. It will still take the purchase total for each item and update that reference, but the tax calculation will always go back to the one cell I specify.

Here’s how that’s done:

---

When I click in cell G10, I type = and then click on cell F10, and type an asterisk. I want Excel to multiple the total for the item by the tax percentage, so then I click on cell G2, where 8%, or.08, is stored.

To make this an absolute reference – so each repetition of the formula goes back to that one cell, I press the F4 key on the keyboard. This places dollar signs in front of both parts of the cell address – the G and the 2. You can also type the dollar signs manually, but obviously, the F4 key is much faster.

Now, that cell is the Absolute Reference in the formula.

As I paste fill it down the column, each item is multiplied by.08, and because I used a cell reference, rather than typing.08 into the formula, all I have to do is update cell G2 to change the tax percentage for each item, with just that one edit.

Imagine the uses for all sorts of things – commission rates, fees, other types of taxes, price increases – any time you want to reference one particular cell all the time in a formula you copy to multiple locations, Absolute References take Relative Addressing and make it even more convenient and efficient!