- Understanding Formula Basics
- Understanding Operator Precedence
- Controlling Worksheet Calculation
- Copying and Moving Formulas
- Displaying Worksheet Formulas
- Converting a Formula to a Value
- Working with Range Names in Formulas
- Working with Links in Formulas
- Formatting Numbers, Dates, and Times
- From Here
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 you copy and move regular ranges, but the results aren’t always straightforward.
For 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 (range C6:C10). The idea behind this worksheet is to calculate a new expense budget number for 2016 as a percentage increase of the actual 2015 total. Cell C3 displays the INCREASE variable (in this case, the increase being used is 3%). The formula that calculates the 2016 BUDGET number (cell C13 for the month of January) multiplies the 2015 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 2015 TOTAL expenses and the 2016 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 (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 I discuss in the next section.
)
Figure 3.4 When you copy the January 2015 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 only to copy the formula in cell C11 to cell D11 and, thanks to relative referencing, everything came 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 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 2016 BUDGET formula in cell C13 to cell D13. Figure 3.5 shows that the result is 0!
)
Figure 3.5 Copying the January 2016 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 2015 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, which 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 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, you need to anchor the INCREASE variable. To do this, you first change the January 2016 BUDGET formula in cell C13 to read =C11*$C$3. After making this change, copying the formula to the February 2016 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—for example, B$6) or its column (by placing the dollar sign in front of the column address only—for example, $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 activate it.
- Use the mouse or keyboard to select the entire formula.
- Copy the selected 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.