Working with Multiple Worksheets

AN EXCEL WORKBOOK STARTS out with three worksheets, although quite often, you may only use one. Each of these worksheets has a generic name, such as Sheet1, Sheet2, and so on, although you can rename them as needed. You can remove sheets from a workbook that you aren’t using, and make the file slightly smaller and easier to use. You can also add sheets to a workbook, if you need to gather related information together (such as each month’s sales figures) and yet keep them separate and unique. If you are building such a workbook, you can create the first month’s sales worksheet, complete with formulas and formatting, and copy it over and over within the workbook, saving tons of time. In this section, you learn how to perform all of these tasks, and many more related to working within a workbook that contains multiple worksheets.

Moving Between Worksheets

Even if you’re not a terribly curious person by nature, if a workbook contains more than one worksheet, at some point or another, your curiosity will simply overwhelm you. What lies beyond this current worksheet? What treasures (or additional work) lay behind those other worksheet tabs? Well, thar may be dragons, as they used to say, or there may be treasures on those other worksheets. But the only way you will find out is to change from the current worksheet to another one.

You probably noticed by now that Excel lines its worksheets up along the bottom of the workbook window, each represented by a single tab, like a tab in a large notebook. The tab for the currently displayed worksheet appears in white, as shown in Figure 6-1.

Here are some tips on how to move back and forth between worksheets:

• To change from one worksheet to another, simply click its tab.

• In workbooks with many worksheets, you may not see all the tabs at one time. Use the tab-scrolling buttons to adjust the tab listing until the tab of the worksheet you want to switch to is visible.

  • To display the first few worksheet tabs, click the first tab-scrolling button.

    To display the last few worksheet tabs, click the last tab-scrolling button.

  • To scroll the tab listing to the left or right one worksheet at a time, click one of the tab-scrolling buttons in the middle.

• Drag the tab split box to the right to increase the number of worksheet tabs displayed at any given time. (This will shorten the horizontal scroll bar a bit, but it will still be useable.) Drag the tab split box to the left to decrease the number of worksheet tabs displayed.

Inserting Additional Worksheets

Normally, a workbook contains three worksheets, although you can set up Excel so that new workbooks contain a different number of starting sheets. In any case, it’s a simple matter to add worksheets to a workbook. You might want to use worksheets to separate similar, but different data in a workbook. For example, you might use separate worksheets, one for each month, to enter budget, sales, or inventory data.

There are several ways to insert a worksheet into a workbook. Use whichever of these methods suits you best at the time:

• Click the arrow on the Insert button on the Home tab, and select Insert Sheet from the menu that appears. A sheet is inserted in front of the current sheet. The newly inserted sheet becomes the currently displayed sheet. See Figure 6-2.

  Figure 6-2. A new sheet is inserted in front of the current sheet.

  [View full size image]

  

• Right-click a sheet tab and select Insert from the menu that appears. The Insert dialog box appears, as shown in Figure 6-3. On the General tab, select Worksheet, and then click OK. A sheet is inserted in front of the sheet you right-clicked.

  Figure 6-3. Select what you want to insert into the workbook.

  [View full size image]

  

• Click the Insert Worksheet button, located at the right end of the sheet tabs, as shown in Figure 6-4.

  Figure 6-4. Insert a worksheet quickly using the Insert Worksheet button.

  [View full size image]

  

New sheets are given names unique to the workbook such as Sheet4, Sheet5, and so on. The names of sheets that were added to the workbook at some other time are not reused even if they were renamed or removed. You can rename these newly inserted sheets if you want, in order to make their purpose more obvious to the users of the workbook. See “Renaming Worksheets.”

Deleting Worksheets

If a workbook contains sheets you do not plan on using, you can remove the unwanted sheets and make the workbook file a bit smaller. If a sheet contains data, it can removed as well, although you should check to be sure you do not want that data before removing it.

Follow these steps to remove a worksheet:

1.	Click the tab of the sheet you want to remove.
2.	Choose either of the following methods to remove the selected sheet: Click the arrow on the Delete button on the Home tab, and select Delete Sheet from the menu that appears. Right-click the sheet tab and select Delete from the menu that appears.
3.	If the sheet contains data, a warning dialog box appears, as shown in Figure 6-5. Click Delete to delete the sheet. If you have reconsidered and no longer want to remove the sheet, click Cancel instead. Figure 6-5. If a worksheet contains data, you must confirm that you want it deleted before Excel will remove it from the workbook. [View full size image]

Renaming Worksheets

Unlike people, worksheets are not immediately given meaningful names. Instead, worksheets typically bear generic-sounding names like Sheet1, Sheet2, and so on. If a workbook contains lots of data spread out over several worksheets, you can make it easier for users to locate the data they need by giving your worksheets names that correspond to the data they contain. For example, you might name worksheets April, May, June, and 2nd Qtr Totals.

