In Excel, Relative reference is the default reference type. Therefore, whenever you copy a formula to a range of cells, they change based on the relative position of the rows and columns. But if you want to fix a reference so that the rows and columns don't change when copied down to a range of cells, we use Absolute reference.
Absolute Reference in Excel
An absolute reference in Excel is a reference that cannot be changed when copied, so you won't see changes in rows or columns when you copy them. Absolute references are used when you want to fix a cell location. These cell references are preceded by a dollar sign. By doing this, you are fixing the value of a particular cell reference.
For example, if you type the formula = A1+A2 into A3 and copy it to another location: B3, the formula will change to = B1+B2.
But, if you type the formula as an absolute reference such as = $A$1+$A$2, and then copy the formula to any other location, it remains fixed as = $A$1+$A$2.
You will learn how to make an absolute cell reference in the next section, so stay tuned.
How to Create an Absolute Cell Reference?
The syntax mentioned below helps you create an Absolute Cell Reference in Excel:
We are fixing the cell B1 as an absolute reference. This is done by appending a dollar sign '$' before the row and column.
To fix the range instead of a single cell, follow the syntax stated below:
How to Use Absolute Cell Reference?
First, let’s look at a simple example where the final cost is calculated by multiplying each product cost with a tax rate multiplier.
In this example, we use the formula = B2*E4.
This formula works only for the cell to which it is added. If you drag down the formula to the rest of the cells in Column C, it results in error values.
To view the formulas added onto our Excel sheet, go to the Formulas tab → Show Formulas option and click on it.
The screenshot attached below shows the formulas on our Excel sheet. Excel follows relative referencing by default.
You will notice that the Tax Rate Multiplier changes for every cost. So, we need to fix this value, i.e., E4, by making it an Absolute Reference.
To make the cell E4 an Absolute Reference, add the dollar symbol ($) before the column name and row number like $E$4.
So now, the formula will be = B2*$E$4.
Look at the demo depicted below for a better understanding of the same.
When you drag the formula to copy it to the rest of the cells, the cell E4 will be locked. The screenshot below displays all the formulas entered on the Excel sheet.
You will notice that the final cost is calculated by multiplying each product cost with a fixed tax rate.
NOTE: Apart from Relative and Absolute references, there is a third category called Mixed Reference. As the name suggests, the cell reference can contain an absolute column and relative row or an absolute row and relative column i.e. mixed. So we add the ‘$’ symbol before either the row or column, whichever is absolute.
- B$1 fixes the row
- $B1 fixes the column
- B1:$B$10 fixes the cell B10
This is the gist on Absolute Reference in Excel that you need to know. Attempt the quiz below to test your knowledge on Absolute references in Excel.
1. Cell References in Excel are classified into 3 types: Relative Reference, Absolute Reference, and?
2. To make the cell D10 as an absolute reference, how should you specify it?
3. Which cell reference will not change if copied or moved?
- Relative reference
- Absolute reference
- Statice reference
- Fixed reference
Solution: Absolute reference
We hope this article has helped you understand how to use an Absolute reference in Excel. This topic might be a little tricky to understand, but it is very crucial if you want to further enhance your Excel skills.
In case you have any queries, please mention them in the comments section of “How to Create an Absolute Cell Reference?” article, and our experts will get back to you at the earliest.
Whether you are interested in learning the basics of Excel or want to develop more advanced Microsoft Excel skills, Simplilearn has a “Business Analytics Certification Course with Excel” course for you!