I use excel all the time to calculate personal finances or to dissect information at work. Every once in a while, I get introduced to shortcut or formula that has existed forever and is life changing.
I learned this one from Nate Moore, when he came to speak at the North Fulton Medical Group Managers Association in March 2023.
Absolute reference is a tool that allows you to refer to data in a specific cell and lock it in so that when you copy formulas it will always refer to that cell. Here’s a quick example. In the sheet I built below, I want to calculate what the sales tax payment would be for each month. It’s a simple formula that multiplies the revenue for that month in Column B by the sales tax rate of 7% (shown in cell C8, image 1).
When you go to copy that same formula down to the next cell (as opposed to manually typing it – because who wants to do that), you’ll notice that excel will move all parts of the formula down as well. (image 2)
Now the formula is referencing an empty cell for the sales tax portion and therefore messes up your calculation.
In order to lock in the sales tax cell (C8), you simply modify your first formula to read as such =B3*$C$8. The use of the dollar signs around that cell now tells excel that this is an absolute and you don’t want this cell to change throughout that formula. (image 3)
Now when you go to copy that formula into the rest of your cells in Column C it will always preserve the reference to the sales tax cell in C8.
This can be a huge time saver, especially if you are dealing with lots of data.
If you didn’t know this one and you’re an Excel junkie, give this one a try. Kudo’s again to Nate Moore for sharing this one.
