Section 7.3. Copying Formulas

7.3. Copying Formulas

Sometimes you need to perform similar calculations in different cells throughout a worksheet. For example, say you want to calculate sales tax on each item in a product catalog, the monthly sales in each store of a company, or the final grade for each student in a class. In this section, you'll learn how Excel makes it easy with relative cell references . Relative cell references are cell references that Excel updates automatically when you copy them from one cell into another. They're the standard kind of references that Excel uses (as opposed to absolute cell references, which are covered in the next section). In fact, all the references you've used so far have been relative references, but you haven't yet seen how they work with copy-and-paste operations.

Consider the worksheet shown in Figure 7-11, which contains a teacher's grade book. In this example, each student has three grades: two tests and one assignment. A student's final grade is based on the following percentages: 25 percent for each of the two tests, and 50 percent for the assignment.

Figure 7-11. This worksheet shows a list of students in a class, and calculates the final grade for each student using two test scores and an assignment score. So far, the only formula that's been added is for the first student (in cell E2).

The following formula calculates the final grade for the first student (Edith Abbott):

 =B2*25% + C2*25% + D2*50% 

The formula that calculates the final mark for the second student (Grace DeWitt) is almost identical. The only change is that all the cell references are offset by one row, so that B2 becomes B3, C2 becomes C3, and D2 becomes D3:

 =B3*25% + C3*25% + D3*50% 

You may get fed up entering all these formulas by hand. A far easier approach is to copy the formula from one cell to another. Here's how:

1. Move to the cell containing the formula you want to copy .

   In this example, you'd move to cell E2.

2. Copy the formula to the clipboard by pressing Ctrl+C .

   You can also copy the formula by choosing Home Clipboard Copy.

3. Paste in the new formulas by pressing Ctrl+V .

   You can also paste the formula by choosing Home Clipboard Paste.

   When you paste a formula, Excel magically copies an appropriate version of the formula into each of the cells from E3 to E10. These automatic formula adjustments occur for any formula, whether it uses functions or just simple operators. Excel then automatically calculates and displays the results, as shown in Figure 7-12.

---

Tip: There's an even quicker way to copy a formula to multiple cells by using the AutoFill feature introduced in Chapter 2. In the student grade example, you'd start by moving to cell E2, which contains the original formula. Then, you'd click the small square at the bottom-right corner of the cell outline, and drag the outline down until it covers all cells from E3 to E10. When you release the mouse button, Excel inserts the formula copies in the AutoFill region.

---

7.3.1. Absolute Cell References

Relative references are a true convenience since they let you create formula copies that don't need the slightest bit of editing. But you've probably already realized that relative references don't always work. For example, what if you have a value in a specific cell that you want to use in multiple calculations? You may have a currency conversion ratio that you want to use in a list of expenses. Each item in the list needs to use the same cell to perform the conversion correctly. But if you make copies of the formula using relative cell references, then you'll find that Excel adjusts this reference automatically and the formula ends up referring to the wrong cell (and therefore the wrong conversion value).

Figure 7-12. When you paste the formula into one or more new cells, each Final Grade formula operates on the data in its own row. This means that you don't have to tweak the formula for each student. The formula bar shows the formula contained in cell E3.

Figure 7-13 illustrates the problem with the worksheet of student grades. In this example, the test and assignment scores aren't all graded out of 100 possible points; each item has a different total score available (listed in row 12). In order to calculate the percentage a student earned on a test, you need to divide the test score by the total score available. This formula, for example, calculates the percentage for Edith Abbott's performance on Test B:

 =B2/B12*100% 

To calculate Edith's final grade for the class, you'd use the following formula:

 =B2/B12*25% + C2/C12*25% + D2/D12*50% 

Figure 7-13. In this version of the student grade book, both the tests and the assignment are graded on different scales (as listed in row 12). When you copy the Final Grade formula from the first row (cell E2) to the rows below it, Excel offsets the formula to use B13, C13, and D13none of which provide any information. Thus a problem occursshown here as a divide-by-zero error. To fix this, you need to use absolute cell references.

Like many formulas, this one contains a mix of cells that should be relative (the individual scores in cells B2, C2, and D2) and those that should be absolute (the possible totals in cell B12, C12, and D12). As you copy this formula to subsequent rows, Excel incorrectly changes all the cell references, causing a calculation error.

