Microsoft Excel: Absolute, Relative and Mixed Cell References

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.

What are Relative Cell References?

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

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. This is 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. 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:

• 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 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.

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, 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.

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.