An Excel workbook file can hold any number of sheets, and these sheets can be either worksheets (sheets consisting of rows and columns) or chart sheets (sheets that hold a single chart). A worksheet is what people usually think of when they think of a spreadsheet. You can open as many Excel workbooks as necessary at the same time.

Figure 4.1 shows Excel with four workbooks open, each in a separate window. One of the windows is minimized and appears near the lower-left corner of the screen. (When a workbook is minimized, only its title bar is visible.) Worksheet windows can overlap, and the title bar of one window is a different color. That’s the window that contains the active workbook.

Figure 4.1. You can open several Excel workbooks at the same time.

The workbook windows that Excel uses work much like the windows in any other Windows program. Each window has three buttons at the right side of its title bar. From left to right, they are Minimize, Maximize (or Restore), and Close. When a workbook window is maximized, the three buttons appear directly below Excel’s title bar.

Excel’s windows can be in one of the following states:

If you work with more than one workbook simultaneously (which is quite common), you have to know how to move, resize, and switch among the workbook windows.

Moving and resizing windows

To move a window, make sure that it’s not maximized. Then click and drag its title bar with your mouse.

To resize a window, click and drag any of its borders until it’s the size that you want it to be. When you position the mouse pointer on a window’s border, the mouse pointer changes to a double-sided arrow, which lets you know that you can now click and drag to resize the window. To resize a window horizontally and vertically at the same time, click and drag any of its corners.

Note

You can’t move or resize a workbook window if it’s maximized. You can move a minimized window, but doing so has no effect on its position when it’s subsequently restored.

If you want all your workbook windows to be visible (that is, not obscured by another window), you can move and resize the windows manually, or you can let Excel do it for you. Choosing View Window Arrange All displays the Arrange Windows dialog box, shown in Figure 4.2. This dialog box has four window-arrangement options. Just select the one that you want and click OK. Windows that are minimized aren’t affected by this comment.

Figure 4.2. Use the Arrange Windows dialog box to quickly arrange all open workbook windows.

At any given time, one workbook is the active workbook, and one sheet is the active sheet in the active workbook. To activate a different sheet, just click its sheet tab, located at the bottom of the workbook window. You also can use the following shortcut keys to activate a different sheet:

If your workbook has many sheets, all its tabs may not be visible. Use the tab-scrolling controls (see Figure 4.3) to scroll the sheet tabs. The sheet tabs share space with the worksheet’s horizontal scroll bar. You also can drag the tab split control to display more or fewer tabs. Dragging the tab split control simultaneously changes the number of tabs and the size of the horizontal scroll bar.

Figure 4.3. Use the tab controls to activate a different worksheet or to see additional worksheet tabs.

Worksheets can be an excellent organizational tool. Instead of placing everything on a single worksheet, you can use additional worksheets in a workbook to separate various workbook elements logically. For example, if you have several products whose sales you track individually, you may want to assign each product to its own worksheet and then use another worksheet to consolidate your results.

The following are three ways to add a new worksheet to a workbook:

If you no longer need a worksheet, or if you want to get rid of an empty worksheet in a workbook, you can delete it in either of two ways:

The default names Excel uses for worksheets—Sheet1, Sheet2, and so on—aren’t very descriptive. If you don’t change the worksheet names, remembering where to find things in multiple-sheet workbooks can be a bit difficult. That’s why providing more meaningful names for your worksheets is often a good idea.

To change a sheet’s name, double-click the sheet tab. Excel highlights the name on the sheet tab so that you can edit the name or replace it with a new name.

Sheet names can be up to 31 characters, and spaces are allowed. However, you can’t use the following characters in sheet names:

Keep in mind that a longer worksheet name results in a wider tab, which takes up more space on-screen. Therefore, if you use lengthy sheet names, you won’t be able to see very many sheet tabs without scrolling the tab list.

Excel allows you to change the color of your worksheet tabs. For example, you may prefer to color-code the sheet tabs to make identifying the worksheet’s contents easier.

To change the color of a sheet tab, right-click the tab and choose Tab Color. Then select the color from the color selector box.

You may want to rearrange the order of worksheets in a workbook. If you have a separate worksheet for each sales region, for example, arranging the worksheets in alphabetical order or by total sales may be helpful. You may want to move a worksheet from one workbook to another. (To move a worksheet to a different workbook, both workbooks must be open.) You can also create copies of worksheets.

You can move or copy a worksheet in the following ways:

If you move or copy a worksheet to a workbook that already has a sheet with the same name, Excel changes the name to make it unique. For example, Sheet1 becomes Sheet1 (2).

In some situations, you may want to hide one or more worksheets. Hiding a sheet may be useful if you don’t want others to see it or if you just want to get it out of the way. When a sheet is hidden, its sheet tab is also hidden. You can’t hide all the sheets in a workbook, so at least one sheet must remain visible.

To hide a worksheet, right-click its sheet tab and choose Hide. The active worksheet (or selected worksheets) will be hidden from view.

