Learn the Ins and Outs of Using Microsoft Excel’s Cell Reference Types

When you start working with Microsoft Excel, one of the essential things you need to understand about the program is cell references and how they work. To understand cell references, you first need to understand the composition of an Excel worksheet.

Excel worksheets contain columns that go across the page. Columns use letters as their name starting at A and increasing by one through column XFD. Each column has a header with its letter. Clicking a column header will select the column.

Figure 1 — Worksheet columns and column headers.

Excel worksheets also contain rows that go down the page. Rows use numbers as their name, starting at 1 and increasing by one through row 1048576. Each row has a header with its number. Clicking a row header will select the row.

Figure 2 — Worksheet rows and row headers.

The intersection of a column and a row is a single cell. Each cell has a name. The cell’s name (or reference) is a combination of its column letter and row number. The top-left cell in a worksheet has the cell reference of A1.

Figure 3 — A worksheet cell and its name.

Each cell in an Excel worksheet can contain a piece of information. Some examples of what an Excel cell can hold include:

• A number.
• Some text.
• A date.
• A time.
• The result of a formula.
• Several things joined together to become one string of characters.

You can use the value from one cell in a worksheet as part of an expression (or formula) in another cell in the worksheet. Formulas allow you to perform calculations.

Figure 4 — Using cell references.

As an example, Figure 4 shows the hours worked for each day and the total for the week. In the image:

• An expression, which you can see in the formula bar, is typed into cell C10:
  =+C5+C6+C7+C8+C9
• The expression adds together the values in cells C5 through C9:
  8, 7.5, 8.5, 4, 8
• The result of the expression is in cell C10:
  36

When Excel executes an expression, each cell reference in the expression is replaced by the value in the cell. So, the expression in cell C10 in our example becomes:

8 + 7.5 + 8.5 + 4 + 8

This results in the answer of:

36

When you select a cell in Excel, its expression is visible in the formula bar, and the result of the expression is in the cell.

Now that you understand what cell references are and how to use them let’s look at the different types of cell references you can use in Excel.

Different Types of Cell References

Part of the power of using Microsoft Excel is copying an expression from one cell and using it in another cell. How a cell reference behaves when copied to a new cell depends on its type.

There are three cell references types in Excel:

• Relative
• Absolute
• Mixed

In Figure 4, all the cell references were relative.

Let’s look at what happens when you copy relative cell references.

Relative Cell References

The simplest and most widely used cell reference type is the relative cell reference. When you copy a formula with this type of cell reference to another cell, the cell reference will change relative to how far away it is from its original location.

The table below describes what happens to a relative cell reference when you copy it to another cell:

Figure 5 — Explanation of how relative references change when copied.

To demonstrate how relative cell references change, I created some sample data. In this data, I entered the values 1 through 9, in cells B6 through D8:

Figure 6 — Highlighting data entered for relative cell reference example.

Next, I added a formula to cell G7 that referenced cell C7. The formula is:

=C7

As you can see in Figure 7, the formula returned the value of “5”, which is the value in cell C7.

After adding the formula to cell G7, I copied it to cells F6 through H8.

Figure 7 — Highlighting output from formulas for relative cell reference example.

The animation in Figure 8 shows the formula entry and copying process in action:

Figure 8 — Typing formula in cell G7 and copying it to surrounding cells.

As a final step, I used a built-in Excel function called the FORMULATEXT function to display the formulas entered in cells F6 through H8 as output in cells J6 through L8. This way, you can see each formula used in the “Output from Formulas” area without looking at the formula bar.

Figure 9 — Highlighting formulas used for relative cell reference example.

Here is the sample data, without any explanation bubbles:

Figure 10 — Relative cell references example.

As you can see in Figure 10, when I copied the formula in cell G7 to cells G6 through H8, the cell references changed based on how far away they were from the original cell. Since cell G6 was one row above cell G7, when I copied the formula in cell G7 to cell G6, it changed from “=C7” to “=C6”. The row number decreased by one since I copied the formula to a cell with a lower row number than the original cell.

When I copied the formula in cell G7 to cell H8, the column letter and row number each increased by one because the new cell was one column letter higher and one row number higher.