Fortunately, Excel provides a perfect solution. It lets you use absolute cell references cell references that always refer to the same cell. When you create a copy of a formula that contains an absolute cell reference, Excel doesn't change the reference (as it does when you use relative cell references; see the previous section). To indicate that a cell reference is absolute, use the dollar sign ($) character. For example, to change B12 into an absolute reference, you would add the $ character twice, once in front of the column and once in front of the row, which changes it to $B$12.

Here's the corrected class grade formula (for Edith) using absolute cell references:

 =B2/$B*25% + C2/$C*25% + D2/$D*50% 

This formula still produces the same result for the first student. However, you can now copy it correctly for use with the other students. To copy this formula into all the cells in column E, use the same procedure described in the previous section on relative cell references.

UP TO SPEED
Creating an Exact Formula Copy

There's another way to copy a formula that prevents Excel from automatically changing the formula's cell references. The trick's to copy the formula itself rather than copy the whole cell (which is what you do when performing a basic copy-and-paste operation on a formula).

The process takes a few more steps, and it lets you paste only one copy at a time, but it can still come in handy if you don't want Excel to use relative references. Here's how it works:

1. First, move to the cell that contains the formula you want to copy.

2. Place this cell in edit mode by double-clicking it or pressing F2.

3. Select all the text in the cell. You can use the mouse, or you can use the arrow keys (just hold down Shift as you scroll from the beginning to the end of the cell).

4. Once you've selected the complete formula, press Ctrl+C to copy it.

5. Press Enter to leave edit mode once you're finished.

6. Move to the new cell, and press Ctrl+V to paste it.

Keep in mind that when you use this approach, you create an exact copy of the formula. That means this technique doesn't help in situations where some cell references need to be absolute, and others need to be relative.

7.3.2. Partially Fixed References

You might wonder why you need to use the $ character twice in an absolute reference (before the column letter and the row number). The reason is that Excel lets you create partially fixed references. To understand partially fixed references, it helps to remember that every cell reference consists of a column letter and a row number. With a partial fixed reference, Excel updates one component (say, the column part) but not the other (the row) when you copy the formula. If this sounds complex (or a little bizarre), consider a few examples:

• You have a loan rate in cell A1, and you want all loans on an entire worksheet to use that rate in calculations. If you refer to the cell as $A$1, then its column and row always stay the same when you copy the formula to another cell.

• You have several rows of loan information. The first column of a row always contains the loan rate for all loans on that row. In your formula cell, if you refer to cell $A1, then when you copy the formula across columns and rows, the row changes (2, 3, 4, etc.) but the column doesn't (A2, A3, A4, etc.).

• You have a table of loan rates organized by the length of the loan (10-year, 15-year, 20-year, etc.) along the top of a worksheet. Loans in each column are calculated using the rate specified at the top of that column. If you refer to the rate cell as A$1 in your first column's formula, then the row stays constant (1), but the column changes (B1, C1, D1, etc.) as you copy the formula across columns and down rows.

---

Tip: You can quickly change formula references into absolute or partially fixed references. Just put the cell into edit mode (by double-clicking it or pressing F2). Then, move through the formula until you've highlighted the appropriate cell reference. Now, press F4 to change the cell reference. Each time you press F4, the reference changes. If the reference is A1, for instance, it becomes $A$1, then A$1, then $A1, and then A1 again.

---

FREQUENTLY ASKED QUESTION
How Changing the Location of Cells Affects Formulas

OK, I know how Excel adjusts a formula when I copy it to another location. But what happens if I move cells around after I've created a formula?

No worries. It turns out that Excel is surprisingly intelligent . Consider the following simple formula:

 =B1+A2 

If you cut and paste the contents of A2 to A3, Excel automatically updates your formula to point to the new cell, without complaining once. It also performs the same automatic cleanup if you drag the contents of a cell to another location (although if you simply make a duplicate copy of the cell, Excel won't change your formula). Excel is also on the ball when you insert and delete rows and columns.

If at any time Excel can't find your cells, the formula changes to show the error code #REF! You can then take a closer look at the formula to find out what really went wrong. For example, if you delete column B from your spreadsheet (by selecting the column and using the Home Cells Delete command), the formula changes to this:

 =#REF!+A2 

Even though there's still a B1 cell in your worksheet (it's the cell that was formerly named C1), Excel modifies the formula to make it clear that you've lost your original data.