Absolute Cell References: An Overview for Non-Geeks
The concept of absolute cell references comes up in several of our Excel courses. Initially, people may find the concept a little hard to understand but, in many cases, absolute cell references are the key to building efficient formulas in Excel – no pocket protector required!
Absolute cell references do not change when you move or copy them. You can refer to the same value in a series of calculations. You create a formula once, copy it to multiple cells, and produce accurate results every step of the way.
Relative Cell References
Before we talk about absolute cell references, we should take a look at Excel’s default behavior when performing calculations: relative cell references.
Relative cell references change as you move or copy them.
Let’s look at an example. In the spreadsheet (below), I have entered a formula that will calculate the price of coffee multiplied by the quantity ordered. The formula is =B3*C3.
To calculate subtotals for the additional items in the worksheet, I don’t have to manually create a separate formula for each row, I simply position my pointer over the fill handle and drag down. The formula gets copied to every cell from D4 to D7.
Here’s the best part. The calculations are accurate for each item in the list. Relative cell addressing caused each instance of the formula to update relative to its position in the worksheet. In row 3, the formula is =B3*C3. In row 4, the formula is =B4*C4. In row 5, the formula is =B5*C5. You get the idea.
Here is one more screenshot, showing the formulas in our spreadsheet.
Absolute Cell References
An absolute cell reference is like a constant in math. You can refer to a specific cell in your calculation and, if you move or copy the formula, the cell reference won’t change. In our next example, we’ll calculate tax on the subtotal for each item on the menu.
To create a formula with an absolute cell reference, I’ll click in cell E3 and type =D3*H2 but I won’t press ENTER. Instead, I’ll press the F4 button on my keyboard then press ENTER. The final formula is =D3*$H$2. The dollar signs ($) let Excel know that the H2 cell reference should not change as it is moved or copied.
To calculate tax for the remaining items, I can drag the fill handle down to copy the formula to E4:E7. The $H$2 cell reference will not change and valid amounts are calculated for each row.
Take a look at the formulas and the absolute cell reference for $H$2.
Mixed Cell References
If you have learned everything you need to know about absolute cell references, or your head is starting to hurt, you can stop reading now. You’re not going to offend me… but there is one more type of cell reference left to talk about: the mixed cell reference. Mixed cell references have a dollar sign in front of the column letter or the row number (not both).
As usual, an example might be helpful. In our next worksheet, we want to calculate discounted pricing for large orders. There are several levels of discounted pricing available but we only have to enter one formula to perform all of the calculations. Let’s see how it works!
I’ll click in cell C4 and type =B4 then I’ll press the F4 button on my keyboard 3 times so the B4 cell reference turns into $B4. $B4 means that the column (B) part of the cell reference won’t change as it’s moved or copied but the row (4) piece will.
Next, I’ll type *(1-C3 and press the F4 button on my keyboard twice. Finally, I’ll add a closing bracket and press ENTER.
When I copy the formula across to D4:F4, all of the pricing scenarios for coffee are calculated with 100% accuracy.
To finish the discounted pricing example, I’ll use the fill handle to copy the formulas down to C5:F8.
Discounted pricing has been calculated for all menu items, using a range of quantities.
Using a combination of relative, mixed, and absolute cell references will help you create a single formula that can be copied to a range of cells and produce accurate results every step of the way.
I hope you have found my explanation of this topic useful. If you liked the article, please share it with your friends on social media!