Follow these steps to rename a worksheet:

1.	Click the tab of the sheet you want to rename.
2.	Choose any of the following methods to rename the selected sheet: Click the Format button on the Home tab, and select Rename Sheet from the menu that appears. Right-click the sheet tab and select Rename from the menu that appears. Double-click the tab of the sheet you want to rename.
3.	The current tab name is highlighted, as shown in Figure 6-6. Type a new name for the sheet, up to 31 characters. You can use numbers, letters, spaces, and special characters (such as $ and %) if you like. Figure 6-6. Identify the contents of each sheet by renaming it. [View full size image]
4.	Press Enter. The new name appears on the worksheet tab.

Copying Worksheets

An easy way to build up a complex workbook full of worksheets is to create a single sheet, and to copy that sheet over and over as needed. When you copy a worksheet, all of its data, including row and column headings, formulas, and worksheet titles, are duplicated in the copy. In addition, formatting and column widths are also copied.

Using these copies, you can then make simple adjustments (changing the title from May Revenues to June Revenues, for example), and quickly create a new worksheet. The idea behind copying worksheets is that you are not stuck with the tedious process of recreating similar worksheets over and over again in a large workbook.

You can copy a worksheet to a place within the current workbook, or to any other workbook.

Follow these steps to copy a worksheet:

Moving Worksheets

Complex workbooks with lots of worksheets often benefit from good organization—and one way in which that happens is by moving worksheets around until they appear in a logical order. Now, of course, there is no right or wrong order, but sometimes the right order will present itself— for example, the Qtr 1 Budget should probably appear before (to the left of) the Qtr 2 Budget sheet in the tab list along the bottom of the Excel window.

You can move a worksheet to a place within the current workbook, or to any other workbook.

Follow these steps to move a worksheet:

Changing Worksheet Tab Colors

Normally, a worksheet’s tab is gray until you click it to make it active, in which case it then changes to white. When you’re dealing with workbooks that contain only a few worksheets at most, this works out just fine. When working with complex workbooks that contain lots of data and worksheets, organizing the sheet tabs by color can be truly helpful. For example, in a workbook that chronicles the schedule, budget, and actual costs related to a long-term project, you might color the schedule and schedule time-line chart the same color, such as blue. Other related tabs, such as the actual costs sheets, might also bear the same color, such as red, as shown in Figure 6-8.

Figure 6-8. Use tab colors to visually organize worksheets in a workbook.

[View full size image]

Now, when you color a worksheet tab, that color appears on the tab when it is not selected, as shown in Figure 6-8. When the tab is selected, like the Actual Costs tab shown in Figure 6-8, only a hint of its tab color appears as a stripe across the bottom of the tab. In this case, if you look closely, you might see a hint of red in that stripe that runs along the bottom of the Actual Costs tab.

To change a tab’s color, follow these steps:

1.	Click the tab you want to color. You can select multiple tabs and color them all in one step—just press and hold Ctrl as you click each tab.
2.	Choose either of the following methods to color the selected tab(s): Click the Format button on the Home tab, and point to Tab Color from the menu that appears. Right-click one of the selected sheet tabs and point to Tab Color from the menu that appears.
3.	A palette of colors appears (see Figure 6-9). Click the color you want for the selected tab(s). The tab(s) are immediately changed to the color you selected, although the current tab appears in white with only a stripe of that color running along the bottom. Figure 6-9. Select a color for the tab. [View full size image] If you don’t see a color you like, select More Colors from the palette. The Colors dialog box appears, as shown in Figure 6-10, displaying the Custom tab. Figure 6-10. Mix a custom color for the tab if you want. Start by clicking the Colors palette to choose a basic color. If you don’t get the exact color you want to start with, you can click again or drag the pointer over the Colors palette. After selecting a good basic color, drag the Saturation slider to adjust the color’s lightness/darkness. If the Custom tab is too intimidating, you can quickly select a basic color without a lot of fuss. Click the Standard tab to display the standard colors, as shown in Figure 6-11. Just click any of the colored hexagons. After choosing a color, click OK to apply it to the selected tab(s). Figure 6-11. Select one of the standard colors for the tab if desired.

Displaying Data Stored Elsewhere in the Workbook

Sometimes, especially in situations in which a worksheet contains a lot of data, you might want to be able to redisplay the contents of a cell in more than one location—either on that same worksheet, or on another worksheet in the same workbook. For example, suppose you set up a workbook with a worksheet that lists each of your company’s products, and its selling price, based on whether it was sold through your catalog, online, or at a tradeshow. On other worksheets, you want to list the number of units sold for each item that month, through each of your various vertical markets. You want to display the current price on each of the monthly worksheets by simply redisplaying the prices you’ve already entered on the price sheet, as shown in Figure 6-12.

