Just what is the difference between an absolute and a relative cell reference in Excel?.

A relative cell reference is the most widely used cell reference in formulas in Excel. These are basic cell references that adjust and change when copied or using the Auto fill function in Excel. For Example if the position of the cell that contains the formula changes, the reference is changed.

Let’s look at an Example .

See the two screen shots below

The cell reference of the above formula =SUM(B4:B7) when dragged across to column C adjusts itself to then sum the results of C4 to C7.

That’s all pretty straightforward, and once you get used to absolute cell referencing that will be too, let’s jump in.

An Absolute cell reference is indicated in your spreadsheet by a $ sign around the cell, for example =$A$1 means that when referring to this cell is is ALWAYS in that location. If the position of the cell that contains the formula changes, the absolute cell reference remains the same. If you copy the formula across rows or down columns, the absolute cell reference remains the same unlike the example above. Let’s take a look.

Lets say you are calculating commissions for sales staff of the Widgets as in the above example. We know that the commission is 5% of their sales- ALWAYS. So you do not want the cell to change when you auto fill your formula to the rest of the sales team.

Nice huh? In the example below, the cell reference B26 will always be used for the % sales commission even if you drag the formula using Auto fill.

See how the B26 reference remains the same even after auto fill.

Ok let’s turn up the volume on this and really make it work for us.

Lets say that there are two types of discount for our widgets Discount Plan 1 and Discount Plan 2 and we want to know the pricing for the different widgets with the two plans.

Look closely at the formula in cell C4 in the example below:-

By making the first cell reference $B4, you keep the column from changing when copied across, but allow the row to change when copying down to accommodate the prices of the different items going down.

By making the last cell reference A$10, you keep the row number from changing when copied down, but allow the column to change and reflect discount B when copied across

The rest of the formula is straight forward enough with the resultant cell displaying the price of each of the Widget products with their discount already subtracted.

Here’s a summary of the available absolute cell references

$A1

Allows the row reference to change, but not the column reference.

A$1

Allows the column reference to change, but not the row reference.

$A$1

Allows neither the column nor the row reference to change- this is the default one.

There is a really quick shortcut available to toggle through these functions, just keep tapping the F4 key…give it a try so as you know the fist tap of F4 gives the usual or default absolute cell reference.