4.1. Worksheets and Workbooks
Many workbooks contain more than one table of information. For example, you might have a list of your bank account balances and a list of items repossessed from your home in the same financial planning spreadsheet. 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.
To move from one worksheet to another, you have a few choices:
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.
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 7.
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 .
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 worksheetsso 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.)
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.
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 (Section 23.3). 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.
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.
4.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's just a nifty timesaver.
Here are some operationsall of which are explained in detail belowthat you can simultaneously perform on worksheets that are grouped together:
To group worksheets, hold down Ctrl while clicking multiple worksheet tabs. When you're finished making your selections, release the Ctrl key. Figure 4-9 shows an example.
Tip: As a shortcut, you can select all the worksheets in a workbook by right-clicking any tab and choosing Select All Sheets.
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.
188.8.131.52. Moving, copying, deleting, or hiding grouped worksheets
As your workbook grows, you'll often need better ways to manage the collection of worksheets you've accumulated . For example, you might want to temporarily hide a number of worksheets, or move a less important batch of worksheets from the front (that is, 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 might even want to relocate several worksheets to a brand new workbook.
It's easy to perform an action on a group of worksheets. For example, when you have a group of worksheets selected, you can drag them en masse from one location to another in the worksheet tab holder. To delete or hide a group of sheets, just right-click one of the worksheet tabs in your group, and then choose Delete or Hide. Excel then deletes or hides all the selected worksheets (provided that action will leave at least one visible worksheet in your workbook).
184.108.40.206. Formatting cells, columns, and rows in grouped worksheets
When you format cells inside one grouped worksheet, it triggers the same changes in the cells in the other grouped worksheets. So you have another tool you can use to apply consistent formatting over a batch of worksheets. It's mainly useful when your worksheets are all structured in the same way.
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. Or say you group Sheet1 and Sheet2, and then change the font of column B in Sheet2Excel automatically changes the font 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. (To change the font in the currently selected cells, just select the column and, in the Home Font section of the ribbon, make a new font choice from the font list. Youll learn much more about the different types of formatting you can apply to cells in Chapter 5.)
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. Excel automatically applies the changes to the other sheet.
220.127.116.11. Entering data or changing cells in grouped worksheets
With grouped worksheets, you can also modify the contents of individual cells, including entering or changing text and clearing cell contents. For example, if you enter a new value in cell B4 in Sheet2, Excel enters the same value into cell B4 in the grouped Sheet1. Even more interesting, 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 them, but it does have its moments. Once again, it makes most sense when all the worksheets have the same structure. For example, you could use this technique to put the same copyright message in cell A1 on every worksheet; or, to add the same column titles to multiple tables ( assuming they're arranged in exactly the same way).
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 see no change. However, if cell A3 contains data on other worksheets that are grouped, these cells are now empty. Grouper beware.
18.104.22.168. Cutting, copying, and pasting cells in grouped worksheets
Cut and paste operations work the same way as entering or modifying grouped cells. Whatever action you perform on one grouped sheet, Excel also performs on other grouped sheets. For example, consider what happens if you've grouped together Sheet1 and Sheet2, and you copy cell A1 to A2 in Sheet1. The same action takes place in Sheet2in other words, the contents of cell A1 (in Sheet2) is copied to cell A2 (also in Sheet2). Obviously, Sheet1 and Sheet2 might have different content in cell A1 and A2the grouping simply means that whatever was in cell A1 will now also be in cell A2.
22.214.171.124. Adjusting printing and display options in grouped worksheets
Excel keeps track of printing and display settings on a per-worksheet basis. In other words, when you set the zoom percentage (Section 7.1.1) to 50% in one worksheet so you can see more data, it doesn't affect the zoom in another worksheet. However, when you make the change for a group of worksheets, they're all affected in the same way.
4.1.4. Moving Worksheets from One Workbook 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 linens 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:
Note: If there are any worksheet name conflicts, Excel adds a number in parentheses after the moved sheet's name. For example, if you try to copy a worksheet named Sheet1 to a workbook that already has a Sheet1, Excel names the copied worksheet Sheet1 (2).