Chapter 2 of my Ebook "5 Reasons Your Spreadsheet is Misbehaving." You can download a copy on the Free Stuff! page.
Make sure Excel is looking in the right place for the information it needs.
One of Excel’s best productivity features is the ability to copy data from one cell and paste it to other cells in the spreadsheet. This feature can save you hours of time because you do not have to manually enter the same data in one cell after another.
However, you have to know the difference between a relative and an absolute cell reference. If you don’t, you will have errors in your spreadsheet.
What You Need to Know
Relative cell reference – changes if you copy or move a formula to another cell.
Absolute cell reference – does not change if you copy or move a formula to another cell.
The dollar sign ($) is used to denote an absolute cell reference.
Here are examples of a relative and absolute cell reference:
Relative cell reference: A1
Absolute cell reference: $A$1
Relative and absolute cell references are one of the most difficult Excel concepts. So don’t get frustrated if you don’t understand it right away. Once you get it, though, you’ll be in the upper percentile of skilled Excel users.
The first step is to understand what we mean by a “cell reference”. Simply put, a cell reference is a cell to which a cell or formula refers. In Picture 1 below, the cell reference is in cell D4 (shaded green) and it refers to cell E5 (shaded yellow).
Picture 1: Example of a cell reference
Understand that Excel views this picture differently than you do. You, the user, see the cell reference in cell D4 as an arrow, pointing you to cell E5. But Excel sees cell D4 as a map, with directions on how Excel gets to cell E5 from cell D4. Put another way:
Users see a cell reference as a location.
Excel sees a cell reference as the path to a location.
Let’s look at some examples!
Here’s how it works. Refer to Picture 2 below as you review each step. In cell A1, you enter the cell reference “=E6.” (Cell A1 is the active cell.) Excel sees it takes two steps to move to cell E6. First, Excel must move right 4 columns relative to cell A1. Second, Excel must move down 5 rows, relative to cell A1. So, to Excel, “=E6” means “move right 4 columns, then move down 5 rows”.
Picture 2: How Excel sees a cell reference as the path to a location in the spreadsheet
Make sure you understand what is happening in Picture 2 before you continue! Once you master this concept, everything that follows will be much easier to understand.
So what happens if we copy the formula in the active cell, A1, to another cell? What will Excel show as the cell reference in the new location? The answer is in Picture 3.
Picture 3: Results from copying the formula with a relative reference in cell A1 to cell C8
Remember that Excel sees the cell reference as a set of directions. In our example, Excel interpreted the formula in the active cell, A1, to mean “move right 4 columns, then move down 5 rows”.
So it doesn’t matter where you copy the formula from the active cell, A1. Excel will always remember it as a set of directions to move right 4 columns, then down 5 rows. So if you copy the formula from A1 to C8, Excel will look right 4 columns, relative to cell C8, then down 5 rows relative to cell C8, and end up at cell G13.
Do you see how it is the same??
Cell E6 is 4 rows right, 5 rows down relative to cell A1.
Cell G13 is 4 rows right, 5 rows down relative to cell G13.
This is an example of a relative cell reference. This is because all of Excel’s moves from one cell to another are relative to the active cell.
Remember that a relative cell reference changes when you copy or move a formula from one cell to another. So if the cell reference is different after you copy it, you know you have a relative cell reference.
Another way to tell you have a relative cell reference is the absence of dollar signs in the cell reference. The dollar sign is the symbol uses to indicate an absolute cell reference.
Let’s take a look at how you might use relative cell addresses in a real spreadsheet. Look at the picture below.
Picture 4: Real-world example of relative cell addresses
How does Excel read the formulas in row 6 (highlighted in yellow)? Add the following:
1. The value one row up relative to row 6 Plus
2. The value two rows up relative to row 6 Plus
3. The value three rows up relative to row 6.
It doesn’t matter what column the formula is in. Excel will always read it the same way. Now take a look at Picture 5.
If you’ve understood everything so far, you’re ready to learn about absolute cell references. You’ll find that, having mastered the concept of relative cell addresses, absolute cell addresses will be easy to learn.
The most important thing to remember about absolute cell references is that they do NOT change when you move or copy a formula from one cell to another. Recall that relative cell references DO change when you move or copy a formula from one cell to another.
So let’s look at some examples to demonstrate the concept. Here’s Picture 3, which you saw earlier:
Picture 3: Results from copying the formula in cell A1 to cell C8
Here’s what happens if we change the cell reference in cell A1 from a relative to an absolute cell reference:
Picture 6: Results from copying a formula with an absolute cell reference in cell A1 to cell C8
Picture 7: Results of copying a formula with an absolute cell reference to another cell
So check your spreadsheet to make sure you are using the correct cell reference – relative or absolute. When you use the proper type of cell reference, you will avoid multiple errors in your spreadsheets.