Looking back at our week of data, I’ve added a column for the number of Calls for each weekday. Then, I copied the formula in cell C10 to cell D10 to get the total number of calls for the week. When I did this since the formula was copied one column higher than the original cell, the column letter for each cell reference in the formula increased by one (changed from “C” to “D”).

Figure 11 — The column of the cell references increased by one because I copied the formula one column higher.

With relative cell references, it’s simple to reuse formulas and make data entry less tedious. However, there will be times when you want to keep all or part of the cell reference the same, no matter where you’re copying it. The other cell reference types address this need.

Absolute Cell References

When you want to copy the same cell reference to different cells and keep it from changing, you’ll use an absolute cell reference. To designate a cell reference as absolute, place a dollar sign ($) before the column letter and the row number.

This sample data shows what happens when you copy an absolute cell reference from one cell to another:

Figure 12 — Absolute cell references example.

In Figure 12, I added the formula “=$C$7” to cell G7. Then, I copied the formula to cells F6 through H8. All of the values in cells F6 through H8 are “5”, which is the value from cell C7. Since the cell reference in cell G7 was absolute, it does not change when you copy it to other cells. Also, notice that the FORMULATEXT results in cells J6 through L8 show “=$C$7”. These are the cell references used in cells F6 through H8.

If we add to our Hours and Calls example, we can see how using an absolute cell reference can benefit us. Let’s say that for each call made, you receive a $0.25 bonus. We could add a column to our data and, in each cell, multiply the number in the Calls column by 0.25 to get that day’s Bonus.

Figure 13 — Bonus calculated by multiplying Calls by $0.25.

Each cell in the Bonus column multiplies the number of Calls for that row by 0.25 to get the Bonus amount. Hard coding the 0.25 was easy to do, but what if we wanted to change the bonus amount? Then, we would have to change each cell’s formula to the new bonus amount. Of course, you could enter the updated formula in the first cell and copy and paste it to other cells, but there’s an even easier way to set up the formulas.

If we place the bonus amount in a cell and reference that cell in our formulas, then any time we want to change the bonus amount, all cells referencing that cell will automatically update.

Figure 14 — Referencing the Bonus per Call cell using a relative cell reference.

In Figure 14, we added the Bonus per Call label in cell B4 and the Bonus per Call amount in cell C4. In cell E7, we wrote the formula for Monday’s Bonus:

=D8*C4

Then, when we copied the formula down the Bonus column, we got incorrect results. Each time the copied formula moved to a new row, the Bonus per Call reference updated because the cell reference C4 is relative. So, in cell E8, the C4 cell reference changed to C5. Since there is no value in cell C5, Excel interpreted it to be a 0 and, when multiplied by the 152 calls, it displayed the result of $0.00.

Wednesday’s Bonus result is even stranger. The formula is:

=D9*C6

It attempts to multiply the 208 Calls by “Hours,” which is the text in cell C6. Unfortunately, you cannot multiply a number by text, so the result is the #VALUE! error.

To fix the problem with our formulas, we need to add a dollar sign before the column letter and row number to change the cell reference for the Bonus per Call cell from C4 to $C$4.

Figure 15 — Referencing the Bonus per Call cell using an absolute cell reference.

In Figure 15, in cell E7, I wrote the formula:

=D7*$C$4

Then, when I copied the formula down the Bonus column, the Calls cell reference updated for each formula, but the Bonus per Call remained $C$4. Using an absolute cell reference gave us the correct results in cells E7 through E12.

If you want to change the Bonus per Call, maybe make it $0.45, you change it in cell C4, and all the Bonus amounts in column E will update automatically; we don’t have to change any of the formulas. Figure 16 shows the data with the updated Bonus per Call amount:

Figure 16 — Updating Bonus per Call automatically updated all Bonus cells.

Now that we’ve looked at relative and absolute cell references let’s look at the final cell reference type: mixed cell references.

Mixed Cell References

In mixed cell references, part of the cell reference is relative, and part of it is absolute.

You can have:

• An absolute column and a relative row

OR

• A relative column and an absolute row

The portion of the cell reference that is absolute will have a dollar sign before it, and the relative part won’t.

When you copy a formula with a mixed cell reference to a new cell, the relative portion will change in the new cell. However, the absolute part of the cell reference will remain the same, no matter where the formula was copied from or to.

