Contents
Moving Between Worksheets
Identifying Worksheets
Renaming a Worksheet
TIP
Changing the Color of a Sheet Tab
Step-by-Step 7.1
Managing Worksheets Within a Workbook
Repositioning a Worksheet
EXTRA FOR EXPERTS
Hiding and Unhiding a Worksheet
Inserting and Deleting Worksheets
TIP
Step-by-Step 7.2
Consolidating Workbook Data
Creating Cell References to Other Worksheets
TIP
Creating 3-D References
TECHNOLOGY CAREERS
Step-by-Step 7.3
Printing a Workbook
Printing Nonadjacent Selections of a Worksheet
Printing More Than One Worksheet
Step-by-Step 7.4
Working with Multiple Workbooks
Arranging Workbooks
Moving and Copying Worksheets Between Workbooks
TIP
Step-by-Step 7.5
Lesson 7: Working with Multiple Worksheets and Workbooks: Summary
Lesson 7: Working with Multiple Worksheets and Workbooks: Review Questions
TRUE / FALSE
MATCHING
FILL IN THE BLANK
Lesson 7: Working with Multiple Worksheets and Workbooks: Projects
PROJECT 7–1
PROJECT 7–2
PROJECT 7–3
PROJECT 7–4
Lesson 7: Working with Multiple Worksheets and Workbooks: Critical Thinking
ACTIVITY 7–1
Moving Between Worksheets
A workbook is a collection of worksheets. Each worksheet within the workbook is identified with a sheet tab that appears at the bottom of the workbook window. The name of the worksheet appears on the sheet tab. Unless you rename the worksheets, they are identified with the default names Sheet1, Sheet2, and so on, as shown in Figure 7–1.
FIGURE 7–1 Default sheet tabs in a workbook
To view a specific worksheet, simply click its sheet tab. The worksheet that appears in the workbook window is called the active worksheet (or active sheet). The active sheet has a white sheet tab. If you don't see the sheet tab for the worksheet you want to display, use the tab scrolling buttons to display the sheet tab.
Identifying Worksheets
To better identify worksheets and their contents, you can give them more descriptive names. You can also change the color of each sheet tab.
Renaming a Worksheet
Although you can use the default worksheet names (Sheet1, Sheet2, and so on), a good practice is to use descriptive names to help identify the contents of each worksheet. For example, the worksheet name Quarter 1 Budget is a better reminder of a worksheet's contents than Sheet1. To rename a worksheet, double-click its sheet tab. The worksheet name in the sheet tab is selected. Type a new name for the worksheet, and then press the Enter key.
TIP
To rename a worksheet, you can also right-click its sheet tab, and then click Rename on the shortcut menu. Type a new name, and then press the Enter key.
Bookmark Title:
Top of Form
Bottom of Form
Changing the Color of a Sheet Tab
Another way to categorize worksheets is by changing the color of the sheet tabs. For example, a sales manager might use different colors to identify each sales region. To change the sheet tab color, right-click the sheet tab you want to change, point to Tab Color on the shortcut menu, and then click the color you want for that sheet tab.
Step-by-Step 7.1
1. Open the Crystal.xlsx workbook from the drive and folder where your Data Files are stored. Save the workbook as Crystal Sales followed by your initials.
2. Click the Sheet3 sheet tab. The Sheet3 worksheet appears as the active sheet. This worksheet summarizes the sales data stored in the other worksheets.
3. Double-click the Sheet3 sheet tab. The worksheet name is highlighted.
4. Type Corporate, and then press the Enter key. The name Corporate appears on the third sheet tab.
5. Double-click the Sheet1 sheet tab to make Sheet1 the active sheet and to highlight the worksheet name, type Western, and then press the Enter key. The Sheet1 worksheet is renamed as Western.
6. Rename the Sheet2 worksheet as Eastern. Rename the Sheet4 worksheet as Northern.
7. Right-click the Corporate sheet tab, and then point to Tab Color. A palette of colors appears, as shown in Figure 7–2.
FIGURE 7–2 Shortcut menu for the selected Corporate sheet tab
8. In the Theme Colors section, click Black, Text 1 (the second color in the first row). A black line appears at the bottom of the Corporate sheet tab.
9. Click the Northern sheet tab. The Northern worksheet becomes the active sheet, and you can see the black sheet tab for the Corporate worksheet.
10. Right-click the Northern sheet tab, point to Tab Color to open the color palette, and then, in the Theme Colors section, click Orange, Accent 6 (the last color in the first row).
Bookmark Title:
Top of Form
Bottom of Form
11. Right-click the Western sheet tab, point to Tab Color, and then clickAqua, Accent 5 (the ninth color in the first row).
12. Right-click the Eastern sheet tab, point to Tab Color, and then, in the Theme Colors section, clickPurple, Accent 4 (the eighth color in the first row).
13. Click the Corporate sheet tab. The Corporate worksheet is active, and the colored sheet tabs are visible for the regional worksheets, as shown in Figure 7–3.
FIGURE 7–3 Renamed and colored sheet tabs
14. Save the workbook, and leave it open for the next Step-by-Step.
Managing Worksheets Within a Workbook
Often, data and analysis are best organized on multiple worksheets. For example, you can enter financial data for each quarter of the year in four different worksheets, and then summarize the annual data in a fifth worksheet. Another common workbook organization is to place sales data for each sales territory or region in its own worksheet, and then summarize the total sales in another worksheet.
Repositioning a Worksheet
To make it simpler to find information, you can position worksheets in a logical order, such as placing a summary worksheet first, followed by the worksheets that contain the data being summarized. You can reposition a worksheet by dragging its sheet tab to a new location. A placement arrow indicates the new location, as shown in Figure 7–4. When you release the mouse button, the worksheet moves to that position.
EXTRA FOR EXPERTS
You can create a copy of a worksheet by pressing the Ctrl key as you drag and drop a sheet tab. When you release the mouse button, a copy of the selected worksheet is added in the location indicated by the arrow and the original worksheet remains in its same location. The sheet tab has the same name as the original worksheet, followed by a number in parentheses.
FIGURE 7–4 Sheet tab being repositioned
Hiding and Unhiding a Worksheet
Some workbooks include many worksheets. Some workbooks might contain data you do not need to see, but still want to save, such as a list of employee names or data from past months. You can keep the sheet tabs organized by hiding the worksheets you do not need to view. Right-click the worksheet you want to hide, and then click Hide on the shortcut menu. To unhide a worksheet, right-click any sheet tab, and then click Unhide on the shortcut menu. The Unhide dialog box appears, as shown in Figure 7–5. Click the worksheet you want to unhide, and then click OK.
FIGURE 7–5 Unhide dialog box
Inserting and Deleting Worksheets
By default, each workbook contains three worksheets. You can always add or delete worksheets as needed to accommodate your data. To insert a blank worksheet, click the Insert Worksheet tab next to the existing sheet tabs. A new worksheet is added to the right of the last worksheet. After adding it, you can drag the new worksheet to the position you want. Another option is to click the sheet tab of the worksheet that will Bookmark Title:
Top of Form
Bottom of Form
follow the new sheet. On the Home tab of the Ribbon, in the Cells group, click the Insert button arrow, and then click Insert Sheet. A new worksheet is inserted to the right of the sheet you selected.
TIP
You can also right-click a sheet tab, click Delete on the shortcut menu, and then click Delete in the message box to delete a worksheet.
Deleting a worksheet permanently removes it and all its contents from the workbook. You cannot undo the action. To delete a worksheet, click the sheet tab for the worksheet you want to remove. On the Home tab of the Ribbon, in the Cells group, click the Delete button arrow, and then click Delete Sheet. If the worksheet is blank, the worksheet is permanently removed from the workbook without confirmation. If the worksheet contains data, a dialog box appears to confirm that you want to delete the worksheet, as shown in Figure 7–6. Click Delete to continue the action, or click Cancel to leave the worksheet in the workbook. After clicking Delete, you cannot undo the action.
FIGURE 7–6 Message that appears when deleting a worksheet
Step-by-Step 7.2
1. Click and drag the Corporate sheet tab to the left until the arrow points to the left of the Western sheet tab, as shown in Figure 7–4.
2. Release the mouse button. The sheet tab for the Corporate worksheet is first.
3. Right-click the Northern sheet tab, and then click Hide on the shortcut menu. The Northern worksheet and its sheet tab are hidden.
4. Right-click any sheet tab, and then click Unhide on the shortcut menu. The Unhide dialog box appears, as shown in Figure 7–5, listing the name of the hidden worksheet in the workbook.
5. Click Northern, if it is not already selected, and then click OK. The Northern worksheet and its sheet tab reappear.
6. Make sure the Northern worksheet is the active sheet.
7. On the Home tab of the Ribbon, in the Cells group, click the Delete button arrow, and then click Delete Sheet. A dialog box appears warning that data may exist in the worksheet selected for deletion, as shown in Figure 7–6.
8. Click Delete. The Northern worksheet is deleted, and its sheet tab no longer appears at the bottom of the worksheet.
9. Save the workbook, and leave it open for the next Step-by-Step.
Consolidating Workbook Data
In some cases, you might need several worksheets to solve one numerical problem. For example, a business that has several divisions might keep the financial results of each division in a separate worksheet. Then another worksheet might combine those results to show summary results for all divisions.
Creating Cell References to Other Worksheets
Rather than retyping data and formulas on multiple worksheets, you can create a reference to existing data and formulas in other places. For example, you could use this type of reference to display regional sales totals on a summary sheet. The location of the data being referenced is the source . The location where the data will be used is the destination .
To display data or formula results from one worksheet in another worksheet in the same workbook, you use a formula in the format shown in Figure 7–7. First, click the destination cell where you want to display the data or formula results from another worksheet. Type an equal sign to begin the formula. Click the sheet tab for the worksheet that contains the source cell or source range you want to reference, and then click the source cell or select the source range to include it in the formula. Finally, press the Enter key to complete the formula. For example, the reference Sheet2!B3 refers to the value contained in cell B3 on Sheet2 and the reference Sheet2!A1:C3 refers to the values contained in the range A1:C3 on Sheet2.
TIP
The contents of a source cell appear in the destination cell. Any change you make to the source cell also changes the value in the destination cell.
FIGURE 7–7 Formula with a reference to another worksheet
Creating 3-D References
A 3-D reference is a reference to the same cell or range in multiple worksheets that you use in a formula. You can use 3-D references to incorporate data from other worksheets into the active worksheet. You can use a 3-D reference with 18 different functions, including SUM, AVERAGE, COUNT, MIN, MAX, and PRODUCT. For example, you might want to enter the SUM function in a summary worksheet to add several numbers contained in other worksheets, such as with quarterly or regional sales data. In general, to use 3-D references, worksheets should have the same organization and structure.
Bookmark Title:
Top of Form
Bottom of Form
A 3-D reference includes the worksheet range, an exclamation point, and a cell or range, as shown in the formula in Figure 7–8. A worksheet range is a group of adjacent worksheets. In a worksheet range, as in a cell range, a colon separates the names of the first worksheet and the last worksheet in the group. An exclamation mark separates the worksheet range from its cell or range reference. For example, the reference Sheet2:Sheet4!B3 refers to the values contained in cell B3 on Sheet2, Sheet3, and Sheet4 and the reference Sheet2:Sheet4!A1:C3 refers to the values contained in the range A1:C3 on Sheet2, Sheet3, and Sheet4.
FIGURE 7–8 Formula with a 3-D reference
Because a worksheet range is a group of adjacent worksheets, moving a worksheet into the range or removing a worksheet from the range affects the formula results. In the 3-D reference Sheet2:Sheet4!B3, if you move Sheet1 so it is positioned between Sheet3 and Sheet4, the value in cell B3 of Sheet1 is also included in the 3-D reference.
Table 7–1 gives other examples of formulas with worksheet and 3-D references.
TABLE 7–1 Formulas that reference other worksheetsFORMULA / DESCRIPTION
=Sheet4!D9 / Displays the value from cell D9 in the Sheet4 worksheet
=Sheet1!D10+Sheet2!D11 / Adds the value from cell D10 in the Sheet1 worksheet and the value from cell D11 in the Sheet2 worksheet
=SUM(Sheet2!D10:D11) / Adds the values from cells D10 and D11 in the Sheet2 worksheet
=SUM(Sheet2:Sheet4!D12) / Adds the value from cell D12 in the Sheet2, Sheet3, and Sheet4 worksheets
TABLE 7–1 Formulas that reference other worksheets
TECHNOLOGY CAREERS
Excel workbooks are extremely useful in areas of business that have a quantitative orientation, such as accounting and finance. In accounting, formulas are used to build financial statements. Financial officers in corporations use worksheets to project sales and control costs.
Step-by-Step 7.3
1. Click the Corporate sheet tab. You will enter formulas in this worksheet that reference cells in the Western and Eastern worksheets.
2. Click cell B4, and then type = to begin the formula.
3. Click the Western sheet tab. The worksheet name and an exclamation mark are added to the formula in the Formula Bar, which is =Western!. The Western worksheet appears in the workbook window so you can select a cell or range.
4. Click cell B6. The cell address is added to the reference in the Formula Bar, which is =Western!B6. The Western worksheet remains visible so you can select additional cells.
5. Press the Enter key. The formula is entered, and the Corporate worksheet is active again. The formula result $525,367 appears in cell B4.
6. In the Corporate worksheet, click cell B5, if it is not the active cell. Type = to begin the formula. Click the Eastern sheet tab, and then click cell B6. The formula =Eastern!B6 appears in the Formula Bar. Press the Enter key. The formula is entered in cell B5 of the Corporate worksheet, displaying the result $521,001.
7. In the Corporate worksheet, click cell B12. Type = to begin the formula. Click the Western sheet tab, and then click cell B3. The formula =Western!B3 appears in the Formula Bar.
8. Type + to enter the operator, click the Eastern sheet tab, and then click cell B3. The formula =Western!B3+Eastern!B3 appears in the Formula Bar.
9. Press the Enter key. The formula is entered in cell B12 of the Corporate worksheet, which shows the formula result $306,744.
10. In the Corporate worksheet, click cell B13, if it is not already selected, and then type =SUM( to begin the formula.
11. Click the Western sheet tab, press and hold the Shift key, and then click the Eastern sheet tab. Release the Shift key. The formula with the worksheet range reference =SUM(‘Western:Eastern’! appears in the Formula Bar.
12. Click cell B4, and then press the Enter key. The cell reference is added to the 3-D reference in the formula, which is =SUM(‘Western:Eastern’!B4). The formula result $551,399, which adds the values in cell B4 in the Eastern and Western worksheets, appears in cell B13.
Bookmark Title:
Top of Form
Bottom of Form
13. Copy the formula in cell B13, and then paste the formula into cell B14. The value in cell B15 is the same as the value in cell B6, as shown in Figure 7–9.
FIGURE 7–9 Data summarized on one worksheet
14. Save the workbook, and leave it open for the next Step-by-Step.
Printing a Workbook
So far, you have printed an active worksheet or selected areas of an active worksheet. You can also print an entire workbook, selected worksheets, or selected areas of a workbook. You designate the portion of the workbook to print on the Print tab in Backstage view, as shown in Figure 7–10. These options are described in Table 7–2.