Ninja Excel BlogExcel formulas and functionsAbsolute and mixed references in Excel: learn how to use them

Absolute and mixed references in Excel: learn how to use them

Woman in front of blackboard

Absolute and mixed references are essential knowledge that you must master when using Excel. In this post we give you everything you need to know to apply them correctly.

Key information about absolute and mixed references

A ABSOLUTE REFERENCE is to “lock” or “pin” the columns and/or rows of an Excel cell so that they do not change when the formula is copied from one cell to another. Unlike a relative reference, it is intended to fix a certain cell for use in formulas.

Reference Basics

Concept: A reference is the use of a cell in an Excel formula. Instead of writing in cell A1 “=2+5”, what you do is give the value 2 to cell A2, the value 5 to A3 and finally in A1 you write “=A2+A3” to obtain the result.

Purpose: It allows you to give flexibility to the formulas, in the sense that if you change the value of one of the cells, the value that the reference takes will automatically be adjusted. In the same previous example, if A2 changed to 7, the value of A1 is adjusted to 12 (7+5) without you doing anything. Furthermore, since it is automatic, errors are avoided and time is saved.

Introduction: relative references

To understand an absolute reference, we must first understand relative references in Excel. Relative references are the “normal” way one writes formulas, indicating a cell (or set of cells).

Thus, the referenced cell will change as you copy the formula into another cell, since what Excel understands is the relative position of the cells. It is easier to understand it with the following example:

First example relative references Excel

We have a table in which we have the hourly payment and the hours worked in order to obtain the total payment for each worker. Thus, we need to multiply the hourly payment by the hours worked. To do that, we write =C3*D3 in cell E3:

Explanation of relative references in ExcelTo fill in the rest of the table we must drag the formula using the lower right corner of cell E3 or by double clicking on this same corner

Click drag relative references Excel
Final result relative references Excel

As seen in the image, Ana worked 3 hours and is paid $13 per hour. Thus, your total payment is $39 and we see that the formula in E5 is =C5*D5. This is a relative reference: Excel remembers the formula as “multiply the cell on the left by the one two to the left.”Explanation of relative references in Excel

Absolute references

When we want to use an Excel formula but we want to fix a cell so that it does not move if we copy or drag the formula we use absolute references. This way we can write the formula once and we don't have to rewrite the formulas in each cell.

Let's think about the previous example, but everyone is paid $11 per hour worked, as seen in the image:

Excel absolute references example

Thus, they must all be multiplied by the same factor. If we used relative references in D3 we would write “=C3*G3” and the first result will give us correct.

Applying relative references incorrectly. Excel Absolute References
Result of a relative reference in Excel

However, if we drag the formula, we see that the other values we got are $0.

Drag relative references with incorrect result. Excel Absolute References

Why is this? Because the reference is relative. For example, for Ana we see that the formula is “=C5*G5” and G5 does not have any value assigned and Excel takes it as 0.

Incorrect use of relative reference in Excel. Absolute reference in Excel

So how do we fix cell G3 so that it doesn't move when dragging or copying the formula? We will do this using the peso or dollar sign ($) when writing the cell we want to set. Applying it to the example, we write in cell D3 “=C3*$G$3”.

Excel absolute reference correctly usedThen, if we drag the formula (or copy the formula below) we see that the cell on the left (hours worked) is always multiplied by cell G3.

Absolute references in Excel example correctly used For example, for Ana the formula is “=D5*$G$3”.

Correct example of absolute reference in Excel

Ninja Tip: Don't spend time writing “$” by hand to make absolute references in Excel, you can apply it by pressing F4 or Fn+F4 on Windows or Cmd+T on Mac. You can advance your knowledge regarding shortcuts in Excel by visiting this post if you use Windows or this one if you use Mac.

Mixed references

A mixed reference is a reference that is part relative and part absolute. That is, some part is kept “free” and another part is fixed. These types of references are used to copy or drag formulas without having to rewrite or edit them manually, saving time and avoiding errors.

In general it is to set only one column or only one row:

  • Using “=$A1” fixes column A, letting the row move
  • Using “=A$1” fixes row 1, letting the columns move

To illustrate mixed references, we will see the following example in which we must fill out a table with the commissions of each sales amount for each commission percentage:

Mixed reference template absolute reference in ExcelFor example, if we want to fill out the following table in which we must extract the amount of a commission for each product price and each commission percentage, if we use relative references we obtain and drag:

Incorrect use of relative references in Excel. Mixed references in ExcelWe obtain:

Relative references in Excel with incorrect use. Mixed references in Excel.Ninja Tip: When “########” appears in a cell, it is not that there is an error in the formula, but that the number is so large that it takes up more space than the width of the cell. If we adjust the width of the cell we can see the full number:

Explanation of what cat signs are. Mixed references in Excel. Relative references in Excel.

We see that, for example, for cell D5, cells C5 are being multiplied by D4 and the prices column and the commission percentage row were not fixed.

Incorrect use of relative references in Excel. Absolute references in Excel.To solve this problem, we could use absolute references in C4 write “=B4*$C$3” and drag down, so that for each price it is multiplied by 10%.

Example absolute reference in Excel and mixed reference in Excel

This would give us the correct result by dragging down, but dragging to the side does not give us a correct result:

Using Absolute References in Excel and Mixed Reference in ExcelIncorrect use of absolute reference in Excel, mixed reference ExcelIf we look closely, in cell D5 we see that it uses the cell to its right because it continues with relative references and multiplies it by the commission of 10% because we set cell C3.

Incorrect use of absolute references in Excel. Mixed Excel References

One solution is to do the same process of absolute references for each column, but that can be a lot of work if we are working with more data and there is a greater chance of making errors.

To solve this, we use mixed references. To do this, in cell C4 we will write “=$B4*C$3”.

Mixed references in Excel example

What does this mean?

As for “$B4”, we are setting column B so that when we drag the formula to the right, column B continues to be referenced, but when we drag down it does move from row to B5, B6, etc.

As for “C$3”, we are setting row 3 so that when we drag down it still refers to row 3, but when we drag to the right it does move columns towards D3, E3, etc.

As seen in the image:

Mixed reference explanation in Excel

Then, by dragging to the right:

Example mixed reference in Excel, drag down and right correctly
Example mixed reference in Excel, drag down correctly with explanation

Ninja Tip: The shortcut for making mixed references is by pressing F4 on Windows or Cmd+T on Mac a second or third time. For example, if you have cell “B2” and press F4, we will get “$B$2”. If we press it a second time we get “B$2”. If we do it a third time we get “$B2”. Finally, a fourth time, it returns to “B2”. In summary:

Number of times F4 or Cmd+TResult
1 time$B$2
2 timesB$2
3 times$B2
4 timesB2
white ninja excel logo

The leading Excel training platform for companies.


Copyright © 2024 Ninja Excel.