Learning about Relative, Absolute and Mixed Cell References is imperative to being a skilled Excel user.
This will save you a great amount of time.
If you want to like this guide and want to learn more about the fundamentals of Excel, check out our Microsoft Excel courses here!
What Are Relative Cell References?
Relative cell references are the most basic type of cell references. They adjust and change when copied. 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
When Should You Use Relative Cell References?
Relative references are great when you want the same formula anywhere else in your sheet as you can copy and paste it straight away.
If you are copying and pasting a formula across your worksheet, always double check the results!
Relative cell references are particularly useful when you need to have the same calculation repeated across several different rows or columns.
What Are Absolute Cell References?
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.
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 is what makes it an absolute reference! More on this later.
If you are including a single cell in a calculation in multiple cells, you need to know this.
A good example is adding a flat VAT percentage to a whole list of products is suitable for an absolute reference.
Step by step to put the calculation formula in cell C2 in the picture is:
- Click on C2
- Click on =
- Click on B2 and then *
- Click on B8 and then press F4
- Press enter
Now when you drag this calculation down into the cells below the first cell reference will change but cell B8 will not!
What Does The Dollar Sign ($) Do?
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 example, 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.
When Should You Use Absolute Cell References?
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.
What Are Mixed Cell References?
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, read here.
For example, in the following picture is a dataset attempting to find the VAT of different prices at different rates. In the E5 Cell we have 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.
When To Use Mixed Cell References
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.
Switching Between Reference Types
You can switch between a relative and absolute reference in two ways.
1 – Manually
Either you manually input the $ symbol as described in the example, placing it wherever needed.
2 – Using F4
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.
Now you know all about cell references, how and when to use them!
Think about your cell referencing every time you work in Excel, and check back in on this page if you have any problems!
Always make sure you check your work as you go when using Excel, and if you come across unexpected mistakes, think about the type of cell references you are using.