Excel 2007 for Starters: The Missing Manual

2017-07-07 02:10:07

5.1. Worksheets and Workbooks

When you create a new workbook, Excel automatically names it Book1 and fills it with three blank worksheets named Sheet1, Sheet2, and Sheet3. Each worksheet contains the same grid of cellsfrom A1 all the way to IV65536. Often, you'll work exclusively with the first worksheet (Sheet1) and not even realize that you have two more blank worksheets to play with.

Tip: You can tell which worksheet is currently active (selected) by taking a look at the color of each worksheet's tab. A white tab means you've selected that sheet; a gray tab means the sheet is awaiting your click.

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 5-2.

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.

5.1.1. Adding, Removing, and Hiding Worksheets

When you open a fresh workbook in Excel, you automatically get three cleverly named blank worksheets: Sheet1, Sheet2, and Sheet3. Adding more worksheets is easy. Just choose Insert Worksheet, and Excel inserts a new worksheet just before the current worksheet and assigns it a new name. For example, if you move to Sheet2 and select Insert Worksheet, Excel adds a new worksheet between Sheet1 and Sheet2. Excel names this worksheet Sheet4, because its the fourth worksheet added to your workbook. (You can rearrange your worksheets into a more reasonable order; see Section 5.1.2.)

Figure 5-2. To move from one worksheet to another, click the appropriate Worksheet tab at the bottom of the grid. Each worksheet contains the same grid of cellsfrom A1 all the way to IV65536.

If you add enough worksheets, you'll run out of room in the narrow tab strip. If this happens, you need to use the scroll buttons (which are immediately to the left of the worksheet tabs) to scroll through the list of worksheets, as shown in Figure 5-3. Using these buttons, you can move one worksheet 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.

Figure 5-3. The scroll buttons (located immediately to the left of the worksheet tabs) scroll through the list of worksheets.

Removing a worksheet is just as easy as adding one. Simply move to the worksheet you want to remove and then choose Edit 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 worksheets so the program prevents you from deleting the last worksheet.

Warning: Be careful when deleting worksheets, as you can't use Undo (Ctrl+Z) to reverse this change! Undo won't reverse the insertion of a new worksheet, either.

Excel comes set to start you off with three worksheets for each workbook, but changing this setting is easy. You can configure Excel to start with fewer worksheets (as few as one), or many more (up to 255). Select Tools Options and choose the General tab. Change the number in the "Sheets in new workbook box, and 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, most modern-day PCs can easily handle even the largest of workbooks.

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, select it and choose Format Sheet Hide. To redisplay a hidden worksheet, choose Format Sheet Unhide. The Unhide dialog box appears along with a list of all hidden sheets, as shown in Figure 5-4. You can then select a sheet from the list and click OK to unhide it.

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

5.1.2. Naming and Rearranging Worksheets

The standard names Excel assigns to new worksheetsSheet1, Sheet2, Sheet3, and so onaren't exactly helpful for identifying their contents. And they become even less helpful if you start adding new worksheets, since the new sheet numbers don't even 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, the worksheet tabs will read: Sheet1, Sheet2, Sheet4, Sheet3. 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-clicking the sheet name. Either way, Excel highlights the worksheet tab, and you can type a new name directly onto the tab. Figure 5-5 shows worksheet tabs with better names.

Figure 5-5. Worksheet names can be up to thirty-one characters long.

Note: You can create worksheet names up to 31 characters long that include letters, numbers, symbols, and spaces. Remember, though, the longer the worksheet name, the fewer worksheet tabs you'll be able to see at once, and the more you'll need to rely on the scroll buttons to the left of the worksheet tabs. For the sake of convenience, try to keep your names brief (but descriptive) by using titles like Purchases, Sales, and Expenses.

Sometimes Excel refuses to insert new worksheets exactly where you'd like them. For example, because it always inserts new worksheets before the currently active worksheet, there's no way to insert a new worksheet after the last worksheet in your workbook. Fortunately, you can easily rearrange any of your worksheets just by dragging their tabs from one place to another, as shown in Figure 5-6.

