Section 4.1. Worksheets and Workbooks | Excel 2007 for Starters. The Missing Manual

4.1. Worksheets and Workbooks

Many workbooks contain more than one table of information. For example, you might have a list of the items you've purchased over two consecutive years . You might find it a bit challenging to arrange these different tables. You could stack them (Figure 4-1) or place them side by side (Figure 4-2), but neither solution is perfect.

Most Excel masters agree that the best way to arrange separate tables of information is to use separate worksheets for each table. When you create a new workbook, Excel automatically fills it with three blank worksheets named Sheet1, Sheet2, and Sheet3. Often, you'll work exclusively with the first worksheet (Sheet1), and not even realize that you have two more blank worksheets to play withnot to mention the ability to add plenty more.

Figure 4-1. Stacking tables on top of each other is usually a bad idea. If you need to add more data to the first table, then you have to move the second table. You'll also have trouble properly resizing or formatting columns because each column contains data from two different tables.

Figure 4-2. You're somewhat better off putting tables side by side, separated by a blank column, than you are stacking them, but this method can create problems if you need to add more columns to the first table. It also makes for a lot of side-to-side scrolling.

To move from one worksheet to another, you have a few choices:

Click the worksheet tabs at the bottom of Excel's grid window (just above the status bar), as shown in Figure 4-3.
Press Ctrl+Page Down to move to the next worksheet. For example, if you're currently in Sheet1, this key sequence jumps you to Sheet2.
Press Ctrl+Page Up to move to the previous worksheet. For example, if you're currently in Sheet2, this key sequence takes you back to Sheet1.

Excel keeps track of the active cell in each worksheet. That means if you're in cell B9 in Sheet1, and then move to Sheet2, when you jump back to Sheet1 you'll automatically return to cell B9.

Figure 4-3. Worksheets provide a good way to organize multiple tables of data. To move from one worksheet to another, click the appropriate Worksheet tab at the bottom of the grid. Each worksheet contains a fresh grid of cells .

Tip: Excel includes some interesting viewing features that let you look at two different worksheets at the same time, even if these worksheets are in the same workbook. You'll learn more about custom views in Chapter 6.

4.1.1. Adding, Removing, and Hiding Worksheets

