Using Excel to its fullest potential requires knowing how to use formulas. Knowing the different formulas is a great start, but there are tips and tricks you can use to make formulas work even better. In today’s tip, we will explore one of those formula tricks: Absolute Reference aka absolute cell reference.
What is An Absolute Reference in Excel?
This term refers to how you identify the cells being referenced in a formula. A cell reference would be specific cell identified in a formula. For example, if we wanted to run a sum on the following Sale & Tax:
We would enter the following Sum formula:
=sum(A4:B4)
The A4 references the cell with the sale amount and B4 references the cell with the tax amount to give us our total of $318.
Now, if you wanted to copy this formula to find that totals for all of sales, you can copy and paste it down (or use the AutoFill option as in the following animation):
This tool works because of Relative reference. The A4 and B4 in the formula changes to A5 and B5 on row 5, and so on. In this case, the two cells being referenced change according to their relation to where the copied formula is being pasted.
This works well when the information relative to the formula changes in each instance. But what do you do when one of the cells doesn’t change location?
For example, let’s say you wanted to find the commission in the following table by multiplying the sale amount against the commission rate:
This works in the first instance, but then when you try to copy it down, the totals won’t work:
This is because the cell being referenced for the commission rate changes as it is still a Relative Reference.
To make it an Absolute Reference, you use the $ symbol to frame the cell you don’t want to change. Then, when you copy and paste the formula, the commission rate cell stays absolute while the sale remains relative to the row the formula is on:
In this case, the absolute reference anchors one part of the formula so that it can be copied and pasted with ease. This really comes in handy when you have lots of rows of data!
We here at Learn Excel Now hope you enjoyed today’s tip on Excel’s Absolute Reference. This should improve your formulas.
Like Learn Excel Now? Don’t forget to subscribe to our newsletter and follow us on social media!
Kevin – Learn Excel Now