This chapter is from the book
This chapter is from the book
This chapter is from the book
Copying and Moving Formulas
You copy and move ranges that contain formulas the same way that you copy and move regular ranges, but the results aren't always straightforward.
For an example, Figure 3.3 shows a list of expense data for a company. The formula in cell C11 uses the SUM() function to total the January expenses in range C6:C10. The idea behind this worksheet is to calculate a new expense budget number for 2011 as a percentage increase of the actual 2010 total. Cell C3 displays the INCREASE variable. In this case, the increase being used is 3 percent. The formula that calculates the 2011 BUDGET number, which is in cell C13 for the month of January, multiplies the 2010 TOTAL by the INCREASE, which is =C11*C3.
)
Figure 3.3 A budget expenses worksheet with two calculations for the January numbers: the total in Cell C11 and a percentage increase for next year in Cell C13.
The next step is to calculate the 2010 TOTAL expenses and the 2011 BUDGET figure for February. You could just type each new formula, but you can copy a cell much more quickly. Figure 3.4 shows the results when you copy the contents of cell C11 into cell D11. As you can see, Excel adjusts the range in the formula's SUM() function so that only the February expenses in cells D6:D10 are totaled. How did Excel know to do this? To answer this question, you need to know about Excel's relative reference format, which is I discuss in the next section.
)
Figure 3.4 When you copy the January 2010 TOTAL formula to February, Excel adjusts the range reference automatically.
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 two." This is called the relative reference format, which is 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 two." However, the formula changes to =A2*2 because A2 is two rows above A4.
Figure 3.4 shows why this format is useful. You only had to copy the formula in cell C11 to cell D11. Thanks to relative referencing, everything came out perfectly. To get the expense total for March, you need 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 care when copying or moving formulas. Let's see what happens if you return to the budget expense worksheet and try copying the 2011 BUDGET formula in cell C13 to cell D13. Figure 3.5 shows that the result is 0!
)
Figure 3.5 Copying the January 2011 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 2010 TOTAL, which is fine. However, instead of the INCREASE cell in C3, the formula refers to a blank cell in D3. Because Excel treats blank cells as 0, 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—that I discuss in the next section.
Understanding Absolute Reference Format
When you refer to a cell in a formula using the absolute reference format, Excel uses the physical address of the cell. You tell the program that you want to use an absolute reference by placing dollar signs ($) before the row and column of the cell address. To return to the example in the preceding section, Excel interprets the formula =$A$1*2 as "Multiply the contents of cell A1 by two." No matter where you copy or move this formula, the cell reference doesn't change. When this occurs, the cell address is said to be anchored.
To fix the budget expense worksheet, you need to anchor the INCREASE variable. To do this, you first change the January 2011 BUDGET formula in cell C13 to read =C11*$C$3. After making this change, copying the formula to the February 2011 BUDGET column gives the new formula =D11*$C$3, which produces the correct result.
You also should know that you can enter a cell reference using a mixed-reference format. In this format, you anchor either the cell's row by placing the dollar sign in front of the row address only such as B$6 or its column by placing the dollar sign in front of the column address only such as $B6.
Copying a Formula Without Adjusting Relative References
If you need to copy a formula but don't want the formula's relative references to change, follow these steps:
- Select the cell that contains the formula you want to copy.
- Click inside the formula bar to select it.
- Use the mouse or keyboard to highlight the entire formula.
- Copy the highlighted formula.
- Press Esc to deselect the formula bar.
- Select the cell in which you want the copy of the formula to appear.
- Paste the formula.