Tuesday Tip: Absolute Reference – Another Excel Timesaver

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.

Richard Stokes: As the Director of Sales for Network 1, Richard identifies “future” clients that can benefit from the support of an experienced, outsourced IT team. He helps clients and prospects find technology solutions they need to achieve better productivity and efficiency so they can focus on making money and growing their businesses.

Network 1 designs, builds and supports the IT you need to run your business more securely, productively and successfully. Whether you want to outsource all of your IT needs to a reliable, responsive, service-oriented company, or need to supplement the work of your internal IT staff, we will carefully evaluate where you are now, discuss where you want to go and implement and support a plan to get you there with as little interruption as possible.

Leave a Comment





Related Posts