Absolute References in Excel – do you know how to make a cell reference absolute in your spreadsheets?

Absolute References: Excel icon​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 in the button!

Click on button to the right 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?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy