How To Reference A Cell in Excel:
Absolute, Relative, and Mixed References Explained

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Most of the time, referencing a cell in Excel is super easy: you just click on it. Or type in the column and row. It’s rare that you need to know more than that.

But every once in a while, you’ll need to understand the details of cell references.

It can take a while to get your head around how relative, absolute, and mixed cell references work. We know; we’ve done it ourselves. So we’re going to make that process easier for you by laying it all out here.

By the time you’re done reading, you’ll be a cell reference expert!

The short version

There’s plenty of detail on all the types of cell references below. But in case you want to get it figured out as quickly as possible, we’ve put together this quick summary for you.

We’ll use this arrangement of numbers for our quick examples:

example-numbers

Let’s start with some simple addition to see how different types of references work.

First, relative references.

In cell F2, we’ll type =B2+1:

relative-addition

After hitting Enter, the result is 2. Excel is adding the 1 from cell B2 and the 1 we added in the formula.

We’ll use the fill handle to drag that formula two cells down, and then two cells to the right.

relative-addition-full

As you can see, Excel added 1 to all the cells in our original set of numbers.

This is the key to relative references: they’re always the same distance away from the new location of the formula.

B2 is four cells to the left of F2. When the formula is filled down to F4, it looks four cells to the left and finds B4. In essence, the cell reference moves with the formula whenever it’s copied or filled with the fill handle.

Don’t worry if you don’t quite get it yet; we’ll look at this idea in more detail in the next section.

Kasper Langmann, Co-founder of Spreadsheeto

Next, we’ll look at absolute references.

In cell F6, we’ll type =$B$2+1:

absolute-reference

Hitting Enter gets the same result as last time: 2.

Filling the formula two down and two to the right, though, gets a very different result:

absolute-addition

Every cell has the same value.

That’s because absolute references ALWAYS reference the same cell, no matter where the formula goes.

So all five of those cells are adding 1 to the contents of cell B2.

You could copy and paste a formula into a different sheet in the same workbook, and it still looks at the same cell.

Finally, let’s check out mixed references. As you might expect, they’re a combination of relative and absolute.

In cell F10, we’ll type =$B2+1:

mixed-reference

The fill process is the same as above. Surprised?

mixed-addition

To get a full explanation of why this happens, you’ll have to read the section below.

In short, though, mixed references lock either the column or the row.

F10, F11, and F12 all reference B2, because the column is locked by the dollar sign. G2 and H2, however, reference B3 and B4, because the row is free to vary.

If this answers all your questions, great! If not, keep reading to get more details and examples of why you might use these types of references.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

You can read all about cell references, but the best way to understand them is to actually use the different types of references in a spreadsheet and see what happens.

We’ve put together an example workbook that you can download for free to follow along. Download it now!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Relative cell references

This is the most common type of cell reference you’ll use in Excel. There are two ways you can create a relative reference in a formula:

  • You can click on a cell
  • You can type the location of the cell

On the second first of our example workbook, you’ll see a list of names with their annual earnings, and a blank column for their hourly earnings.

Let’s use relative references to calculate everyone’s hourly earnings quickly. We’ll divide their annual earnings by 2,087, an estimate of a full-time year’s worth of work hours.

First, click into cell C2 and type an equals sign (=).

Now, we need a cell reference. You can either click on cell B2 or type “B2″—either works.

relative-reference

Take note that there’s no dollar sign in this reference.

Clicking to create a relative reference is especially useful when you need to select an array of cells.

Kasper Langmann, Co-founder of Spreadsheeto

Finish off the formula (it should look like this: =B2/2087) and hit Enter.

relative-division

Now, use the fill handle to drag that cell down:

relative-fill

You’ll see that each cell has a unique value. To see how those values were calculated, click into cell C5 and take a look at the formula displayed in the formula bar.

relative-fill-formula

The formula for this cell is =B5/2087.

