[Master Guide] Excel Cell References

 

Cell References tell your formula how it works.

Using the right kind will let you copy your formulas across your data.

All while keeping them working as intended

We also teach this on our Microsoft Excel courses, as it’s a core fundamental!

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

Absolute Cell References?

Absolute Cell References are used to ‘lock’ a cell in a calculation.

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 autofilled elsewhere, it is locked.

The $ symbol is what makes it an absolute reference!

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

When using AI to generate formulas, always pay attention where the $’s 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 and functions effectively!

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 down to 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!

Switching Between Reference Types

You can switch between a relative and absolute reference in two ways.

1 – Manually

You can manually input the $ symbol as described in the example, placing it wherever needed.

2 – Using F4

You can change cell references simply by pressing F4 when you click on the cell you wish to effect.

Pressing F4 allows you to rotate between the 4 different potential references.

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 keyboard shortcut switches between relative and absolute for you.

Conclusion

Now you know all about cell references, how and when to use them!

Think about your cell referencing every time you work in Excel, they are always relevant.

Keeping an eye on your cell references is a great way to fix and audit your formulas!