How to Use Relative, Absolute, and Mixed References in Excel

When dealing with lots of data in Excel, you can use relative, absolute, or mixed references to save repetitive manual work, ensure your formulas work correctly, and capture large volumes of data in just a few quick actions. Let’s explore how to use these Excel reference types.

What Are Excel Cell References?

You use a cell reference when you want to capture information that is contained within another cell or range of cells. For example, the following formula references cells A1 and A2, and will add their contents together to produce a result.

=SUM(A1+A2)

You can use the following types of cell references to achieve specific outcomes:

• By default, references in Excel are relative, which refers to the relative position of the cell. If you are typing a formula in cell A1 that involves cell A2, you are referencing the cell that is one cell below where you are typing. If you were to copy the formula into another cell, the reference would automatically perform the same relative action as in its previous location.
• You can also use an absolute reference, which does not change if you copy the formula into another cell. If you create a formula that refers to a cell using an absolute reference, that same cell will be referred to wherever the formula is used.
• Finally, you can use a mixed reference, which is a combination of the two.

Let’s look into these in more detail.

How to Use Relative References in Excel

In the example shown here, you can see how much the person has spent on each item in each month. What they now want to do is work out the total spend for each month.

First, click the cell where you want the first calculation to be made (in this case, H2), and type the formula to create this calculation. In this example, you would use the following formula:

=SUM(E2+E12+E22)

This has now referenced cells E2, E12, and E22 within the calculation, adding them together. This is a relative reference—the formula in cell H2 has referenced the cells that are three columns to the left (E2), three columns to the left and ten rows down (E12), and three columns to the left and twenty rows down (E22), respectively.

Now, if you were to copy and paste that same formula elsewhere, it would pick up the values that are in the same relative positions to where you paste it. You can also use Excel’s AutoFill function to complete the rest of your data with the same relative reference.

As you can see here, the formula in cell H11 is:

=SUM(E11+E21+E31)

This is because the relative reference is placed nine rows down from where it was originally. Now, rather than typing the formula for each month, you only have to type it once and then apply the same relative reference wherever you want on your Excel worksheet.

How to Use Absolute References in Excel

In this example, the company accountant wants to work out how much to pay the employees based on the number of hours they have worked and a fixed hourly rate.

Start by clicking the cell where you want the first total to be calculated (in this case, E2), and begin to type the appropriate formula. In this example, the accountant wants to multiply the hourly rate in cell A2 by the total hours worked by Jacinda in D2.

=SUM(A2

However, if we use a relative reference here, when we apply the formula to the other employees’ totals, the formula will reference cell A3 for Raul, A4 for Lucy, and so on, meaning you will not get the results you want.

So, we need to make sure that Excel continues to refer to the correct cell, which, in this case, is A2. To do this, you need to insert the dollar symbol before each part of your reference:

=SUM($A$2

This now tells Excel that if we use the same formula elsewhere, A2 will always be referenced.

You can now complete your formula. In this case, it will be as follows:

=SUM($A$2*D2)

Note how the second part of the formula (D2) does not use an absolute reference. This is because we want to multiply cell A2 by each employee’s respective hours, so that needs to be relative to each person’s hours in column D.

You can now apply the formula to the other cells by copying and pasting it or using Excel’s AutoFill function, knowing that cell A2 is referenced absolutely.

How to Use Mixed References in Excel

There may be occasions when you need to lock the row but not the column within your formula, or vice versa. For example, in this case, the accountant wants to work out how much tax each employee has to pay based on their individual tax rates.

Therefore, we need to lock the values in column E (the tax rates), but not the rows, as they are different for each employee, and not the references to the years, as they need to be adaptable.

Click the cell where you want the first calculation to be made (in this case, cell G2) and input your formula. In the example above, the accountant wants to begin by working out Arjun’s 2021 tax payable, so they would type the following formula:

=SUM(B2*E2)

We now need to lock column E. If we were to AutoFill to the right without the formula being amended, it wouldn’t correctly calculate Arjun’s 2022 tax payable; while it would accurately pick up the correct yearly income totals (because we are not locking cell B2), the formula would also reference cell F2. To do this, add a dollar symbol before the reference to column E in your formula:

=SUM(B2*$E2)

We don’t want to use an absolute reference here because that would mean that, when we work out the tax payable for the other employees, Arjun’s tax rate would be used instead of each employees’ individual rates. In essence, locking only column E means that we will stay on the tax rates but can relatively adjust to each person’s different rates.

We can now use the AutoFill function to complete Arjun’s tax payable totals, knowing that column E will remain locked in our calculations, whilst the totals for each year will move relative to where the formula is placed.

Because we have locked column E but left the rows relative within our formula, we can confidently complete the remaining employees’ totals based on their individual tax rates. Remember, you can click any cell within your results and see the formula in the formula bar to check the correct cells are referenced.

How to Switch Between Different Types of Cell References in Excel

If you want to easily toggle between relative, absolute, and mixed references in your Excel sheet, you don’t need to type the dollar symbol each time. Instead, click the cell you want to amend and then, in the formula bar, click the part of the formula you want to switch (clicking directly before the reference, in the middle of the reference, or directly after the reference will work).

In this case, we want to toggle between the cell reference types for cell G11, so we need to make sure to click somewhere on or next to that cell reference.

Then, press F4 and you will see the cell reference type change.

It will first switch to an absolute reference:

$G$11

Then a mixed reference with the row locked:

G$11

Then a mixed reference with the column locked:

$G11

And, finally, it will revert to the default, a relative reference:

G11

If you want to toggle several references in the same formula at the same time, simply highlight the relevant references in the formula and follow the same steps.

And you’re done! Who knew that the mere presence of a dollar symbol can help you to quickly and accurately organize your cell references in Excel?