Working with Formulas That Reference Other Workbooks and Worksheets

Because a workbook can contain multiple worksheets, you might need to reference a cell in another worksheet or even another workbook file. As long as you follow the proper syntax, you can type a formula that contains a reference to any file.

If the cell is contained in another worksheet in the current workbook, you need to include the sheet name followed by an exclamation point ( ! ) and then the actual address of the cell. Blank spaces are not allowed. For example, Sheet4!A75 , points to cell A75 on Sheet 4 of the current workbook. Anytime you reference another worksheet in the workbook, you must include the exclamation point. If you've renamed a worksheet from its default sheet name, use the sheet name and an exclamation point.

Although blank spaces are not allowed in cell references, your sheet name might contain one or more blanks, for example, Jan Sales. If your sheet name contains blank spaces, enclose the sheet name in single quotes, such as 'Jan Sales'!A21 . The exclamation point character follows the quotes.

Although you can type a cell reference to another worksheet, it's easier to use the mouse. If you need to reference a cell on another sheet as you're building a formula, click the appropriate sheet tab and then point to the cell you want to use. Excel places the sheet name and cell reference, using perfect syntax, into the formula.

You can also reference a cell from another workbook. It's best to point to the cell you want to use, rather than worry about making sure that you've used the correct syntax. Make sure that you open the workbook(s) that contains the cells you want to reference before you begin.

Begin the formula by clicking the Edit Formula button so that the formula appears in the Formula palette. As you construct the formula, switch to the open worksheet by clicking the Window menu, as illustrated in Figure 44.11, and navigating to the worksheet and cell. After you click the cell you want, use the Window menu and click the file that contains the formula you're working on. When you're done building the formula, click the OK button on the Formula palette.

Figure 44.11. Switch to another open workbook.

The next few steps give you some hands-on practice in working with formulas and in entering data. Once again, you're working with the My Budget workbook. If the file isn't open right now, open it before you begin these practice steps.

1. Drag the mouse over the rectangular block of cells that contain data on the Summary tab of the My Budget worksheet. When the cells appear highlighted, click the Edit menu and choose Clear, Contents.

2. Click the Detail tab to make the second worksheet active. When the Detail worksheet appears, click in cell A4 and type =Summary!A4 . Click the Enter button. Cell A4 reads Mortgage/Rent , the same text that appears on cell A4 on the Summary sheet.

3. Click the Summary tab to make the Summary worksheet active. Double-click cell A4, highlight the existing text, and type Housing . Press Enter when you're through typing.

4. Switch to the Detail worksheet. Note that the formula in cell A4 references the same cell on the Summary worksheet. Therefore, changes to the cell on the Summary sheet automatically update here.

5. Click into cell A5, type Rent , and press the down-arrow key. Enter Mortgage , Parking , and Condo/Fees into the three cells located below the cell that contains Rent. When you're done, click the A column marker and choose Format, Column, AutoFit Selection to widen the column to the width of the widest entry.

6. Enter values that approximate your real expenses next to Rent, Mortgage, Parking, and Condo/Fees in the January column. (For practice, enter 100 in each cell.)

7. Obtain a total of the amounts you've typed so far with Excel's QuickSum feature. Select the range that contains the numbers you entered. The total appears on the Excel status bar, as shown in Figure 44.12.

   Figure 44.12. QuickSum adds the amounts you select.

   

   

   QuickSum is a great way to obtain a running total of a range of values. The total is not inserted in the worksheet. It's displayed on the Excel status bar.

   
   

8. Click into cell A9 and type Misc to create a new category. If you want, enter a corresponding amount for miscellaneous housing expenses in the January column.

9. Click into cell B10. When a border appears around the cell, click inside the Formula bar, type =SUM(B5:B9) , and press Enter. The total for the January housing expense is shown.

10. Click the Summary tab to activate the Summary worksheet. You're going to enter the total of housing expenses for January into cell B4. Double-click cell B4 and type = to begin the formula. Next, click the Detail sheet and click cell B10, which contains the sum for the category. Press Enter. The Summary worksheet reappears with the January total amount in the cell and the cell reference displayed in the Formula bar.

11. Click the Save button on the Standard toolbar to save the My Budget workbook. Remember, if you don't save the file, all your hard work will be lost.

12. (Optional) Switch to the Detail sheet and repeat steps 210 of this exercise to create details for your car expense. Create subcategories like Insurance, Fuel, and Maintenance. Reference the total amount on the Summary worksheet sheet. Substitute the cells for the new range in your calculation and cell referencing. Save the file when you're done.

Working with Simple Functions

Excel contains a range of functions designed to help you enter formulas easily. Excel functions run the gamut of simple calculations to complex, multitiered equations. You can use an Excel function to total a range of numbers or calculate a car payment. Excel contains more than 100 functions for your use.

This section examines a few of Excel's simpler functions. Table 44.2 shows some simple, commonly used functions.

Table 44.2. Common Excel Functions

Function	What It Does
SUM	Adds a range
AVERAGE	Determines the average of a range
NOW	Inserts the date based on the system clock; updates the date whenever the worksheet is opened or saved
PMT	Computes a monthly loan payment
HYPERLINK	Sets a hyperlink

Excel functions are handled like formulas. Each function begins with an = . Next enter the function name, which is usually a one-word description of what the function does. Following the function name is an opening parenthesis. Arguments follow. The function is concluded with a closing parenthesis.

The function

 =SUM(A1:A5) 

returns the sum of the cells from A1 through A5. (The colon character indicates through .) The function could also be written as =SUM(A1+A2+A3+A4+A5) . Although writing it with all the cells is technically correct, it makes more sense (and conserves space) to use range coordinates. If a function has more than one argument, commas separate the arguments.

A few functions don't use arguments. For example, =NOW() enters the serial number for the current date in the cell.