Excel References: Absolute, Relative and MixedWhat they are and how to use them

Written By:
Adiste Mae

Working with Excel, we know that the information we input is held in different cells – it’s what makes up our worksheets. 

The worksheets contain rows and columns.

For example, cell D5 refers to Column D (4th column) and Row 5. 

The importance of these cells is brought to light when we have to work with formulas.

It becomes relatively easy when we reference cells instead of manually typing the information we have already supplied previously. 

3 Kinds of Cell References

  • Absolute Cell Reference
  • Relative Cell Reference
  • Mixed Cell Reference

Absolute Cell Reference

Copied formulas do not change when you paste them into a different cell.

This is referred to as an absolute reference. 

To illustrate, see the image below:

If we want to get the maximum discount we can get from the sale and reference the discount percentage from another cell (H1), we simply use this formula:

=D2*$H$1

You must have noticed that we have added a dollar sign in the reference cell before the letter and before the number. 

Function of $

What makes the value of a cell absolute is the use of the $ symbol. This means that when the formula is copied to other cells, the value of the reference cell remains constant. 

In our example above, notice how the formula in the second column went from =D2*$H$1 to =D3*$H$1 in the third column. 

Because of the $ symbol we added before H & before 1, the cell value will no longer change, making it an absolute reference. 

When Absolute Reference is Used

It is particularly beneficial for you to use absolute reference in Excel when you have to deal with values that are fixed such as commission percentages, tax rates, discount percentages, rent amounts, etc. 

While you can just manually enter the values in the formula, the advantage of storing the value in the cell is that when you change it at a later date, the values are automatically updated. 

You no longer have to copy-paste the formula to the rest of the cells. 

Relative Cell Reference

This can be easily explained through the illustration below:

To get the total sales, we will have to multiply columns B and C.

The formula to be entered in cell D2 would then be:

=B2*C2

Instead of manually entering the formula in cells D3 to D7, you can simply copy the formula in D2 and paste it to the rest of the cells in column D. 

Note that the formula in cells D3 to D7 has changed and references columns B & C but for the same row. B2*C2 has become B3*C3 and so forth. 

When the cell references change as the formulas are copied, it is referred to as Relative Cell Reference. 

When Relative Cell Reference is Used

It is particularly useful to use relative cell reference when you are working with cell ranges.

Here, you only need to input the formula in one cell, and then copy and paste it into the rest of the cells. Or, you can use the fill handle and drag the cells down. 

Mixed Cell Reference

Compared to the Absolute & Relative Cell References, the Mixed Cell Reference can be a bit more challenging. 

Types of Mixed Cell References:

  • When copying a formula, the column changes while the row remains locked
  • When copying a formula, the row changes while the column remains locked

Let’s simplify what mixed cell references are by using the illustration below:

If we want to know the maximum discount to be given based on the different allowable discount percentages, we need to apply the formula shown below in cell E3:

=$B3*$C3*E$2

What makes the formula a mixed cell reference? We’ll explain that below:

  • $B3 & $C3 – the dollar sign is before the column reference. This means that when the cell is copied down the cells, it changes to $B4 & $C4, respectively, as the rows are not locked. However, if you copy it to the right of the cell, it will remain constant because the column is fixed. $B3 & $C3 will still remain the same. 
  • E$2 – Here, notice that the $ symbol is before the row reference. Yes, this means that the row is locked while the column is not and can change. In contrast to the above, when copied down the row, the cell reference remains the same. Copied to the right, the column reference will change. 

Changing to an Absolute or Mixed Cell Reference from a Relative Cell Reference

It is easy to change a relative cell reference to either an absolute or mixed cell reference. All you have to do is add the $ symbol. 

For example, to make cell B3 an absolute reference, add the $ symbol before B and before 3. Now, it becomes $B$3. 

You can manually edit the cells and change them to either an absolute or mixed cell reference by clicking on the cell and making the edit from the formula bar or pressing F2, or you can double-click on the cell.  

There is a faster way, click on the cell and press F4. 

F4 Key

Here’s what the F4 key can do:

  • Press Once – B3 will instantly become $B$3 (absolute)
  • Press Twice – B3 will instantly become B$3 (mixed, row is locked)
  • Press Thrice – B3 will instantly become $B3 (mixed, column is locked)
  • Press Four Times – will revert as a relative cell reference