Figure 6-12. Don’t retype data—simply redisplay it when needed.

[View full size image]

Here’s how to display data stored elsewhere in the workbook:

Displaying Data Stored in Another Workbook

You can reference data stored in another workbook when needed, and display that data in the current workbook. Typically, this is done for convenience, so you do not need to open that other workbook in order to look up related information. For example, you might want to reference the product codes in an inventory workbook, for use in your marketing analysis workbook.

You can also reference the data in that other workbook for use in a formula. For example, perhaps you want to reference the current inventory total for a product, for use in a sales projection-planning-budget worksheet. Follow these steps:

1.	Open both workbooks—the workbook in which you want the referenced data to appear, and the workbook that contains that data.
2.	Click the cell where you want the data to appear.
3.	Type the equals (=) sign.
4.	Change to the workbook that contains the data you want to reference. Change to the worksheet that contains the data by clicking that worksheet’s tab. Then scroll if necessary so you can see the cell containing the data, and click it. If you want to type the cell’s address yourself, you need to follow the rules: first type the name of the workbook in square brackets (as in [HHQtr1Breakdown.xlsx]), followed by the name of the worksheet, followed by an exclamation point (!), followed by the cell address —for example, type =[HHQtr1Breakdown. xlsx]January!K8. (See Figure 6-15.) Figure 6-15. Reference a cell in another workbook when needed. [View full size image]
5.	Press Enter. The contents of the referenced cell appear in the result cell you selected in Step 1.

After you create a reference to data in another workbook, whenever you reopen the workbook in which you created the reference, you will see a warning just above the Formula bar, as shown in Figure 6-16. The warning is telling you that this workbook depends on data saved in another workbook but nothing in this workbook has been updated automatically. Because the data in the other workbook might have changed, Excel recommends that you click the Enable Content button to update it. If you don’t want to update the worksheet with data from the other workbook (because it’s wrong, or you’re in a hurry, or whatever), just ignore the warning and simply continue working. You can close the warning bar by clicking its Close button (the X) over there on the right if you want.

Figure 6-16. Update the data referenced in another workbook when needed.

[View full size image]

Linking to Elsewhere in the Same or Different Workbook

A hyperlink (or simply, a link) is a bit of text or a graphic that, when clicked, displays related information elsewhere in the same worksheet, in another worksheet, or within another workbook. You can also link to other files such as Word documents, audio or video files, or new workbooks. In addition, you can link to a Web page or an e-mail address.

When you move the mouse pointer over a hyperlink, whether the link is a bit of text or a graphic, the mouse pointer changes to a hand to indicate that if you click, the linked file, Web page, or e-mail address will be opened. (See Figure 6-17.) This behavior makes it easier to distinguish links from regular text and graphics.

In addition, when the mouse pointer moves over a hyperlink, a ScreenTip appears, displaying the location of the linked file, URL of the linked Web page, or address of the e-mail link—as shown in Figure 6-17. You can tell Excel to display different information in the ScreenTip if you want, by entering that information when you create the link.

Figure 6-17. The mouse pointer changes when placed over a hyperlink, and a ScreenTip appears.

[View full size image]

Linking to the Same Workbook

To create a hyperlink to a place within the current workbook, follow these steps:

1.	Click the cell containing the text you want to use as the link, or click a graphic.
2.	Click the Hyperlink button on the Insert tab. The Insert Hyperlink dialog box appears, as shown in Figure 6-18. Figure 6-18. Create a link to another location in this same workbook. [View full size image]
3.	Click the Place In This Document button from the Link To list.
4.	Enter the address of the cell you want to link to in the Type The Cell Reference text box. If you want to link to a cell on a different worksheet, select that worksheet from the Cell Reference section of the Or Select A Place In This Document box, and then type the cell address. Tip To save time, you can give the cell(s) within the worksheet that you want to link to range names, and then select one of those range names from the Defined Names section of the Or Select A Place In This Document box to link to that particular cell.
5.	To enter a ScreenTip for the link, click the ScreenTip button, type the description to display in the ScreenTip, and click OK to return to the dialog box.
6.	Click OK. The link is inserted into the worksheet. When you click the link, the cursor is moved to the cell you linked to.

Linking to a Different Workbook or Other File

To create a hyperlink to a place within a different workbook or to some other file such as a Word document, follow these steps:

