News & tips

Microsoft Excel: Absolute Cell References

Absolute cell references are used to ‘lock’ a cell in a calculation. This is in contrast to standard Excel references that ‘move’ as the cell that the sit-in moves. Controlling formula references is covered in our intermediate Excel course.

When it is used a $ sign is included in the calculation next to a cell reference, e.g. A1 + $A$2. This means that this cell reference will not change if it is copied or auto-filled elsewhere, it is locked.

If you are including a single cell in a calculation in multiple cells, you need to know this. E.g adding VAT to a whole list of products

The easiest way to do this is to create your calculation in the normal way but when you click on the cell you want to lock hit the F4 key, this will put in the $ signs.

Step by step to put the calculation in cell C2 in the picture is:

• Click on C2
• Click on =
• click on B2 and then *
• Click on B8 and then press F4
• Press enter

Now when you drag this calculation down into the cells below the first cell reference will change but cell B8 will not

To learn about relative cell referencing click here.

To learn more about Excel formulas click here.

This is covered in our Excel Essentials course.