I’ve been teaching Excel since 1990 and if I had to name one feature that puzzles participants the most, it would have to be the one described in the title of this post. I would love to resolve this today! Let’s start from the very beginning shall we?
I’m starting at the very beginning but we’ll move on shortly! The word ‘reference’ refers to the address of a single cell or a range (or selection) of cells. A reference to a single cell might be: D5. Finally, for a range, it could be: C4:F12.
Only with formulas or functions
This section is titled ‘Only with formulas or functions’. However keep in mind that a formula is not necessarily about arithmetic. The referenced cell(s) can contain a date or even simply text! I often use the term expression to refer to all types of formulas.
By default all references are Relative. A user may needs to configure a reference as Mixed or Absolute only if a formula is to be copied. So if your formula will not be copied (a one off) then no need to worry about this subject.
Relative references
Let’s look at one simple example where only relative references are needed. In every screenshots in this post the cells containing a formulas appear with green font. In the example to the right the formula in cell C1 multiplies the two cells to the left. The column D shows the formulas used in column C. Here I use the FORMULATEXT() function (introduced in Excel 2013) to display the actual formula in the worksheet.
When the formula in C1 is copied down, Excel automatically adjusts the references to point to the pair of numbers in row 2 and 3. We say that the references in these formulas are relative to the row where that formula is copied. If there was a pair of numbers (side by side of course) in row 450 but same column, then the copied formula would be =A450*B450. Lastly if there was a pair of numbers in row 45 but in column F and G, the copied formula would automatically be =F45*G45.
So, the formula =A1*B1 will work in any possible cells in any workbook as long as there are 2 numbers immediately to the left. Of course the formula could not be in column A or B since there cannot be two cells to the left. Simple as that! The formula can be much more complicated that our example, the principle is the same.
Absolute References
The Absolute type of references is often misused. You only need to configure a reference to a cell or selection as absolute if the expression will be copied Down and Across. An example of absolute reference to cell A1 will be $A$1. To add the $ character to a reference press the F4 function key after you selected the cell or range.
The $ before the A means that that when the formula is copied across the column letter A will stay the same. Also, the $ in front of the 1 means that when the formula is copied down the row number 1 will stay the same. I like to call this type of reference a ‘constant’ because that cell is used with many formulas.
In the example above we need to multiply the 12 numbers on the left by the single cell containing the 5 and have the 12 answers further across. Because the 12 numbers will be used to generate 12 answers we use a Relative reference. However, since the 12 formulas use the same cell (containing the 5) and will be copied down and across this reference is a constant. Since the resulting formula is copied both down and across the reference to cell E1 will be $E$1.
Below our example above you can see the same result but showing the formulas.
Mixed References
The Mixed type of reference is the least understood. Not because it’s a hard one to grasp but (and I often hear this) because 95% of the time users use an Absolute reference. Another reason could be that they never faced the situation where a Mixed reference is required.
If you see the following reference in an expression: A$1 can you figure out what’s going on? What about the reference $A1? We know that if there is a $ somewhere in a reference, the formula will be copied. Also the $ forces the column letter or row number to stay the same as the formula is copied.
A Mixed reference $A1 prevents the letter to change. Let’s call it a Column Constant. Formula is copied to the right.
A Mixed reference A$1 prevents the row number to change. Let’s call it a Row Constant. Formula is copied down.
Let’s look at another example.
In the example across we want to multiply the 6 values in the first column by the single number 2 at the upper right corner. Remember that the green colored values are the answers.
You may remember that the cell D1 is called a constant. Since the formula is copied down only, we will use the reference D$1. When copied down, the reference would stay D$1. Hence, we call it a row constant.
If the example was rotated then the formula would need to be copied across. The formula would look like the following (Assuming that the constant is in cell A1 and the 6 values are in row 2: =A2*$A1
Gentle warning
The solution for the example shown in this section (Mixed References) can cause a bit of division among some Excel users. Why? Because some feel that the reference to cell D$1 (the constant) can also be set as Absolute ($D$1). True, but this approach will cause you headache later in more elaborate situations.
If you train your mind to use Absolute references for constants like the one show just above then why did Microsoft also offers Mixed references? Follow along and see an example where a simple formula must use and Absolute reference and two Mixed references. No Relative reference at all used here.
Putting everything together
Now that we have a better understanding of the type of references Excel offers let us see a cool exercise where we need to be careful which type of reference to use based on the purpose (or role) the figures have in a two dimensional table.
In this exercise we need to put a formula in cell B6 that will: Multiply the respective Value by the respective Markup then add the result with the Base Figure. So technically, if there was only one Markup (Column) and only one Value (Row) the formula would simply be: =A6*B5+B1. This would work perfectly. But there are more than one rows and columns! The formula =A6*B5+B1 only works in cell B6 but not in the rest of our exercise!
Carefully look at the three callout boxes. The Table Factor points to cell B1. The Column Factors point to the top row of the table and the Row Factors, to the left column. Let’s briefly define what that means:
Table Factor – This value is used in every formula in the table. Changing the Base Figure to another amount would update the whole table instantly. Use an Absolute reference.
Row Factors – Each Value is used in their respective row. Changing a figure in the Value column would update the formula in that row and nowhere else. Use a Mixed using the model $A1. As the formula is copied across the column letter stays the same (Column constant).
Column Factors – You guessed it! Each Markup is used in their respective column. Changing a Markup to another percentage would update the formula in that column and nowhere else. Use a Mixed using the model A$1. As the formula is copied down the row number stays the same (Row constant).
The formula in cell B6 would then be: =$B$1*B$5+$A6. This formula can be copied down and across in this table and the result will be perfect. This formula could have been written a couple of different ways but the type of reference would have to say the same. For instance: =B$5+$A6*$B$1 or =$A6+B$5*$B$1 would yield the same result.
Help to debug formulas
If you’re having difficulties to see how such formula work, Excel offers two spectacular ways to make all this much more visual!
Trace Dependents
The Trace Dependents command is located (not surprisingly) in the Formulas tab. The term Dependent here refers (as discussed in the previous exercise) to cells that depend of the value of another cell. A constant is the best example of this. As we have discussed in this post, if a cell is used as a Mixed or an Absolute reference then there are cells depending on that cell (Constant).
If you briefly review the definition of Table, Row and Column factors you will quickly understand that changing a factor will update many cells. Below are the steps to use Trace Dependents properly:
Select a cell that is a constant in your table. Across it is E1.
In the Formulas tab click Trace Dependents. Immediately Arrows point from the constant to all formula that will update if that constant changes.
You can print the worksheet with these arrows if so desired.
Click Remove Arrows to … clear the arrows.
The Show Formula view
The Show Formula command will display the current worksheet with all the formulas. In this view you do not see the result of the formulas, only the formulas. The number formatting is disable temporarily. The keyboard shortcut to enable Show Formula is Ctrl+` (above the Tab key, also showing the ~). Press it again to return to the normal Excel view.
On the right you see the result of using Show Formulas when looking at the Markup table used above. In this view you can print all formulas in the current sheet!
A really nice feature of this view is that as the user moves between formulas in the worksheet, all referenced cells are highlighted in colour! In this example moving between the formulas in column B will highlight the corresponding cell in column A and the constant in cell E1.
Final Words
It would be impossible for an Excel user to think that they are a very fluent Excel user if they struggle with choosing the proper type of references for the situation at hand. Hopefully this detailed article has helped you shed some light on the topic of Relative, Mixed and Absolute references.
Download the Zip file containing an Excel workbook with the sample used in this post and a cheat sheet explaining all this in a concise manner.
