Relative, Mixed and Absolute References
I’ve been teaching Excel since 1990 and if I had to name one feature that puzzles participants the most, it would 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 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.
Let’s look at one simple example where only relative references are needed. In all 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 that are 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 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.
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. We 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. Here because the 12 numbers to the left are 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 result table is copied both down and across the reference to cell E1 will be $E$1.
Below the table discussed here you can see the same table but showing the formulas.
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 face the scenario where a Mixed reference is required.
Now that you are familiar with the role of the $ in a reference let’s push this a tad further. If you see the following reference in an expression: A$1 can you figure out what’s going on? What about the reference $A1? Well, we know that if there is a $ somewhere in a reference this means that 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 Row Constant.
- A Mixed reference A$1 prevents the row number to change. Let’s call it a Column Constant.
In the example here, the formulas in cell B1 multiply all amounts in column A with the single value in cell D1 (the value 2). 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 $D1. Hence, we call it a column constant.
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 (which took that example from the former Lotus 123) 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 and nowhere else!
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 with their respective row. Changing any Value to another amount 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 with their respective column. Changing any 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. Just in case you wish you could ask, 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!
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 this means that there are cells depending of the value of that cell.
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:
- Click in a cell that is a constant in your worksheet.
- 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.
To the right is an example of arrows pointing from a single constant to all the formulas using that constant. Cool!
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. Also 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 from the Trace Dependents section just 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.
I would be impossible for an Excel user to think that they are a very fluent 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.
Daniel from ComboProjects