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
We've got our sum for
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,
This formula works well for cell
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
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!
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
|$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
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.
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...