1.	Click the cell containing the text you want to use as the link, or click a graphic.
2.	Click the Hyperlink button on the Insert tab. The Insert Hyperlink dialog box appears, as shown in Figure 6-19. Figure 6-19. Create a link to another workbook or other file. [View full size image]
3.	Click the Existing File or Web Page button from the Link To list.
4.	Enter the path to the file you want to link to in the Address text box, or select the drive and folder containing the file from the Look In list and then select the file. Tip If you have used the file recently, click the Recent Files button and select the file from those listed.
5.	To link to a specific place within a file such as another Excel workbook or a Word document, click the Bookmark button. To link to a cell on a specific worksheet, select that worksheet from the Cell Reference section of the Or Select A Place In This Document box. If you’ve created a range name or a bookmark in a Word file, select it from the Defined Names section in the Or Select A Place In This Document box. Click OK to return to the dialog box.
6.	To enter a ScreenTip for the link, click the ScreenTip button, type the description to display in the ScreenTip, and click OK to return to the dialog box.
7.	Click OK. The link is inserted into the worksheet. When you click the link, the file you linked to is opened and displayed.

Linking to a Web Page

To create a hyperlink to a Web page out on the Internet, or on your company’s network, follow these steps:

1.	Click the cell containing the text you want to use as the link, or click a graphic.
2.	Click the Hyperlink button on the Insert tab. The Insert Hyperlink dialog box appears, as shown in Figure 6-20. Figure 6-20. Create a link to a Web page. [View full size image]
3.	Click the Existing File or Web Page button from the Link To list.
4.	Enter the path to the page you want to link to in the Address text box, or click the Browse the Web button, and use your Web browser to display the page you want to link to. After displaying the page, do not close your browser, but simply switch back to Excel and the dialog box—the Web page address automatically appears in the Address box. Tip If you have visited the page recently, click the Browsed Pages button and select the page from those listed.
5.	Some Web pages are designed with bookmarks that link to places on that page. To link to a specific place within the page, click the Bookmark button, and select a place from those listed. Click OK to return to the dialog box.
6.	To enter a ScreenTip for the link, click the ScreenTip button, type the description to display in the ScreenTip, and click OK to return to the dialog box.
7.	Click OK. The link is inserted into the worksheet. When you click the link, the Web page you linked to is displayed in a Web browser.

Linking to a New Workbook

To create a hyperlink to a new workbook, follow these steps:

1.	Click the cell containing the text you want to use as the link, or click a graphic.
2.	Click the Hyperlink button on the Insert tab. The Insert Hyperlink dialog box appears, as shown in Figure 6-21. Figure 6-21. Create a new workbook to link to. [View full size image]
3.	Click the Create New Document button from the Link To list.
4.	Type a name for the new workbook in the Name of New Document text box. Tip To save the new workbook in a folder other than the current one, click the Change button, select the drive/folder in which you want to save the workbook, and click OK to return to the dialog box.
5.	If you want to edit this new workbook now, select Edit the New Document Now. Otherwise, select Edit the New Document Later.
6.	To enter a ScreenTip for the link, click the ScreenTip button, type the description to display in the ScreenTip, and click OK to return to the dialog box.
7.	Click OK. If you indicated that you wanted to edit the workbook now, it is opened in Excel so you can enter data. Regardless, the link is inserted into the worksheet. When you click the link, this new workbook is displayed.

Linking to an E-Mail Address

To create a hyperlink that generates an e-mail message pre-addressed to a particular address, follow these steps:

1.	Click the cell containing the text you want to use as the link, or click a graphic.
2.	Click the Hyperlink button on the Insert tab. The Insert Hyperlink dialog box appears, as shown in Figure 6-22. Figure 6-22. Create a link that generates an e-mail message. [View full size image]
3.	Click the E-mail Address button from the Link To list.
4.	Enter the e-mail address you want to link to in the E-Mail Address text box. Excel automatically adds the text, mailto: in front of the address you type. Tip If you’ve e-mailed this address recently, select the address to use from the Recently Used E-Mail Addresses list.
5.	Type a description for the e-mail message in the Subject box.
6.	To enter a ScreenTip for the link, click the ScreenTip button, type the description to display in the ScreenTip, and click OK to return to the dialog box.
7.	Click OK. The link is inserted into the worksheet. When you click the link, an e-mail message appears, automatically addressed to the e-mail address link.

Using and Managing Links

After creating a link, simply move the mouse pointer over it and click when the pointer changes to a hand to activate the link. Here are some tips for managing links:

• To edit the text in a cell that contains a hyperlink, you can’t just click and edit because as soon as you click, the link is activated. Instead, click a cell nearby and use the arrow keys to navigate to the cell. Then type new text for the link or edit the existing text using the Formula bar. Press Enter to save your changes.

• To edit the place to which the link points, move the cursor to the link cell. Click the Hyperlink button on the Insert tab, and the original information you used to create the link appears. Make any changes you want, and click OK to save them.

• To remove a hyperlink, move the cursor to the link cell. Click the Hyperlink button on the Insert tab and click the Remove Link button. The link is immediately removed, along with the text used to create the link.