Section 7.3. Copying Formulas

7.3. Copying Formulas

Sometimes you need to perform similar calculations in different cells throughout a worksheet. For example, you might 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-9, 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.

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% 

Figure 7-9. 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).

Entering all these formulas by hand can be painstaking. 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 selecting Edit Copy.

    You can also copy the formula by pressing the key combination Ctrl+C.

  3. Select the range of cells you want to copy the formula into.

    Select cells E3 to E10.

  4. Paste in the new formulas by selecting Edit Paste (Ctrl+V).

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

    Tip: There's an even quicker way to copy a formula to multiple cells by using the AutoFill feature introduced in Chapter 2 (Section 2.2.3). In the student grade example, you would 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 will insert the formula copies in the AutoFill region.

    Figure 7-10. 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.

7.3.1. Absolute Cell References

Relative references are a true convenience in that 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 might 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, 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-11 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:


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-11. In this version of the student grade book, both the tests and the assignment are graded on different scales (as listed in row 12). Thus, the formula for calculating the final class grade uses the values in cells B12, C12, and D12. 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 exist. Thus a problem occursshown here as a divide-by-zero error. To fix this, you need to use absolute cell references.

Cell Formulas "Under the Hood"

To understand how Excel adjusts copied formulas, you need to know a little more about how Excel stores formulas. Internally, all Excel formulas are actually written using a system called R1C1 reference (Sidebar 1.2). With R1C1 referencing, when you create a formula, it doesn't contain cell references; instead, it contains cell offsets . Offsets tell Excel how to find a cell based on its position relative to the current cell, as you'll see below.

For example, in the student grade calculation, the formula:

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

looks like this in R1C1 representation:

 =RC[-3]*25% + RC[-2]*25% + RC[-1]*50% 

Notice that Excel has translated the cell reference B2 into the notation RC[-3], which means "get the number in the same row, but three columns to the left." If the formula was using a number from the row above, you'd see an R1C1 reference like R[-1]C[-3], which would tell Excel to go one row up and three columns to the left. Negative numbers in relative cell referencing indicate movement to the left (for columns ) or up (for rows); positive numbers indicate movement to the right (columns) or down (rows).

When you copy a formula from one cell to another, Excel actually copies the R1C1 formula, rather than the formula you've entered (unless you've instructed Excel to use absolute cell references, as explained in the next section). To view your formulas in R1C1 style, you can temporarily change the type of cell addressing used in your spreadsheet. Select Tools Options, choose the General tab, and then turn on the "R1C1 reference style" checkbox. You'll probably want to turn this mode off when you're about to create a formula, as it's almost always easier to write formulas using Excel's standard cell addressing.

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 won'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, and change 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 is 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 (or select Edit Copy).

  5. Move to the new cell, and press Ctrl+V to paste it (or select Edit Paste from the menu).

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

7.3.2. Partial 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 because 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 will update 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, 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, when you copy the formula across columns and rows, the row will change (2, 3, 4, etc.) but the column will not (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, the row will stay constant (1), but the column will change (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.

7.3.3. Referring to Other Worksheets and Workbooks

Most formulas refer to the cells in a single worksheet. Excel does, however, let you use formulas that refer to cells in other worksheets or even other files.

To reference a cell in another worksheet, you simply need to preface the cell with the worksheet name , followed by an exclamation mark. For example, say you've created a formula to double the value of the number in cell A1 on a worksheet named Sheet1. You'd use this formula:


If you want to use the same formula in another worksheet (in the same workbook), you'd insert this formula in the new worksheet:


Note: If you use the point-and-click method to build formulas, you'll find that you don't need to worry about the syntax for referring to cells on other worksheets. If you switch to another worksheet while you're building a formula, Excel automatically inserts the correct reference to the worksheet name.

It's fairly common for one worksheet to reference another within the same workbook file. (For a refresher on the difference between worksheets and workbooks see Figure 1-1.) On Section 11.4, you'll see an example where one worksheet includes a product catalog, and a second worksheet builds an invoice. In that example, the second worksheet uses formulas that refer to data on the first sheet.

It's less common for a worksheet to refer to data in another file . The potential problem with this type of link is that there's no way to guarantee that referenced files will always be available. If you rename the file or move it to a different directory, the link will break. Fortunately, Excel doesn't leave you completely stranded in this situationinstead, it continues with the most recent version of the data it was able to retrieve. You can also tweak the link to point to the new location of the file.

To create a link to a cell in another workbook, you need to put the file name at the beginning of the reference and then enclose it in square brackets. You follow the file name with the sheet name, an exclamation mark, and the cell address. Here's an example:


If the file name or the sheet name contains any spaces, you need to enclose the whole initial portion inside apostrophes , like so:

 ='[Simple Expenses.xls]Sheet1'!B3 

When you enter this formula, Excel checks to see if this file is already open in another Excel window. If not, Excel attempts to find the file in the current directory. If it can't find the file, it shows a standard Open dialog box. You can use this dialog box to browse to the file you want to use. Once you select the file, Excel updates the formula accordingly .

Excel performs a little sleight of hand with formulas that reference other files. If the referenced workbook is currently open, the formula displays the file name only. However, once you close the referenced file, the formula changes so that it includes the full directory path . Excel makes this change automatically, whether you type a file name in by hand or build the formula by pointing and clicking on cells in another open workbook.

Here's an example of what the full formula looks like when you close the file that contains the Sheet1 worksheet (split over two lines to fit the page):

 ='[C:\Documents and Settings\Matthew\My Documents\Simple Expenses.xls]Sheet1'!B3 

When you save a workbook that refers to another workbook, Excel stores all the worksheet information you need. For example, consider an example with a workbook named Summary.xls that refers to cell B3 in a workbook named SimpleExpenses.xls (as in the previous example). The cell B3 in SimpleExpenses.xlscontains the staggering total $94,200.12. When you save the workbooks, Excel stores the number $92,200.12 in both SimpleExpenses.xls and Summary.xls. When you open Summary.xls later on, you have the choice of using the current information, or rechecking SimpleExpenses.xls and refreshing Summary.xls if cell B3 has been changed. Figure 7-12 shows the full story.

Figure 7-12. Top : When a formula references another workbook, Excel asks whether you want to get the latest data by opening the referenced workbook or use the current data stored in the original workbook. If you go for the update, the refresh is seamlessunless Excel can't find the workbook you referenced.
Bottom : If Excel can't find the referenced workbook, you'll see an error message that gives you the choice of ignoring it and working with what you have (click Continue), or relinking the workbook to the right file (click Edit Links and see Figure 7-13).

If you know that a link has gone bad, you can't change it without editing your formulas. This behavior is handy if you have a pile of linked formulas that point to cells in the same workbook. Rather than changing each one by hand, you can point the link to another file or directory using the Edit Links dialog box shown in Figure 7-13.

Figure 7-13. Excel can't find the linked file listed in the dialog box. You can select the link, and click Change Source to browse for the linked workbook (click OK when you find it); or click Break Link to make your workbook independent, and just use whatever data you extracted last time permanently.

Figure 7-13 shows the Edit Links dialog box and explains how you can use it to fix a broken link. In addition, some of the buttons in this dialog box work with links that aren't broken. Click Update Values to refresh the current workbook, Open Source to open the linked workbook in another Excel window, or Check Status to see if the linked workbook is available. Use the Startup Prompt to choose the behavior Excel uses when it opens this workbookrefresh automatically, never refresh, or ask you (the standard setting).

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:


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 will change 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 Edit Delete command), the formula changes to this:


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.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: