Cell References
In storytelling, a static character maintains the same persona from the beginning of the novel until the end. A good example would be the ultra-intelligent, pipe-smoking crime detective Sherlock Holmes.
Conversely, a dynamic character is more malleable and transitions throughout the trials of the narration; think Edmund Dantes of the Count of Monte Cristo.
Dantes begins his journey as a poor aspiring ship captain who morphs into the Wealthy Count with a lust for justice after his imprisonment and betrayal of a trusted companion. If you hate books think Batman for dynamic (orphan to Bruce Wayne) and Homer Simpson for Static.
Excel Workbooks can be similar to Novels except the stories are told with data and formulas instead of character narration. Rather than static or dynamic like characters in a Novel, cell references of a workbook are described as absolute or relative.
Absolute and Relative Cell references behave similarly until their formulas are copied/dragged into adjacent cells. Let’s examine the syntax and how they behave.
Relative Cell Reference
In cell D2, Let’s enter the following formula
=B2 * C2
This is a relative cell reference – B2 * C2 calculates the factors and the value is shown in D2.
If the formula is dragged to cell D3 and further notice how D3 Calculates A3 * B3 and so on.
However, the Absolute Reference Works a little differently. Let’s try it out now with the same data set.
Remove the prior cell data from D2 to D4 and Replace D2 with this:
= $B$2 * C2
What you have just entered is an absolute reference for B2. The dollar signs are sometimes referred to as anchors and precede the indicator that they are locking in place. The $ before the A locks the reference onto column A while the $ before the two makes this cell always refer to row two. Together both dollar symbols create a locking reference and make the cell as absolute (static) as Homer Simpson.
Observe the absolute behavior of the cells as you drag the formula to D3 and further.
The above example is not an appropriate use of an absolute reference so let’s find a better fit.
When using absolute references?
One of the most common reasons to use absolute cell references is to keep a constant variable for our Formulas. Some examples would be a tax rate, conversion factor, or an overhead rate that you want to calculate.
Let’s assume some of the books get damaged, lost, or stolen from the warehouse and we want to calculate the amount of loss from these types of misfortunate events. We’ll assume the rate of loss is 3 % for this exercise.
Its poor practice to a hard key or retype your tax rate into every cell like this:
E2 * .03
A better way to include the tax rate would be to create a referencing cell. Let’s enter .03 into cell H2 and make it our referenced cell.
Now we can use an absolute reference in our formula and drag it down so it looks like this:
E2 * $H$2
E3 * $H$2
E4 * $H$2
Doesn’t that look better? I’m not sure what is a realistic amount of loss in a book warehouse but three percent seems reasonable for our example. A restaurant manager would benefit from calculating a food waste rate similar to our example above. I’m sure that many times they will use a rate that’s much higher than our three percent (or they should). Just ask Jon Taffer from Bar Rescue!
Mixed Cell References
In terms of cell references, there isn’t a definitive line between using absolute and relative references! Sometimes you can use a mixed cell reference. In this case, you decide to lock the column only ($A1) or row only (A$1).
The best example of Mixed Cell References that I can provide is if you want to combine or multiply values in a table.
For example, there are two sets of values and you want to layout all of the possible combinations.
Data Set 1: Square, Triangle, Rectangle, Circle
Data Set 2: 1,2, 3, 4
Since we are trying to combine two values, we will use the CONCATENATE feature (or CONCAT for short). The dollar sign precedes the letter A because you want to lock the column as you drag down vertically. As you drag horizontally to anchor the row the $ sign will be placed before the number.
**Remember the Rows in excel are numbers so to lock them place the $ in front of a number. To Anchor Columns use the $ in front of the corresponding column letter.
Here’s a table to help you write a formula to reference cell A1 using relative absolute and mixed cells.
| Relative Reference | A1 |
| Absolute Reference | $A$1 |
| Mixed Reference- Lock Column Only | $A1 |
| Mixed Reference- Lock Row Only | A$1 |
I hope this article provides you with a better understanding of Excel Cell References.
Here’s a key to help you remember Absolute vs Relative Cell References:
| Absolute | Relative |
| Static | Dynamic |
| Stays the Same Regardless | Ready for Change |
| $A$1 | A1 |
***Bonus Tip***
I know the excitement is building as it’s time to run off and begin referencing cells with the tips learned today but it wouldn’t be an Excel shortcut without a bonus shortcut to take with you. The F4 Key can be used to cycle through different reference types. Just type a reference cell to get started.
Type A1
Click F4 once – =A$1$
Click F4 twice- =A$1, Row locked only
Click F 3rd time- =$A1, Lock Column only
Click again to return to the Relative cell
