What is Absolute Referencing in Excel?

Sep 18, 2018 | Excel Hints and Tips

We are talking about calculations here and how they behave when you autofill them.  Before we move on to absolute referencing it’s worth going back to basics with relative referencing.

Relative Referencing

Normally when you create a calculation in Excel you want it so that if you autofill down the cell references move down and if you autofill across the cell references move across.  This also applies to copying and moving cells.  This moving of the cells is called relative referencing or relative addressing.

For example, if I have the calculation =B2*C2 in cell D2 and autofill it down the cell references move in relation to the direction we are autoflling to become = B3*C3

relative reference

Absolute Referencing

Sometimes you need the calculations to behave a little differently when you autofill so the cells stay fixed and do not move.  This is called absolute referencing or absolute addressing.  This is done by using dollar signs.  The dollar goes before the direction it needs to fix.  A dollar before the letter fixes the column and a dollar before the number fixes the row.

For example, if I have the calculation =D2*$G$1 in cell E2 and autofill it down the D2 will move relative as before and the $G$1 will stay fixed becoming =D3*$G$1.  This is often useful for percentage increases when the percentage is stored in an individual cell but is also used in a range of functions to stop ranges of data moving.

absolute reference

Mixed Referencing

Les frequently used, but no less useful if required is mixed referencing.  This is a mixture of fixing the row but not the column or vice versa

For example, with the calculation =$B8*C$6 when it is autofilled down becomes =$B9*C$6. In $B8 the column is fixed but not the row so autofilling down the row number moves while C$6 the row is fixed so it stays on C$6.  In contrast, if we autofill across $B8 will not move as the column is fixed but c$6 will because the column is not fixed becoming =$B8*D$6

Mixed Reference

Great Shortcut!

Now we all love a good shortcut and there is a cracker here that toggles between the 4 options above.  If we are editing the calculation the F4 Key will apply firstly absolute referencing, then will fix the row, then the column before return to relative referencing for every subsequent time it is pressed.  This works for individual cells if the cursor is clicked in the cell reference but can be applied to the whole calculation if that is selected instead.  This is far quicker and less fiddly than typing the dollar signs in.

Want to see how this can be used effectively in functions then contact me at lara@laramellortraining.co.uk to discuss your needs.