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%
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:
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%
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.
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:
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:
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.
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 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).