If you have worked a lot in Excel, you have probably encountered absolute and relative cell references before. Most people have likely had to learn about these references after noticing that their formulas or functions sometimes behave unexpectedly. Therefore, this article will discuss the differences between an absolute and a relative cell reference.

A cell reference refers to a cell, e.g., cell A1, or a range of cells, e.g., A1:A10. These cell references are most often used in formulas and functions so that Excel can locate the values you are referring to or extract data from them.

In a formula, you do not necessarily have to reference a cell or range in the same worksheet you are working in. There are concepts such as external references and external program references. External references mean referring to another worksheet, while external program references mean referring to cells in other programs. While it can be useful to know about these, they will not be covered further in this article.

Another point worth noting is that you can also refer to a named range. For example, the range B1:D21 can be named “ProductList.” For simplicity, this article will not explore named ranges further, and we will instead use actual cell ranges. Consequently, we will also set aside external and external program references and focus solely on absolute and relative references.

Relative cell references are the default type of references that Excel uses automatically, without any additional adjustments.

The range A1:A10 is a relative reference. It is simply the combination of column letters and row numbers without any dollar signs ($). When you copy a cell downward, the relative cell address changes according to the relative position of the column and row. Of course, this is best illustrated with an example:

In the example, a simple formula can be seen in cell N2, and you can also see that the relevant cells for the formula, L2 and M2, are highlighted.

If you then copy the formula downward, go to N3 and click in the formula bar, you will see that the references have moved down one row, while the columns remain the same. The relative references for L2 and M2 change to L3 and M3, and so on. This happens depending on the relative position of the row.

What is an absolute cell reference?

An absolute cell reference is one where both the row and column are made constant by adding dollar signs. You can think of it as “locking” the column and row by simply placing a $ before the column letter and row number. An absolute reference does not change when you copy the formula from one cell to another.

You can also create a mix. For example, you can lock the column but not the row, or vice versa. This is called a mixed reference. Of course, there is a shortcut to make a cell reference absolute: press F4 while the cursor is in or next to the cell reference in the formula bar.

The image above is an example of both absolute and relative references. Here, relative and absolute cell references are mixed, but this is not what is called a mixed reference. More on that later.

A new column has been added since the previous example, called “Growth Factor.” It is used to multiply the sum of Q1 and Q2.

If you copy the formula downward and evaluate it, you will notice that the formula tries to fetch values from empty cells. However, if you make the cell absolute, this will not occur.

In this case, it is sufficient to lock only the row, i.e., N$2. If you lock only the column ($N2), the same error as in the previous example will occur—the formula will try to pull data from empty cells.

When would you need to lock the column? For simplicity, I transpose the matrix with =TRANSPOSE(K1:O6) in an arbitrary cell, delete the old matrix, and paste the new matrix as values, then fill in the correct formulas from the previous example. If you now want to copy the formula in the image below to the right, you can choose to use either an absolute or a mixed cell reference. For this example, a mixed reference is used, where only the column is locked.

Summary

This article has explained what relative and absolute cell references are, the differences between them, and how a mixed reference can be used when needed. It’s important to have a basic understanding of these concepts, as things can get trickier when working with long, nested functions or formulas. It can become confusing when a formula references many cell ranges or intervals.

The main takeaway from this article is to, as much as possible, evaluate your formulas and cell references in a similar way. At the same time, a bit of trial and error isn’t necessarily wrong, but if you want to save yourself some trouble, I recommend checking out our Excel Essentials course, which covers relative and absolute cell references. If you want to apply your knowledge of absolute and relative references in a more practical way, you should take theExcel Functions course. For those who want to follow along with the relatively simple examples presented here, the accompanying Excel file can be found here.

/ Niklas