This chapter is from the book
This chapter is from the book
This chapter is from the book
Copying and Moving Formulas
In Chapter 1, I showed you various techniques for copying and moving ranges. The procedures for copying and moving ranges that contain formulas are identical, but the results are not always straightforward.
For an example, check out Figure 3.3, which shows a list of expense data for a company. The formula in cell C11 uses the SUM() function to total the January expenses (range C6:C10). The idea behind this worksheet is to calculate a new expense budget number for 2005 as a percentage increase of the actual 2004 total. Cell C3 displays the INCREASE variable (in this case, the increase being used is 3%). The formula that calculates the 2005 BUDGET number (cell C13 for the month of January) multiplies the 2004 TOTAL by the INCREASE (that is, =C11*C3).
)
Figure
3.3 A budget expenses worksheet with two calculations for the January
numbers: the total (cell C11) and a percentage increase for next year (cell
C13).
The next step is to calculate the 2004 TOTAL expenses and the 2005 BUDGET figure for February. You could just type each new formula, but you learned in Chapter 1 that 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 (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.
)
Figure
3.4 When you copy the January 2004 TOTAL formula to February, Excel
automatically adjusts the range reference.
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!
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.
)
Figure
3.5 Copying the January 2004 BUDGET formula to February creates a
problem.
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 2." No matter where you copy or move this formula, the cell reference doesn't change. The cell address is said to be anchored.
To fix the budget expense worksheet, we need to anchor the INCREASE variable. To do this, we first change the January 2005 BUDGET formula in cell C13 to read =C11*$C$3. After making this change, copying the formula to the February 2005 BUDGET column gives the new formula =D11*$C$3, which produces the correct result.
CAUTION
Most range names refer to absolute cell references. This means that when you copy a formula that uses a range name, the copied formula will use the same range name as the original. This might produce errors in your worksheet.
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—for example, B$6) or its column (by placing the dollar sign in front of the column address only—for example, $B6).
TIP
You can quickly change the reference format of a cell address by using the F4 key. When editing a formula, place the cursor to the left of the cell address (or between the row and column values), and keep pressing F4. Excel cycles through the various formats. If you want to apply the new reference format to multiple cell addresses, highlight the addresses and then press F4 until you get the format you want.
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 activate it.
Use the mouse or keyboard to highlight the entire formula.
Copy the highlighted formula.
Press Esc to deactivate the formula bar.
Select the cell in which you want the copy of the formula to appear.
Paste the formula.
NOTE
Here are two other methods you can use to copy a formula without adjusting its relative cell references:
-
To copy a formula from the cell above, select the lower cell and press Ctrl+' (apostrophe).
-
Activate the formula bar and type an apostrophe (') at the beginning of the formula (that is, to the left of the equals sign) to convert it to text. Press Enter to confirm the edit, copy the cell, and then paste it in the desired location. Now delete the apostrophe from both the source and destination cells to convert the text back to a formula.