The sample data in Figure 17 shows what happens when you copy a mixed cell reference that has an absolute column and a relative row:

Figure 17 — Mixed cell references with absolute column and relative row.

As you can see, the column letter does not change, no matter where you copy the formula. However, the row number increases or decreases based on whether you copied it to a higher row or lower row.

The sample data in Figure 18 shows what happens when you copy a mixed cell reference that has a relative column and an absolute row:

Figure 18 — Mixed cell references with relative column and absolute row.

With this type of mixed reference, the column letter increases or decreases based on where you copy the formula, but the row number does not change.

To see the mixed cell references in action, I’ve updated our weekday data. Here are the changes I made:

• Removed the Bonus per Call label in cell B4
• Removed the Bonus per Call amount in cell C4
• Removed the Bonus column from column E
• Added three new Bonus per Call columns (E, F, and G)
• Added cells to show all the formulas (cells I6 through K11)

Figure 19 — Copying mixed references example.

In cell E6, we wanted to calculate the Bonus by multiplying the Calls in cell D6 by the $0.25 Bonus per Call value in cell E5. We also wanted to ensure that we could use the same formula in all the Bonus per Call cells (E6 through G10).

To use the same formula in cells E6 through G10:

For the Calls cell references:

• The column letter will always be D because the Calls are all in column D, so this will need to be absolute.
• The row number will need to change for each formula to reference the Calls for that row, so this will need to be relative.

For the Bonus per Call references:

• The column letter will need to change for each formula to reference the Bonus per Call for that column, so this will need to be relative.
• The row number will always be 5 because all the Bonus per Call amounts are in row 5, so this will need to be absolute.

The formula in cell E6, which calculates a $0.25 bonus for Monday’s calls, is:

=$D6*E$5

When you copy the formula above to a new row, the row number in the Calls cell reference ($D6) will change, but the row number in the Bonus per Call cell reference (E$5) will not change. Likewise, when you copy the formula above to a new column, the column letter in the Calls cell reference will not change, but the column letter in the Bonus per Call cell reference will change.

For example, when you copy the formula in cell E6 to cell F7, it becomes:

=$D7*F$5

Even though you copied the formula over one column to the left, the column letter did not change in the Calls cell reference because it is absolute, but the column letter in the Bonus per Call cell reference changed since it is relative.

And because the formula was copied one row down, the row number in the Calls cell reference changed, but the row number in the Bonus per Call cell reference did not change.

I used a shortcut to quickly add the formula in cells E6 through G11 all at once. Here’s how I did it:

1. I selected cells E6 through G11.
2. Then I typed the formula in cell E6.
3. Finally, I pressed CTRL+ENTER to enter the same formula in all the selected cells.

Figure 20 shows this process in action:

Figure 20 — Using CTRL+ENTER to update selected cells with identical entries.

Moving Cell References

So far, all the examples in this article demonstrate how cell references are impacted by copying them to other cells. But what happens when you move a formula from one cell to another?

The result is that all the cell references remain the same. So, if you have a formula that references various cells throughout your worksheet, you don’t have to worry about breaking your formula if you want to move it to another cell in your worksheet.

Cell Reference Type Shortcut

Typing dollar signs in your formulas can get a bit annoying. So Excel has a shortcut key you can use to update a cell reference to any of the three cell reference types (actually four types if you count each of the two mixed types). When you have a cell reference selected or have your insertion point in or near the cell reference, press the F4 key. Each time you press F4, the cell reference type will change. Here are the four options the F4 key will cycle through:

• Relative
• Absolute
• Relative Column / Absolute Row
• Absolute Column / Relative Row

Figure 21 — Using F4 to cycle through cell reference types

If you want to change multiple cell references in your formula, select all of them, and they will all change each time you press the F4 key. Just be aware that if you choose multiple cell references, they will all change to the same type; you cannot change one to absolute and another to relative, for instance. So, if you need different cell reference types in a formula, you will either need to type each one or use the F4 shortcut on each cell reference separately.

Conclusion

So now you should have a clearer understanding of the different cell reference types you can use in Microsoft Excel. They may take a little time to get used to, especially the mixed cell reference type, but once you have them down, using the right type can save you time when you want to reuse formulas within a worksheet.