When you open a fresh workbook in Excel, you automatically get three blank worksheets in it. You can easily add more worksheets. Just click the Insert Worksheet button, which appears immediately to the right of your last worksheet tab (Figure 4-4). You can also use the Home Cells Insert Insert Sheet command, which works the same way but inserts a new worksheet immediately to the left of the current worksheet. (Don't panic; Section 4.1.2 shows how you can rearrange worksheets after the fact.)

If you continue adding worksheets, you'll eventually find that all the worksheet tabs won't fit at the bottom of your workbook window. If you run out of space, you need to use the scroll buttons (which are immediately to the left of the worksheet tabs) to scroll through the list of worksheets. Figure 4-5 shows the scroll buttons .

Figure 4-4. Every time you click the Insert Worksheet button, Excel inserts a new worksheet after your existing worksheets and assigns it a new name . For example, if you start with the standard Sheet1, Sheet2, and Sheet3 and click the Insert Worksheet button, then Excel adds a new worksheet namedyou guessed itSheet4.

Figure 4-5. Using the scroll buttons, you can move between worksheets one at a time or jump straight to the first or last tab. These scroll buttons control only which tabs you seeyou still need to click the appropriate tab to move to the worksheet you want to work on.

Tip: If you have a huge number of worksheets and they don't all fit in the strip of worksheet tabs, there's an easier way to jump around. Right-click the scroll buttons to pop up a list with all your worksheets. You can then move to the worksheet you want by clicking it in the list.

Removing a worksheet is just as easy as adding one. Simply move to the worksheet you want to get rid of, and then choose Home Cells Delete Delete Sheet (you can also right-click a worksheet tab and choose Delete). Excel wont complain if you ask it to remove a blank worksheet, but if you try to remove a sheet that contains any data, it presents a warning message asking for your confirmation. Also, if you're down to one last worksheet, Excel won't let you remove it. Doing so would create a tough existential dilemma for Excela workbook that holds no work-sheetsso the program prevents you from taking this step.

Warning: Be careful when deleting worksheets, as you can't use Undo (Ctrl+Z) to reverse this change! Undo also doesn't work to reverse a newly inserted sheet.

Excel starts you off with three worksheets for each workbook, but changing this setting's easy. You can configure Excel to start with fewer worksheets (as few as one), or many more (up to 255). Select Office button Excel Options, and then choose the Popular section. Under the heading "When creating new workbooks change the number in the "Include this many sheets" box, and then click OK. This setting takes effect the next time you create a new workbook.

Note: Although you're limited to 255 sheets in a new workbook, Excel doesn't limit how many worksheets you can add after you've created a workbook. The only factor that ultimately limits the number of worksheets your workbook can hold is your computer's memory. However, modern day PCs can easily handle even the most ridiculously large, worksheet stuffed workbook.

Deleting worksheets isn't the only way to tidy up a workbook or get rid of information you don't want. You can also choose to hide a worksheet temporarily. When you hide a worksheet, its tab disappears but the worksheet itself remains part of your spreadsheet file, available whenever you choose to unhide it. Hidden worksheets also don't appear on printouts. To hide a worksheet, right-click the worksheet tab and choose Hide. (Or, for a more long-winded approach, choose Home Cells Format Hide & Unhide Hide Sheet.)

To redisplay a hidden worksheet, right-click any worksheet tab and choose Unhide. The Unhide dialog box appears along with a list of all hidden sheets, as shown in Figure 4-6. You can then select a sheet from the list and click OK to unhide it. (Once again, the ribbon can get you the same windowjust point yourself to Home Cells Format Hide & Unhide Unhide Sheet.)

Figure 4-6. This workbook contains two hidden worksheets. To restore one, just select it from the list, and then click OK. Unfortunately, if you want to show multiple hidden sheets, you have to use the Unhide Sheet command multiple times. Excel has no shortcut for unhiding multiple sheets at once.

4.1.2. Naming and Rearranging Worksheets

The standard names Excel assigns to new worksheetsSheet1, Sheet2, Sheet3, and so onaren't very helpful for identifying what they contain. And they become even less helpful if you start adding new worksheets, since the new sheet numbers don't necessarily indicate the position of the sheets, just the order in which you created them.

For example, if you're on Sheet 3 and you add a new worksheet (by choosing Home Cells Insert Insert Sheet), then the worksheet tabs read: Sheet1, Sheet2, Sheet4, Sheet3. (Thats because the Insert Sheet command inserts the new sheet just before your current sheet.) Excel doesn't expect you to stick with these auto-generated names. Instead, you can rename them by right-clicking the worksheet tab and selecting Rename, or just double-click the sheet name. Either way, Excel highlights the worksheet tab, and you can type a new name directly onto the tab. Figure 4-7 shows worksheet tabs with better names.

Figure 4-7. Worksheet names can be up to 31 characters long and can include letters , numbers, some symbols, and spaces.

Note: Excel has a small set of reserved names that you can never use. To witness this problem, try to create a worksheet named History. Excel doesn't let you because it uses the History worksheet as part of its change tracking features. Use this Excel oddity to impress your friends .

Sometimes Excel refuses to insert new worksheets exactly where you'd like them. Fortunately, you can easily rearrange any of your worksheets just by dragging their tabs from one place to another, as shown in Figure 4-8.

Figure 4-8. When you drag a worksheet tab, a tiny page appears beneath the arrow cursor. As you move the cursor around, you'll see a black triangle appear, indicating where the worksheet will land when you release the mouse button.

Tip: You can use a similar technique to create copies of a worksheet. Click the worksheet tab and begin dragging, just as you would to move the worksheet. However, before releasing the mouse button, press the Ctrl key (you'll see a plus sign [+] appear). When you let go, Excel creates a copy of the worksheet in the new location. The original worksheet remains in its original location. Excel gives the new worksheet a name with a number in parentheses. For example, a copy of Sheet1 is named Sheet1 (2). As with any other worksheet tab, you can change this name.

GEM IN THE ROUGH
Colorful Worksheet Tabs

Names aren't the only thing you can change when it comes to newly added worksheets. Excel also lets you modify a worksheet tab's background color . This minor convenience has no effect on your data or your printout, but it can help you quickly find an important worksheet if it has lots of neighbors.

To change the background color of a worksheet tab, right-click the tab, and then select Tab Color (or move to the appropriate worksheet and Home Cells Format Tab Color). A list of color choices appears; make your selection by clicking the color you want.