Mainly, we have two kinds of cell references, relative reference, and absolute reference. Here we are going to discuss an absolute reference in Excel. Both kinds of references work differently when you use them. When you copy any formula to another cell, relative reference will get change, whereas absolute reference will not change.
Let’s understand the absolute reference in detail.
What is an Absolute Reference?
An absolute reference has a column and row coordination that stays constant whether you copy a formula from one place to another. You can use the $ symbol before the coordinates to make them correct. For instance, $D$2 would be an absolute reference to cell D2.
Moreover, you can use F4 from your keyboard for the functioning of an Absolute Reference. When you have an absolute cell reference, it clearly shows that there will be no more changing, no matter if you alter the formula from one cell to another or even another worksheet. Excel always has a relative cell reference that identifies changes while the formula is duplicated.
A cell reference could be blended with an absolute or relative reference. And if you have a mixed reference, you may use the column name or the row number that stays constant.
How to Make Absolute Reference in Excel?
Below we have some useful hacks that let you perform this function easily. Follow the steps given below:
- Select a cell where you need to make an absolute reference. Cell A1 is in this example.
- Cell A1 formula needs you to add “=” to the equal sign to choose the point of reference – Cell C1.
- Now, you will have to put two dollar signs in the same formula either manually or by pressing the F4 key as a shortcut. To manually add a dollar sign, you will have to press Shift + 4 in front of the row and column coordinates.
- Press ENTER button and now you will see that Cell A1 has the value of Cell C1.
So basically this is how to make absolute references in Excel. The procedure is quite easy and you will not have to remember big formulas or lengthy steps. Let’s move on to how you can use an absolute cell reference.
How to Use Absolute Cell Reference?
First, have a look at this example that shows the final cost is calculated by multiplying every product cost with a tax rate multiplier.
The formula = B2*E4 will be used here.
Keep in mind this formula is effective for the cell to which it is added. In case, you drag the formula to the other cells in column C, you will have error values in the result. When you need to see the formulas added to the Excel sheet, open the Formula tab and click on the Show Formulas option. Choose this option.
In this case, Excel will be using relative reference automatically.
Here you will see that the Tax Rate Multiplier is changing for each cost. That’s why we have to resolve it, such as E4, by turning it into an Absolute Reference, which makes it constant.
For this, you may add a $ sign right before the column name and row number just like this: $E$4.
After this procedure is done, the formula would be as:
=B2*$E$4
For a better understanding, you can have a look at the below image.
Cell E4 will get locked when you drag the formula to copy it to other cells.
You will see the final cost is being calculated by multiplying each product cost with a fixed tax rate.
So, this is how you can use an absolute cell reference easily. Enjoy exploring new features of Excel and keep sharing them with others.