Figure 5-6. 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 by double-clicking it and typing in a new name.

STYLISH FRILLS

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 select Tab Color (or select the worksheet and select Format Sheet Tab Color). A window appears; make your selection by clicking the color you want.

5.1.3. Grouping Sheets

As you've seen in previous chapters, Excel lets you work with more than one column, row, or cell at a time. The same holds true for worksheets. You can select multiple worksheets and perform an operation on all of them at once. This process of selecting multiple sheets is called grouping, and it's helpful if you need to hide or format several worksheets (for example, if you want to make sure all your worksheets start with a bright-yellow first row), and you don't want the hassle of selecting them one at a time. Grouping sheets doesn't let you do anything you couldn't do ordinarilyit just speeds up your work.

Here are some operationsall of which are explained in detail belowthat you can simultaneously perform on worksheets that are grouped together:

You can move, copy, delete, or hide the worksheets.

You can apply formatting to individual cells, columns, rows, or even entire worksheets.

You can type in new text, change text, or clear cells.

You can cut, copy, and paste cells.

You can adjust options like those under File Page Setup (used for printing), as well as some of the display options on the View tab that appears when you select Tools Options.

To group worksheets, hold down Ctrl while clicking multiple worksheet tabs. When you're finished making your selections, release the Ctrl key.

Tip: As a shortcut, you can select all the worksheets in a workbook by right-clicking any tab and choosing Select All Sheets.

When worksheets are grouped, their tab colors change from gray to white. Also, in workbooks with groups, the title bar of the Excel window includes the word [Group] added to the end of the file name, as shown in Figure 5-7.

Figure 5-7. In this example, you can tell Sheet2 and Sheet3 are grouped because the tabs are white.

To ungroup worksheets, right-click one of the worksheet tabs and select Ungroup Sheets, or just click one of the worksheet tabs that isn't in your group. You can also remove a single worksheet from a group by clicking it while holding down Ctrl. However, this technique works only if the worksheet you want to remove from the group is not the currently active worksheet.

5.1.3.1. Moving, copying, deleting, or hiding grouped worksheets

As your workbook grows, you may find you need better ways to manage the collection of worksheets you've accumulated. For example, you may want to temporarily hide a number of worksheets, or move a less important batch of worksheets from the front (i.e., the left side) of the worksheet tab holder to the end (the right side). And if a workbook's got way too many worksheets, you may even want to relocate several worksheets to a new workbook with more space. (The section "Moving Worksheets from One Workbook (Excel File) to Another" on Section 5.1.3.5 shows you how.)

Performing an action on a group of worksheets is easy. For example, after you have a group of worksheets selected, you can drag them en masse from one location to another in the worksheet tab holder. In addition, if you choose a command like Edit Delete Sheet, Excel removes all the selected worksheets (provided that action will leave at least one visible worksheet in your workbook).

5.1.3.2. Formatting cells, columns, and rows in grouped worksheets

When you format cells inside one grouped worksheet, Excel triggers the same changes in the cells in the other grouped worksheets. This approach lets you apply consistent formatting over a batch of worksheets.

For example, imagine you've created a workbook with 10 worksheets, each one representing a different customer order. If you group all 10 worksheets together and then format just the first one, Excel formats all the worksheets in exactly the same way. Then, say you group Sheet1 and Sheet2 and then change the fill color of column B in Sheet2Excel automatically changes the fill color in column B in Sheet1, too.

The same is true if you change the formatting of individual cells or the entire worksheetExcel replicates these changes across the group.

Note: It doesn't matter which worksheet you modify in a group. For example, if Sheet1 and Sheet2 are grouped, you can modify the formatting in either worksheet, and Excel will automatically apply the changes to the other sheet.

5.1.3.3. Typing in data or changing cells in grouped worksheets

With grouped worksheets, you can also modify the contents of individual cells, including typing in text, changing text, and clearing cell contents. For example, if you type in a new value in cell B4 in Sheet2, Excel puts the same value into cell B4 in the grouped Sheet1. Even cooler, if you modify a value in a cell in Sheet2, the same value appears in the same cell in Sheet1, even if Sheet1 didn't previously have a value in that cell. Similar behavior occurs when you delete cells.

