# Absolute and relative cell references

One of the magical parts of working with formulas in Excel is that hard-coding values into your formulas is not necessary (in fact, it is not advisable). Whenever you enter a value into a formula, like

You can also include cell ranges as cell references in the form

When you reference cells in this manner, you can do so with one or two "reference types":

## Relative cell references

Take a look at the following worksheet, which lists SnackWorld sales by month for various product categories.

Let's say that we want to sum up sales for each of these categories across all three of the months in our spreadsheet. Starting in cell C7, let's use a

=SUM (C4:C6 )

Output:$16,000,000

We've got our sum for

=SUM (D4:D6 )

Output:$24,000,000

Notice that in cell D7, our formula has changed. Rather than referencing the range

The answer is that we are using a relative cell reference in this formula. As we copy and paste it from cell to cell, the cells referenced will change relative to the cell that we started in and the cell that we copy and paste into. In this case, relative cell references are working to our advantage — we want our

## Absolute cell references

Relative references work great in certain situations, but there are some times during which you may not want your formulas to change. Let's say that we want to insert another section in row 8 that calculates the taxes SnackWorld will owe on the revenue it collected over the three months of data:

First, we'll make a new cell,

=C7 *H4

Output:$4,800,000

This formula works well for cell

=D7 *I4

Output:0

It looks like the formula has broken, because our relative reference to cell

We indicate absolute cell references with the

Take a look at a new formula we've written for cell

=C7 *$H4

Output:$4,800,000

When we paste this formula into the adjacent cells, the column letter does not change, because it is an absolute reference. Our problem is solved!

=D7 *$H4

Output:$4,800,000

## Absolute references can appear on rows or columns

An important thing to remember here is that absolute cell references can appear on rows, columns, or both. Here's a handy table that will show you what the

Format | Meaning | Explanation |
---|---|---|

$A$1 | Row and column locked | Cell reference will not change at all as cell is copied and pasted. |

$A1 | Column locked | Only row reference will change as cell is copied and pasted. |

A$1 | Row locked | Only column reference will change as cell is copied and pasted. |

A1 | Nothing locked | Both row and column will change as cell is copied and pasted. |

Note that you can also lock rows and columns in a range. For example, the range

## Hotkeys

The hotkey to cycle through locked rows, columns, and both is one of the most-used shortcuts in Excel. When typing a formula in the formula bar, use `F4` to cycle through lock states on Windows and `⌘`+`T` on a Mac. You'll need your cursor to be on a reference to a cell for this to work, so that Excel knows which cell to toggle.

## Additional practice

For practice, take a look at the four images below, in which the formula in cell

Absolute and relative cell references are one of the most useful — and confusing — features of Excel. So be sure to play around with these at length until you are comfortable with them. It'll pay off down the road!

## Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive **must-learn lessons** on getting Excel to do your work for you.

- How to create
**beautiful table formatting**instantly... - Why to
**rethink the way you do VLOOKUPs**... - Plus, we'll reveal
**why you shouldn't use PivotTables**and what to use instead...