News & tips
Learning about Relative, Absolute and Mixed Cell References will allow you to understand each type of cell referencing when creating formulas.
This will save you a great amount of time.
If you want to learn more about the more complex features in Excel, read our guide on Using Macros in Excel here.
Table of Contents
Relative cell references are the most basic type of cell references. They adjust and change when copied, or when using AutoFill. By default, any reference is a relative reference in Excel.
They change based on the position of rows and columns relative to the cell the formula was written in. If you were to copy a formula such as “=A1+B1” down from row number 1 to row 2, the formula will change to “=A2+B2”.
For example, this simple addition formula in cell E7 asks Excel to add up B7, C7, D7 and place the value in E7. The positions of each cell are relative to E7, Excel tracks how far away they are – hence the name relative references.
When you Copy and Paste the formula downwards, these positions will change accordingly.
If E7 = B7, C7, D7, then E8 = B8, C8, D8
Relative references are great when you want the same formula anywhere else in your sheet as you can copy and paste it straight away. This is particularly useful when you need to have the same calculation repeated across several different rows or columns.
Absolute Cell References are used to ‘lock’ a cell in a calculation. This is in contrast to standard Excel references that ‘move’ as the cell that the sit-in moves. Controlling formula references is covered in our intermediate Excel course.
When it is used a $ sign is included in the calculation next to a cell reference in Excel, e.g. A1 + $A$2. This means that this cell reference will not change if it is copied or auto-filled elsewhere, it is locked. The $ sign makes it an absolute reference.
If you are including a single cell in a calculation in multiple cells, you need to know this. E.g adding VAT to a whole list of products is suitable for an absolute reference.
The easiest way to do this is to create your calculation in the normal way but when you click on the cell you want to lock hit the F4 key, this will put in the $ signs.
Step by step to put the calculation formula in cell C2 in the picture is:
Now when you drag this calculation down into the cells below the first cell reference will change but cell B8 will not.
In Excel Formulas, a dollar sign makes the following row or column number an absolute reference. This means when copied, the row or column you wish to be absolute, will not be changed.
In the prior exampe, when the formula in cell C2 was copied down to C5, the row and columns the formula used with $’s before them, did not change whatsoever. B2 goes all the way up to B5, but $B$8 did not change.
This is all controlled by careful use of these dollar signs. This is how Absolute Cell Referencing operates, as well as how to make a mixed reference.
Absolute Cell References should be used when you do not want a cell reference in Excel to change when being copied or filled. If the cell reference is absolute, it keeps rows and columns constant.
In the example given, a flat VAT Rate was applied to a number of different product prices in order to find the resulting taxable cost – which was in turn used to find out the final price of an item. For this type of example, an Absolute Cell Reference in Excel is entirely appropriate as you have a constant value you wish to use (the VAT Rate). They are great for any time you are using a fixed value within the formula. If you wanted to use a relative reference for such an example, you would have to spend lots of time reorganising data.
While you could simply write the VAT Rate in by hand on a formula, (by writing 20% instead of the actual cell containing 20%), an Absolute Cell Reference will allow you to change this value later and effect the whole worksheet at once.
Mixed cell references are a combination of relative references and absolute reference. There are two distinct types of mixed reference.
Either the column is locked while the row changes when the formula is copied, or the row is locked while the column changes when the formula is copied.
Both ways are known as a mixed reference. They are both very useful in order to copy formulas effectively, for more information on how to copy formulas, visit our guide.
For example, in the following picture is a dataset attempting to find the VAT of different prices at different rates. In the E5 Cell I have written the formula “=$B5*$C5*E$4”. This is a mixed reference.
The reason we have the $ before B5 and C5 is because when we copy the formula horizontally to the right, the reference will stay as it is because the column is fixed. However it will change when we copy the formula downwards 0 but the row number will not.
For E$4, the $ is before the row number instead of the column. When the formula is copied downwards, the reference is not going to change, but when copied to the right the column will change because it is not locked!
After copying the formula each way, we can see that each different cell reference has changed accordingly. Each cell now contains the correct percentage rates of each seperate price, and you can tell by the formula in cell G9 being “=$B9*%C9*E%4”. The First two cell references have changed, but the final has not as we have only moved downwards.
Mixed Cell References are great for efficiency in data handling on large complicated projects. It is a more complex form of Absolute referencing which means when the data on your sheet is too spread out for Absolute, Mixed referencing is appropriate.
You can switch between a relative and absolute reference in two ways.
Either you manually input the $ symbol as described in the example, placing it wherever needed.
You can insert absolute cell references simply by pressing F4 when you are in the formula that you wish to alter.
Pressing F4 allows you to rotate between the 4 different potential references. Each press of the key moves you along to the next possibility.
If you are in a formula referencing A1, pressing F4 three times will adjust the formula in the following order: from A1 to: $A$1 > A$1 >$A1.
Pressing F4 one last time you take you back to the start with A1.
This handy shortcut switches between relative and absolute for you, and can save lots of time when writing complex formulas with all reference types. Understanding how to move between a relative, absolute and mixed reference in Excel is key.