Editing a group of worksheets at once isn't as useful as moving and formatting themafter all, how often do you want to create worksheets containing identical data?but it does have its moments. For example, you could use this technique to put the same copyright message in cell A1 on every worksheet.

Warning: Be careful to remember the magnified power your keystrokes possess when you're operating on grouped worksheets. For example, imagine that you move to cell A3 on Sheet1, which happens to be empty. If you click Delete, you'll see no change. However, if cell A3 contains data on other worksheets that are grouped, these cells will now be empty. Grouper beware!

5.1.3.4. Cutting, copying, and pasting cells in grouped worksheets

Cut-and-paste operations work the same way as modifying grouped cells. Whatever action you perform on one grouped sheet, Excel also performs on other grouped sheets. For example, if you copy cell A1 to A2 in Sheet2, a similar sequence occurs in any other worksheet you've grouped with Sheet2. That doesn't mean that all these worksheets will have the same content in cell A2. It just means that whatever happens to be in cell A1 will now also be in cell A2.

5.1.3.5. Adjusting printing and display options in grouped worksheets

Excel keeps track of printing and display settings on a per-worksheet basis. In other words, if you set the zoom percentage to 50% in one (ungrouped) worksheet so you can see more data, your change won't affect the zoom in another worksheet. However, if you make the change for a group of worksheets, they'll all be affected in the same way.

5.1.4. Moving Worksheets from One Workbook (Excel File) to Another

Once you get the hang of creating different worksheets for different types of information, your Excel files can quickly fill up with more sheets than a linen store. What happens when you want to shift some of these worksheets around? For instance, you may want to move (or copy) a worksheet from one Excel file to another. Here's how:

Open both spreadsheet files in Excel.
The file that contains the worksheet you want to move or copy is called the source file; the other file (where you want to move or copy the worksheet to) is known as the destination file.

Go to the source workbook.
Remember, you can move from one window to another using the Windows taskbar, or by choosing the file's name from Excel's Window menu.

Select the worksheet you want to transfer.
To choose a worksheet, just click the appropriate worksheet tab. If you want, you can transfer multiple worksheets at once. Just hold down the Ctrl key and select all the worksheets you want to move or copy. Excel highlights all the worksheets you select (and groups them together).

Choose Edit Move or Copy Sheet.
Figure 5-8. Here, the worksheet you selected in step 3 is about to be moved into the SimpleExpenses.xls workbook (the destination file), just before Sheet1. Because the "Create a copy" checkbox is not turned on, Excel removes the worksheet from the source workbook after it completes the transfer.

Choose the destination file from the "To book" list.
The "To book" drop-down list shows all the currently open workbooks (including the source workbook).
Tip: Excel also lets you move your worksheets to a brand-new workbook, which it creates automatically for you. Here's how: choose the "(new book)" item in the "To book" list. The new workbook won't have the standard three worksheets. Instead, it will only have the worksheets you've transferred.

Specify the position where you want the worksheet inserted.
Choose a destination worksheet from the "Before sheet" list. Excel will place the copied worksheets just before the worksheet you select. If you want to place the worksheets at the end of the destination workbook, select "(move to end)." Of course, you can always rearrange the worksheets after you transfer them, so you don't need to worry too much about getting the perfect placement.

If you want to copy the worksheet, turn on the "Create a copy" checkbox at the bottom of the window.
If you don't turn this option on, Excel will copy the worksheet to the destination workbook and remove it from the current workbook. If you do turn this option on, you'll end up with a copy of the workbook in both places.

Click OK.
This final step closes the Move or Copy dialog box and transfers the worksheet (or worksheets).

Note: If Excel encounters any worksheet name conflicts, it simply adds a number in parentheses after the moved sheet's name. For example, if you try to copy a worksheet named Purchases to a workbook that already has a Purchases, Excel will name the copied worksheet Purchases (2).