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

Chapter 3. Building Basic Formulas > Copying and Moving Formulas

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.


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