To unhide a hidden worksheet, right-click any sheet tab and choose Unhide. Excel opens its Unhide dialog box that lists all hidden sheets. Choose the sheet that you want to redisplay and click OK. You can’t select multiple sheets from this dialog box, so you need to repeat the command for each sheet that you want to unhide.

Preventing Sheet Actions

To prevent others from unhiding hidden sheets, inserting new sheets, renaming sheets, copying sheets, or deleting sheets, protect the workbook’s structure:

1. Choose Review Changes Protect Workbook.

2. In the Protect Workbook dialog box, click the Structure option.

3. Provide a password, if you like.

After performing these steps, several commands will no longer be available when you right-click a sheet tab: Insert, Delete, Rename, Move or Copy, Hide, and Unhide. Be aware, however, that this is a very weak security measure. Cracking Excel’s protection features is relatively easy.

You can also make a sheet “very hidden.” A sheet that is very hidden doesn’t appear in the Unhide dialog box. To make a sheet very hidden:

1. Activate the worksheet.

2. Choose Developer Controls Properties. The Properties dialog box, shown in the following figure, appears. (If the Developer tab isn’t available, you can turn it on using the Popular tab of the Excel Options dialog box.)

3. In the Properties box, select the Visible option and choose 2 - xlSheetVeryHidden.

After performing these steps, the worksheet is hidden and doesn’t appear in the Unhide dialog box.

Be careful! After you make a sheet very hidden, you can’t use the Properties box to unhide it because you aren’t able to select the sheet! In fact, the only way to unhide such a sheet is to use a VBA macro. (See Part VI for more information about VBA.) For example, this VBA statement unhides Sheet1 in the active workbook:

ActiveWorkbook.Worksheets("Sheet1").Visible = True

Normally, everything you see on-screen is displayed at 100 percent. You can change the zoom percentage from 10 percent (very tiny) to 400 percent (huge). Using a small zoom percentage can help you to get a bird’s-eye view of your worksheet to see how it’s laid out. Zooming in is useful if your eyesight isn’t quite what it used to be and you have trouble deciphering tiny type. Zooming doesn’t change the font size, so it has no effect on printed output.

Figure 4.5 shows a window zoomed to 10 percent and a window zoomed to 400 percent.

Figure 4.5. You can zoom in or out for a better view of your worksheets.

You can easily change the zoom factor of the active worksheet by using the Zoom slider located on the right side of the status bar. Click and drag the slider, and your screen transforms instantly.

Another way to zoom is to choose View Zoom Zoom, which displays a dialog box. Choosing View Zoom Zoom To Selection zooms the worksheet to display only the selected cells (useful if you want to view only a particular range).

Sometimes, you may want to view two different parts of a worksheet simultaneously—perhaps to make referencing a distant cell in a formula easier. Or you may want to examine more than one sheet in the same workbook simultaneously. You can accomplish either of these actions by opening a new view to the workbook, using one or more additional windows.

To create and display a new view of the active workbook, choose View Window New Window.

Excel displays a new window for the active workbook, similar to the one shown in Figure 4.6. In this case, each window shows a different worksheet in the workbook. Notice the text in the windows’ title bars: climate data.xls:1 and climate data.xls:2. To help you keep track of the windows, Excel appends a colon and a number to each window.

Figure 4.6. Use multiple windows to view different sections of a workbook at the same time.

A single workbook can have as many views (that is, separate windows) as you want. Each window is independent of the others. In other words, scrolling to a new location in one window doesn’t cause scrolling in the other window(s).

You can close these additional windows when you no longer need them. For example, clicking the Close button on the active window’s title bar closes the active window but doesn’t close the other windows for the workbook.

In some situations, you may want to compare two worksheets that are in different windows. The View Side By Side feature makes this task a bit easier.

First, make sure that the two sheets are displayed in separate windows. (The sheets can be in the same workbook or in different workbooks.) If you want to compare two sheets in the same workbook, choose View Window New Window to create a new window for the active workbook. Activate the first window; then choose View Window View Side by Side. If more than two windows are open, you see a dialog box that lets you select the window for the comparison. The two windows appear next to each other.

When using the Compare Side by Side feature, scrolling in one of the windows also scrolls the other window. If, for some reason, you don’t want this simultaneous scrolling, choose View Window Synchronous Scrolling (which is a toggle). If you have rearranged or moved the windows, choose View Window Reset Window Position to restore the windows to the initial side-by-side arrangement. To turn off the side-by-side viewing, choose View Window View Side by Side again.

Keep in mind that this feature is for manual comparison only. Unfortunately, Excel doesn’t provide a way to show you the differences between two sheets.

If you prefer not to clutter your screen with additional windows, Excel provides another option for viewing multiple parts of the same worksheet. Choosing View Window Split splits the active worksheet into two or four separate panes. The split occurs at the location of the cell pointer. If the cell pointer is in row 1 or column A, this command results in a 2-pane split. Otherwise, it gives you four panes. You can use the mouse to drag the individual panes to resize them.

Figure 4.7 shows a worksheet split into two panes. Notice that row numbers aren’t continuous. In other words, splitting panes enables you to display in a single window widely separated areas of a worksheet. To remove the split panes, choose View Window Split again.

Figure 4.7. You can split the worksheet window into two or four panes to view different areas of the worksheet at the same time.

Another way to split and unsplit panes is to drag either the vertical or horizontal split bar. These bars are the small rectangles that normally appear just above the top of the vertical scroll bar and just to the right of the horizontal scroll bar. When you move the mouse pointer over a split bar, the mouse pointer changes to a pair of parallel lines with arrows pointing outward from each line. To remove split panes by using the mouse, drag the pane separator all the way to the edge of the window or just double-click it.

If you set up a worksheet with row or column headings, these headings will not be visible when you scroll down or to the right.. Excel provides a handy solution to this problem: freezing panes. Freezing panes keeps the headings visible while you’re scrolling through the worksheet.

To freeze panes, start by moving the cell pointer to the cell below the row that you want to remain visible as you scroll vertically, and to the right of the column that you want to remain visible as you scroll horizontally. Then, choose View Window Freeze Panes and select the Freeze Panes option from the drop-down list. Excel inserts dark lines to indicate the frozen rows and columns. The frozen row and column remain visible as you scroll throughout the worksheet. To remove the frozen panes, choose View Window Freeze Panes, and select the Unfreeze Panes option from the drop-down list.

Figure 4.8 shows a worksheet with frozen panes. In this case, rows 1:3 and column A are frozen in place. This technique allows you to scroll down and to the right to locate some information while keeping the column titles and the column A entries visible.

Figure 4.8. By freezing certain columns and rows, they remain visible while you scroll the worksheet.

Figure 4.9. When using a table, scrolling down displays the table headings where the column letters normally appear.

In some situations, you may want to monitor the value in a particular cell as you work. As you scroll throughout the worksheet, that cell may disappear from view. A feature known as Watch Window can help. A Watch Window displays the value of any number of cells in a handy window that’s always visible.

To display the Watch Window, choose Formulas Formula Auditing Watch Window. The Watch Window appears in the task pane, but you can also drag it and make it float over the worksheet.

To add a cell to watch, click Add Watch and specify the cell that you want to watch. The Watch Window displays the value in that cell. You can add any number of cells to the Watch Window, and you can move the window to any convenient location. Figure 4.10 shows the Watch Window monitoring four cells.

Figure 4.10. Use the Watch Window to monitor the value in one or more cells.

Although the number of rows and columns in a worksheet is fixed, you can still insert and delete rows and columns if you need to make room for additional information. These operations don’t change the number of rows or columns. Rather, inserting a new row moves down the other rows to accommodate the new row. The last row is simply removed from the worksheet if it’s empty. Inserting a new column shifts the columns to the right, and the last column is removed if it’s empty.

Figure 4.11. You can’t add a new row or column if it causes nonblank cells to move off the worksheet.

To insert a new row or rows, you can use any of these techniques:

The procedures for inserting a new column or columns is similar, but you choose Home Cells Insert Insert Sheet Columns.

You also can insert cells, rather than just rows or columns. Select the range into which you want to add new cells and then choose Home Cells Insert Insert Cells (or right-click the selection and choose Insert). To insert cells, the existing cells must be shifted to the right or shifted down. Therefore, Excel displays the Insert dialog box shown in Figure 4.12 so that you can specify the direction in which you want to shift the cells.

Figure 4.12. You can insert partial rows or columns by using the Insert dialog box.

You may also want to delete rows or columns in a worksheet. For example, your sheet may contain old data that is no longer needed.

To delete a row or rows, use either of these methods:

Deleting columns works in a similar way. If you discover that you accidentally deleted a row or column, select Undo from the Quick Access Toolbar (or press Ctrl+Z) to undo the action.

In some cases, you may want to hide particular rows or columns. Hiding rows and columns may be useful if you don’t want users to see particular information or if you need to print a report that summarizes the information in the worksheet without showing all the details.

To hide rows or columns in your worksheet, select the row or rows that you want to hide by clicking in the row or column header. Then right-click and choose Hide from the shortcut menu. Or, you can use the commands on the Home Cells Format drop-down list.

A hidden row is actually a row with its height set to zero. Similarly, a hidden column has a column width of zero. When you use the arrow keys to move the cell pointer, cells in hidden rows or columns are skipped. In other words, you can’t use the arrow keys to move to a cell in a hidden row or column.

Unhiding a hidden row or column can be a bit tricky because selecting a row or column that’s hidden is difficult. The solution is to select the columns or rows that are adjacent to the hidden column or row. (Select at least one column or row on either side.) Then right-click and choose Unhide. For example, if column G is hidden, select columns F and H.

Another method is to choose Home Find & Select Go To (or its F5 equivalent) to select a cell in a hidden row or column. For example, if column A is hidden, you can press F5 and specify cell A1 (or any other cell in column A) to move the cell pointer to the hidden column. Then you can choose Home Cells Format Hide & Unhide Unhide Columns.

Often, you’ll want to change the width of a column or the height of a row. For example, you can make columns narrower to accommodate more information on a printed page. Or you may want to increase row height to create a “double-spaced” effect.

Excel provides several different ways to change the widths of columns and the height of rows.