Viewing Worksheets

Excel provides a few helpful features you can use to change the way worksheets display. You can set up your workspace for specific tasks and then save the same view settings for the next time you need to perform the same task.

Splitting Worksheets into Panes

Worksheet panes let you view different areas of your worksheet simultaneously. You can split any worksheet in a workbook vertically, horizontally, or both vertically and horizontally, with synchronized scrolling capabilities. On the worksheet shown in Figure 6-17, columns B through M and rows 4 through 37 contain data. Column N and row 38 contain the totals. In Normal view, it's impossible to see the totals and the headings at the same time.

Figure 6-17: You can scroll to display the totals in column N or row 38, but you won't be able to see the headings.

On the CD You'll find the  2008 Projections.xlsx file in the Sample Files section of the companion CD.

It would be easier to navigate the worksheet in Figure 6-17 if it were split into panes. To do so, click the View tab on the Ribbon, and click Split; the window divides into both vertical and horizontal panes simultaneously, as shown in Figure 6-18. You can use the mouse to drag either split bar to where you need it. If you double-click either split bar icon (located in the scroll bars, as shown in Figure 6-17), you divide the window approximately in half. When you rest your pointer on a split bar, it changes to a double-headed arrow.

Figure 6-18: With the window split, you can scroll each pane independently.

Note 

Before clicking Window, Split or double-clicking one of the split bar icons, select a cell in the worksheet where you want the split to occur. This splits the worksheet immediately to the left or above the selected cell. If cell A1 is active, the split occurs in the center of the worksheet. In Figure 6-17, we selected cell B4 before choosing the Split command, which resulted in the split panes shown in Figure 6-18.

With the window split into four panes, as shown in Figure 6-18, four scroll bars are available (if not visible)-two for each direction. Now you can use the scroll bars to view columns A through N without losing sight of the product headings in column A. In addition, when you scroll vertically between rows 1 and 38, you'll always see the corresponding headings in row 3.

After a window is split, you can reposition the split bars by dragging. If you are ready to return your screen to its normal appearance, click the Split button again to remove all the split bars. You can also remove an individual split by double-clicking the split bar or by dragging the split bar to the top or right side of the window.

Freezing Panes

After you've split a window into panes, you can freeze the left panes, the top panes, or both panes by clicking the View tab on the Ribbon, clicking Freeze Panes, and selecting the corresponding option, as shown in Figure 6-19. When you do so, you lock the data in the frozen panes into place. As you can see in Figure 6-19, the pane divider lines have changed from thick, three-dimensional lines to thin lines.

Figure 6-19: Freezing panes locks the top and/or left panes of a split window.

Note 

You can split and freeze panes simultaneously at the selected cell by clicking Freeze Panes without first splitting the worksheet into panes. If you use this method, you will simultaneously unfreeze and remove the panes when you click Unfreeze Panes. (The command name changes when panes are frozen.)

Notice also that in Figure 6-18, the sheet tabs are invisible because the horizontal scroll bar for the lower-left pane is so small. After freezing the panes, as shown in Figure 6-19, the scroll bar returns to normal, and the sheet tabs reappear.

Note 

To open another worksheet in the workbook if the sheet tabs are not visible, press Ctrl+Page Up to open the previous worksheet or Ctrl+Page Down to open the next worksheet.

After you freeze panes, scrolling within each pane works differently. You cannot scroll the upper-left panes in any direction. You can only scroll the columns (right and left) in the upper-right pane and only the rows (up and down) in the lower-left pane. You can scroll the lower-right pane in either direction.

Inside Out-Make Frozen Panes Easier to See

Generally speaking, all the tasks you perform with panes work better when the windows are frozen. Unfortunately, it's harder to tell that the window is split when the panes are frozen because the thin frozen pane lines look just like cell borders. To make frozen panes easier to see, you can use a formatting clue you will always recognize. For example, select all the heading rows and columns, and fill them with a particular color.

Zooming Worksheets

As mentioned previously, you can use the Zoom control in the bottom-right corner of the screen or click the View tab on the Ribbon and use the two Zoom buttons to change the size of your worksheet display. Clicking a Zoom button displays a dialog box containing one enlargement option, three reduction options, and a Fit Selection option that determines the necessary reduction or enlargement needed to display the currently selected cells. Use the Custom box to specify any zoom percentage from 10 through 400 percent. The Zoom To Selection button enlarges or reduces the size of the worksheet to make all the selected cells visible on the screen. For example, clicking Zoom To Selection with a single cell selected zooms to the maximum 400 percent, centered on the selected cell (as much as possible) in an attempt to fill the screen with the selection.

Note 

The Zoom command affects all the selected worksheets; therefore, if you group several worksheets, Excel will display all of them at the selected Zoom percentage. For more about grouping worksheets, see "Editing Multiple Worksheets" on page 236.

For example, to view the entire worksheet shown in Figure 6-17, you can try different Zoom percentages until you get the results you want. Better still, select the entire active area of the worksheet, and click the Zoom To Selection button. Now the entire worksheet appears on the screen, as shown in Figure 6-20. Note that the Zoom percentage resulting from clicking Zoom To Selection is 85 percent, displayed next to the Zoom control at the bottom of the screen.

Figure 6-20: Click the Zoom To Selection button with the active area selected to view it all on the screen.

Of course, reading the numbers might be a problem at this size, but you can select other reduction or enlargement sizes for that purpose. While your worksheet is zoomed, you can still select cells, format them, and type formulas as you normally would. The Zoom option in effect when you save the worksheet is the displayed setting when you reopen the worksheet.

Note 

The wheel on a mouse ordinarily scrolls the worksheet. You can also use the wheel to zoom. Simply hold down the Ctrl key, and rotate the wheel. If you want, you can make zooming the default behavior of the wheel. To do so, click the Microsoft Office Button, click Excel Options, select the Advanced category, and select the Zoom On Roll With IntelliMouse check box in the Editing Options area.

Using Custom Views

Suppose you want your worksheet to have particular display and print settings for one purpose, such as editing, but different display and print settings for another purpose, such as an on-screen presentation. By clicking the Custom Views button on the View tab, you can assign names to specific view settings, which include column widths, row heights, display options, window size, position on the screen, pane settings, the cells that are selected at the time the view is created, and, optionally, the print and filter settings. You can then select your saved view settings whenever you need them, rather than manually changing the settings each time.

Note 

Before you modify your view settings for a particular purpose, you should save the current view as a custom view, named Normal. This provides you with an easy way to return to the regular, unmodified view. Otherwise, you would have to retrace all your steps to return all the view settings to normal.

In the Custom Views dialog box, the Views list is empty until you click Add to save a custom view. Figure 6-21 shows the Custom Views dialog box with two views added, as well as the Add View dialog box you used to add them.

Figure 6-21: Click Add to name the current view and print settings in the Custom Views dialog box.