With relative cell references, the cells entered into a formula are always relative to where that formula is typed.

For example, the formula we entered in C2 references B2, a cell directly to the left. When that formula is copied (or, in our case, filled) to another cell, that relative relationship is maintained; the formula in C5 uses the cell directly to its left, B5.

If you use the fill handle to drag the cell in C5 to the right, you’ll get a much smaller number; because the new formula in D5 is referencing the cell directly to its left, C5.

This is probably the most common type of reference that you’ll use. In almost every case, relative references let you run the same calculations on lots of cells very quickly.

Kasper Langmann, Co-founder of Spreadsheeto

Absolute cell references

Absolute references are the opposite of relative references—they don’t change, no matter where a formula is copied or filled. In this way, they’re actually simpler than relative references.

They just aren’t as common.

To see a situation where you might use this, let’s look at the second sheet in our example workbook. Here, we have the same list of names and annual earnings.

We also have a multiplier column; we’ll use absolute references to multiply the earnings by that number.

Before we do, though, let’s see what happens when we use relative references.

Click into cell C3 and type =B3*C2, then hit Enter and fill that cell down a few spaces.

relative-multiplication-error

As you can see, we didn’t get 1.5 times each of the earnings values. Instead, we got 1.5 times the first earnings value, then the second times the first, the third times the second, and so on. This is the problem with relative references—you don’t always want the cell reference to change.

Let’s use an absolute reference instead.

Delete those values, and click back into cell C3. Type =B3*$C$2 and hit Enter. Then drag that cell down.

absolute-multiplication

That looks much more reasonable, doesn’t it?

The dollar signs before the column letter and row number lock them in place—no matter where you copy or fill the formula, it will always look to cell C2.

Protip: Insert the $ symbol using this shortcut.

Simply highlight the cell number (in this case C2) and click F4.
That’s it!

Mixed cell references

Placing a dollar sign before the column and row identifiers creates an absolute reference. Placing a dollar sign before either the column OR the row identifier creates a mixed reference.

The absolute reference locks both the column and the row. The mixed reference locks either one, but not both. Let’s take a look at how that works.

On the third sheet in our example workbook, you’ll find information on employees’ base salary, commissions, and bonuses. We want to apply the bonus percentage to both the base salary and the commissions. Let’s try that with relative references first.

We’ll click into D3 and type =C3*B3.

That gives us the correct number:

bonus-relative

Even after we use the fill handle to drag that cell down, we get the correct numbers:

bonus-rel-fill

Now let’s copy that column over to column F to get the commission plus bonus:

bonus-inflation

Something has clearly gone wrong. And if you look in the formula bar, you’ll see what it is: the formulas in column F are multiplying columns E and D, instead of E and B.

We can fix that with a mixed reference.

Clear columns D and F, and click back into D3. This time, we’ll enter a slightly different formula: =C3*$B3

mixed-multiplication

This ensures that the second number being multiplied always references column B. Because there’s no dollar sign before the row number, it can change freely with the new location of the formula. As can the first number in the formula. But the second number will always come from column B.

Hit Enter, drag the cell down, and copy and paste it over into column F.

copied-mixed-reference

This time it works perfectly.

Mixed references can be a bit tricky to get your head around, but keep an eye out in your own work for places you can use them. Once you’ve saved some time by using them, you’ll get much more comfortable with them!

Kasper Langmann, Co-founder of Spreadsheeto

While there are many situations where mixed references are useful, you’ll probably find them most practical in situations like this, where you’re copying and pasting cells that need to reference the same multiplier or divisor.

Conclusion

Cell references are one of the most foundational parts of Excel that you need to understand.

Once you start using absolute and mixed references, you’re well on your way to becoming an Excel expert!

Think about the spreadsheets you work with often and how you might be able to save some time by using these kinds of references. It might not be obvious at first, but you’ll get much better with practice.

2019-03-28T13:31:53+00:00