Use absolute cell references in Excel to reduce duplicate work and speed up your calculations. Cell references allow you to carry formulas across cells while maintaining certain values in place so you can quickly perform dynamic calculations.
Cell Reference Example #1
Absolute cell references are very important in Excel when you're working with calculations where you have multiple values and a single individual value that is used in that calculation. Say you have products and prices, what you’re trying to do is figure out the sales tax for all of these products. When you go to a restaurant they will take the bill and multiply the total amount by the sales tax rate to get the sales tax total. To do this in Excel, you would input the sales tax rate into one cell and multiply that number across all of your products.
To start, you would multiply the first product by the sales tax rate. Now, instead of retyping the formula for every product, we want to be able to drag the formula down across all products, and that’s where we need to use absolute cell references. If I were to drag the formula down without using absolute cell references I would see that my answers don’t look correct and I may be questioning whether a career in Excel is right for me. Do not despair. We’ll want to diagnose that issue and when we look into what’s happening in those cells when we drag the formula down we’ll see that the selection where the sales tax value is is being moved down as I drag the formula down which is why I’m getting the error. Every time I drag the formula down another row, the reference that was supposed to be for sales tax is moving down another row and getting me a different/blank value in my sheet.
Solution
What we are going to need to do is lock one of the cell references so that when I drag the formula down it knows to keep the sales tax number in place. The quick way to do this is to click F4 on your keyboard when typing the cell reference for the sales tax field. You can also manually type in those dollar signs to lock the cell references. After I lock those cells in and drag the formula down I see that I got the correct answer now.
Example #2
Another example where this might be helpful is calculating the percentage of total. Say I have a list of numbers and I want to see what percentage each value is of the total of those numbers. I will go ahead and type in a formula that divides the first number by the sum of the full list of numbers. Now, I want to go ahead and drag that formula down to see the percentages of each number, but as I recall from the previous exercise, I need to make sure my cell referencing is set up correctly. In this case, I want the sum of the list of numbers to stay the same for all of my percentages because that total number is a constant, and I want the numerator here to change as I drag down the formula. So when I go ahead and type the formula, I will lock the cell references within the denominator (sum total) using the F4 key and then drag the formula down.
Learn More
To learn more helpful techniques like these, sign up for our Excel bootcamp or take one of our Excel classes.
See our other Excel class pages: