# Did You Use the Correct Cell Reference?

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.

*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.