What does the $ mean in an Excel formula?

There may be occasions when you want to use a value in one cell in your formula but cannot allow the formula to change the cell reference when you copy it down or across your spreadsheet. For instance in the example below the formula in C11 in the Surcharge column references cell B5 (which is the Surcharge rate) but if you copy this formula down to C12 the B5 reference will change to B6. This is not what we need in cell C12…we need it to read =B12*B5.

We want to make the cell B5 ‘Absolute’…in other words we don’t want it to be able to move from that cell reference.

We can make the ‘B’ part of the cell absolute by putting a $ in front of the ‘B’ and we can make the ‘5’ part of the cell absolute in the same way. If we therefore never want excel to change the reference of this particular cell at all the formula would read =B11*$B$5.

A quick way to put the $ in is to tap the ‘F4’ key on your keyboard after typing the B5…you will find that the $ signs will go in before the B and the 5. This is a toggle and so you can tap it again and again to see other variations if you don’t need both $ signs.

This is covered in more detail in Chapter 5 of Excel Level 1 – Formulas and Functions.

For more information about our Excel Courses please click here.

Share :
Related Posts

Leave Your Comment