Absolute References in Excel – do you know how to make a cell reference absolute in your spreadsheets?
This week’s hint and tip is about how to make a cell reference absolute in Excel and how to use them in your spreadsheets. This is on our Basic Excel training course and it covers the different ways in which you can create them. We are going to go through it now below.
What are they?
In an Excel spreadsheet (technical name – worksheet) there are many cell references that are used in calculations and formulas. A cell reference is created where a column and row intersect. For example, in the cell that intersects column A and row 4 will have the cell reference of A4.
When you then create formulas and calculations in Excel, you use these cell references to build them up. Therefore when you copy the calculation or formula, it will be copied relative to the row or column that it is copied in to. In this example, this is called relative copying. However, there will be some instances where you will want a cell to be ‘fixed’ in the formula or calculation. This is so when it is copied, that cell does not change, it remains the same within the formula or calculation. In this instance, this is called an absolute reference.
How to make a cell absolute
There are two ways to make a cell reference absolute. Firstly by using $ signs and secondly by naming cells. The video below shows you a different example of how you can use absolute references. Meanwhile, if you want to know more about absolute references and how to create them, take a look at our previous hint and tip here.
The video below shows you what an absolute reference is and how you can make a cell reference absolute in Excel. It takes you through the 3 different types of absolute references that you can have. As well as this, it shows how that makes copying your calculation into other cells easier.
Watch the video below to find out more. After that, try it out using the example spreadsheet below!
Click here to download the example spreadsheet shown in the video to try it out yourself! (It is blank so that you will have to create the calculation yourself!)
We hope you have enjoyed this hint and tip on absolute references in Excel. Why not take a look at our previous one on 365 Maps in Excel?