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

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:

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:

To 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

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

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:

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.

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

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.

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

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

For example, for Ana the formula is “=D5*$G$3”.

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:

For 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:

We obtain:

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:

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.

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

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

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

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

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:

Then, by dragging to the right:

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: