• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 3. Building Basic Formulas > Understanding Relative Reference Format

Understanding Relative Reference Format

When you use a cell reference in a formula, Excel looks at the cell address relative to the location of the formula. For example, suppose that you have the formula =A1*2 in cell A3. To Excel, this formula says, “Multiply the contents of the cell two rows above this one by 2.” This is called the relative reference format, and it's the default format for Excel. This means that if you copy this formula to cell A4, the relative reference is still “Multiply the contents of the cell two rows above this one by 2,” but the formula changes to =A2*2 because A2 is two rows above A4.

Figure 3.4 shows why this format is useful. You had to copy only the formula in cell C11 to cell D11 and, thanks to relative referencing, everything comes out perfectly. To get the expense total for March, you would just have to paste the same formula into cell E11. You'll find that this way of handling copy operations will save you incredible amounts of time when you're building your worksheet models.

However, you need to exercise some care when copying or moving formulas. Let's see what happens if you return to the budget expense worksheet and try copying the 2005 BUDGET formula in cell C13 to cell D13. Figure 3.5 shows that the result is 0!

Figure 3.5. Copying the January 2004 BUDGET formula to February creates a problem.


What happened? The formula bar shows the problem: The new formula is =D11*D3. Cell D11 is the February 2004 TOTAL, and that's fine, but instead of the INCREASE cell (C3), the formula refers to a blank cell (D3). Excel treats blank cells as 0, so the formula result is 0. The problem is the relative reference format. When the formula was copied, Excel assumed that the new formula should refer to cell D3. To see how you can correct this problem, you need to learn about another format: the absolute reference format.

NOTE

The relative reference format problem doesn't occur when you move a formula. When you move a formula, Excel assumes that you want to keep the same cell references.


  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint