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
Controlling the Worksheet View
As you add more information to a worksheet, you may find that navigating and locating what you want gets more difficult. Excel includes a few options that enable you to view your sheet, and sometimes multiple sheets, more efficiently. This section discusses a few additional worksheet options at your disposal.
Zooming in or out for a better view
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.
Excel contains separate options for changing the size of your printed output. (Use the controls in the Page Layout➪Scale To Fit ribbon group.) See Chapter 10 for details.
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).
CROSS-REF CROSS-REF
Zooming affects only the active worksheet, so you can use different zoom factors for different worksheets. Also, if you have a worksheet displayed in two different windows, you can set a different zoom factor for each of the windows.
If your worksheet uses named ranges (see Chapter 5), zooming your worksheet to 39 percent or less displays the name of the range overlaid on the cells. Viewing named ranges in this man- ner is useful for getting an overview of how a worksheet is laid out.
Viewing a worksheet in multiple windows
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 work- book, 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:1and climate data.xls:2. To help you keep track of the windows, Excel appends a colon and a number to each window.
If the workbook is maximized when you create a new window, you may not even notice that Excel has created the new window; but if you look at the Excel title bar, you’ll see that the workbook title now has :2appended to the name. Choose View➪Window➪Arrange and choose one of the Arrange options in the Arrange Windows dialog box to display the open windows. If you select the Windows Of Active Workbook check box, only the windows of the active workbook are arranged.
FIGURE 4.6
Use multiple windows to view different sections of a workbook at the same time.
TIP TIP
CROSS-REF CROSS-REF
TIP TIP
A single workbook can have as many views (that is, separate windows) as you want. Each window is inde- pendent 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.
Multiple windows make copying or moving information from one worksheet to another easier.
You can use Excel’s drag-and-drop procedures to copy or move ranges.
Comparing sheets side by side
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 win- dow; 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.
Splitting the worksheet window into panes
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.
TIP TIP
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.
Keeping the titles in view by freezing panes
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 horizon- tally. 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 col- umn titles and the column A entries visible.
The vast majority of the time, you’ll want to freeze either the first row or the first column.
Excel 2007 makes it a bit easier. The View➪Window➪Freeze Panes drop-down list has two additional options: Freeze Top Row and Freeze First Column. Using these commands eliminates the need to position the cell pointer before freezing panes.
If you have designated a range to be a table (by choosing Insert➪Tables➪Table), you may not even need to freeze panes. When you scroll down, Excel displays the table column head- ings in place of the column letters. Figure 4.9 shows an example. The table headings replace the column let- ters only when a cell within the table is selected.
NEW FEATURE NEW FEATURE NEW FEATURE NEW FEATURE
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.
Monitoring cells with a Watch Window
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.
Double-click a cell in the Watch Window to immediately jump to that cell.
TIP TIP
FIGURE 4.10
Use the Watch Window to monitor the value in one or more cells.
Working with Rows and Columns
This section discusses worksheet operations that involve rows and columns. Rows and columns make up an Excel worksheet. Every worksheet has exactly 1,048,576 rows and 16,384 columns, and these values can’t be changed.
If you open a workbook that was created in a previous version of Excel, the workbook is opened in “compatibility mode.” These workbooks have 65,536 rows and 256 columns. To increase the number of rows and columns, save the workbook as an Excel 2007 XLSXfile and then reopen it.
Inserting rows and columns
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.
If the last row isn’t empty, you can’t insert a new row. Similarly, if the last column contains information, Excel doesn’t let you insert a new column. Attempting to add a row or column displays the dialog box shown in Figure 4.11.
FIGURE 4.11
You can’t add a new row or column if it causes nonblank cells to move off the worksheet.