Using More Than One Workbook

As you complete daily tasks with Excel, you'll often find it necessary to open additional workbooks to review sales figures, prepare an invoice, copy data, or complete other work. Excel allows you to load as many workbooks into memory as your system can handle. Each workbook appears in its own window and is given a separate icon on the Windows taskbar. (If you don't see separate icons for multiple open workbooks on your taskbar, choose Options from the Tools menu, click the View tab, and select the Windows In Taskbar option.) You can switch between workbooks by clicking the workbook icons on the taskbar or by choosing the workbook's filename from the Window menu. (Excel lists files on the Window menu in the order that you open them.) The following section shows you how to:

• Switch between workbook windows
• Link information between open workbooks
• Use multiuser workbooks in a network setting

Switching Between Workbooks

You can open additional workbooks in Excel by choosing Open from the File menu, locating the workbook you want to open in the Open dialog box, and then double-clicking it. When multiple workbooks are loaded, you can view them one at a time in maximized windows (the default), or side by side in the workplace. To view workbook windows side by side, choose Arrange from the Window menu.

The Arrange dialog box includes four useful window orientation options that display different parts of the workbook: Tiled, Horizontal, Vertical, and Cascade. Figure 18-5 shows how two open workbooks are arranged if you click the Vertical option button and click OK. To switch between these open workbooks, click the workbook you want to work with. (The active workbook's title bar will be displayed using your system's Active Title Bar color settings.)

Figure 18-5. The Arrange command lets you view more than one workbook at once.

You can also switch between open workbook windows by using a key combination and by choosing filenames from the Window menu.

To switch between windows using the keyboard, follow these steps:

1. Load the workbooks you want to work with by using the Open command.
2. Cycle among workbooks by pressing Ctrl+F6. (To cycle through the workbooks in the opposite direction, press Ctrl+Shift+F6.)

To switch between workbooks using the Window menu, follow these steps:

1. Load the workbooks you want to work with by using the Open command.
2. From the Window menu, choose the workbook you want to display in the active window. The following illustration shows what the Window menu looks like when two workbooks are loaded. The active workbook (SalesSum) is identified by a check mark next to its filename.

Linking Information Between Workbooks

Earlier in this chapter, you learned how to build formulas that reference other worksheets in the workbook. You can also build formulas that reference worksheets in other workbooks. Before you create the linked formula, however, you must open each of the workbooks you plan to use. The following example adds the total revenue from a workbook named SalesLnk (containing international sales data) to the domestic sales total calculated in the SalesSum workbook.

If you want to create formulas that reference other workbooks, follow these steps:

1. Open the workbooks you plan to reference in your formulas.

The following screen shows a sample workbook named SalesLnk, which computes the total revenue received by a company from areas outside the United States:



ON THE WEB

---

The SalesLnk.xls example is on the Running Office 2000 Reader's Corner page.

2. Add a formula to your worksheet that references cells in other workbooks.

For example, to copy a grand total from the International worksheet in the SalesLnk workbook, start in the cell where you want to copy the data, type an equal sign (=), click the SalesLnk workbook on the Windows taskbar, click the International worksheet tab, click the cell with the total you want to incorporate (B12 in this example), and press Enter. The linking formula appears in the formula bar, composed of the workbook filename enclosed in square brackets, an exclamation mark following the worksheet name, and dollar signs ($) preceding the linked cell's column letter and row number. Your screen will look similar to this one:

TIP

---

Consolidate Worksheets with Identical Formats
If you want to link together several worksheets that share a common organizational format, you can also use the Consolidate command on the Data menu to assemble workbook information. When you consolidate worksheets, you can use one or more statistical functions on the cell ranges you select to obtain useful information about your data. The statistical functions available include Sum, Count, Average, Max, Min, and StdDev. You'll learn more about using statistical functions in Chapter 20, "Using Formulas and Functions to Crunch Numbers." For more information about applying the Consolidate command, search for "consolidating data" in the Excel online Help.

Saving a Workspace File

---

If you often use the same collection of workbooks in Excel, consider creating a workspace file to save information about which workbooks are open and how they appear on the screen. The next time you want to use the workbooks, simply open the workspace file, and the workbook will appear as it did when you last saved the workspace, including toolbars, cell selections, and other tools in the user interface. The workspace file doesn't include changes you make to your worksheets— you need to save these separately by using the Save or Save As command— but it does keep track of your open windows and worksheets, so that you can pick up right where you left off. To save the arrangement of open workbooks in a workspace file, follow these steps:

1. Open and organize your workbooks as you would like them saved in the workspace file. (Creating a workspace file is a little like taking a picture, so get everything positioned just where you want it.)
2. From the File menu, choose Save Workspace. The Save Workspace dialog box appears. (It works basically like a Save As dialog box.)
3. Type a name for the workspace file in the File Name text box, and specify a folder location if necessary.
4. Click the Save button to save the workspace file. (You might also be prompted to save one or more of the open workbooks.)

When you're ready to open the workspace file later, choose Open from the File menu as you would for any file. Your workbooks and worksheets will appear just as you left them, including any cell selections you made.