Excel Absolute References $ – The Missing Link

Once you uncover the mystery of Absolute References copying and pasting formulas becomes child’s play.

You might have seen cell references in formulas surrounded by ‘$’ signs. For example $D$3:$D$10.  What’s that all about?

Well, the ‘$’ before the column or row reference instructs excel to keep the reference absolute.  Huh, I hear you say.  Ok, I’ll explain in English.

Looking at the table below we have a Commission Rate of 3% in cell G3.  In column E we want to calculate the commission as Total $k x Commission Rate 3%.

I could simply enter the formula as =D3*3% and copy it down column E, but there are two problems with this that make it not ideal:

1)      I can’t easily see what the Commission Rate is without looking in the formula bar.  I could put it as part of the column heading e.g. ‘Commission $ @ 3%’, but that makes my heading bigger than it needs to be, and if I change the rate at a later date I have to go back and change the heading too, which is easily overlooked.

2)      If I change the rate I need to change the formula and copy it down the column again. Ok, so it might only take a few seconds to do this, but if you’re playing with scenarios and you want to change the rate a few times to see what the results are it’s much quicker and easier to just type a new figure in cell G3 and let Excel do the work to change the formula.

So assuming I’ve convinced you that referencing one cell for the commission rate is the best method let’s look at how absolute references work.

Other ways to use absolute references

• Make a whole range of cells an absolute reference: $D$1:$F$1
• Make only the column absolute $D3
• Make only the row absolute D$3

As you can see in the examples above, whatever the ‘$’ sign prefixes is absolute. i.e. as you copy the formula anywhere in the spreadsheet the reference prefixed by the ‘$’ sign will not change.

Shortcut to entering Absolute References

The magical F4 key instantly enters the ‘$’ signs for you.  You can do it while you’re building your formula, or you can go back and edit the formula and enter them.  Of course you can also type them in but it’s quicker to use F4.

Let’s look at the different ways you can enter absolute references using the F4 key in more detail

1)      While you’re building your formula; as you can see below I have started to type a formula into the cell E3.  I have just selected cell G3, as you can see by the marching ants (dashed line) surrounding the cell.

At this point, before I press enter to finish the formula, I can press the F4 key and Excel will automatically put the ‘$’ signs around G3 for me like this.

2)      Or I can go back to a cell at any time and press the F2 key to edit the cell.  I can then put my cursor anywhere in the cell reference I want absolute and press F4.  See below.

3)      If you want to absolute a range you have to highlight the cell range like the example below before pressing F4.

4)      The above examples show you how to apply an absolute reference to the column and the row, but if you keep pressing F4 Excel will scroll through your options. Using =D3*G3 as an example, I want to absolute G3:

a.       With the first press of F4 you will get =D3*$G$3

b.      With the second press of F4 you will get =D3*G$3

c.       With the third press of F4 you will get =D3*$G3

d.      With the fourth press of F4 you will get =D3*G3

So, now you know how Absolute References work in Excel, and how to apply them quickly using the F4 key, you can start to build better spreadsheets that you can dynamically update.

Download the workbook to practice here.

Share the knowlege with your friends and colleagues.  Click the icons below for Twitter, Facebook, Stumbleupon and many more.

Share This

Tweet

Please share this or leave a comment and I'll make sure you get a personal reply.

Tagged as: absolute references, excel absolute references