Section 6.1. Controlling Your View | Excel 2010: The Missing Manual

6.1. Controlling Your View

So far, most of the worksheets in this book have included only a small amount of data. But as you expand your data with dozens of columns , and hundreds or even thousands of rows, editing becomes much trickier. The most challenging problems are keeping track of where you are in an ocean of information and making sure the data you want stays visible. Double that if you have multiple large worksheets in one workbook.

The following sections introduce the basic tools you can use to view your data, along with a few tips for managing large worksheets.

6.1.1. Zooming

Excel's zoom feature lets you control how much data you'll see in the window. When you reduce the zoom percentagesay from 100 percent to 10 percentExcel shrinks your individual cells , letting you see more of them at once, which also makes it harder to read the data. Very small zoom percentages are ideal for looking at the overall layout of a worksheet. When you increase the zoom percentagesay from 100 percent to 200 percentExcel magnifies your cells, letting you see more detail but fewer cells. Larger zoom percentages are good for editing.

Note: Excel lets you zoom in to 400 percent and out all the way to 10 percent.

You can adjust the zoom for an open worksheet by selecting View Zoom. A Zoom dialog box appears (shown in Figure 6-1) that lets you select a preset zoom percentage or type in your own percentage in the Custom box.

Figure 6-1. The standard zoom setting is 100 percent, although other factors like the size of the font you're using and the size and resolution of your computer screen help determine how many cells fit into Excel's window. As a rule of thumb, every time you double the zoom, Excel cuts in half the number of rows you can see. Thus, if you can see 20 rows at 100 percent, you'll see 10 rows at 200 percent.

Note: Changing the zoom affects how your data appears in the Excel window, but it won't have any effect on how your data is printed or calculated.

Frequent trips to the Zoom dialog box can be a hassle. It's faster to use the Zoom control on the Standard toolbar, which looks and behaves like a little menu, as shown in Figure 6-2. The control lists a handful of percentages you can choose from, or you can just type in your own percentage. You can also select an area and then, on the Zoom control, choose Selection to have Excel adjust the zoom automatically so your highlighted cells fit perfectly into the whole window. (You can perform this same trick by highlighting some cells, opening the Zoom dialog box, and choosing "Fit selection.")

Note: The Zoom control on the toolbar always displays the current zoom percentage.

Figure 6-2. If the Zoom control doesn't show up in your Standard toolbar, click the arrow at the far right of the toolbar, and select Show Buttons on Two Rows to have the Standard toolbar appear on top, with the Zoom control at the far right end.

You can also use the zooming feature to home in on a range of cells. If your data extends beyond the edges of your monitor, this handy option lets you shrink a portion to fit your screen. Conversely, if you've zoomed out to get the bird's eye view of all your data, and you want to swoop in on a particular section, this feature also lets you expand a portion to fit your screen. Figure 6-3 shows you how.

Figure 6-3. Top : To magnify a range of cells, select them, as shown here. Then, on the Zoom control, choose Selection to have Excel expand the range to fill the entire window, as shown below. You can also perform this trick by highlighting your cells and then opening the Zoom dialog box (View Zoom) and choosing "Fit selection."
Bottom : The toolbar shows that Excel automatically zoomed your data to 118 percent. You can use this same procedure when your data creeps beyond the edges of your monitor. Select all the cells you want to see, and then choose Selection to shrink them into the window.

6.1.2. Viewing Distant Parts of a Spreadsheet at Once

Zooming is an excellent way to survey a large expanse of data or focus on just the important cells, but it won't help if you want to simultaneously view cells that are not near each other. For example, if you want to focus on both row 1 and row 138at the same time, zooming won't help. Instead, try splitting your Excel window into multiple panes separate frames that each provide a different view of the same worksheet. You can split a worksheet into two or four panes, depending on how many different parts you want to see at once. When you split a worksheet, each pane contains an identical replica of the entire worksheet. If you make a change to the worksheet in one pane, Excel automatically applies the same change in the other panes. The beauty of panes is that you can look at different parts of the same worksheet at once.

Tip: If you're using a mouse with a scroll wheel, you can zoom with the wheel. Excel comes with this feature turned off. To turn it on, select Tools

Options, and then click the General tab. Turn on "Zoom on roll with IntelliMouse" (you can choose this option no matter what kind of scroll mouse you're using), and then click OK. Now, when you roll the wheel up, you'll zoom in, and when you roll the wheel down, you'll zoom out.

Gem In the Rough Filling the Screen with Cells

If you really want to see the maximum number of cells at once, Excel provides a little known feature that strips away the toolbars and other extraneous screen elements, making more room for cells. When you switch into this full screen mode, Excel removes all toolbars and the window border, though it does keep the menu bar at the top of the screen, as shown here. To make the switch, select View Full Screen. When Excel switches to Full Screen mode, it automatically pops up a floating toolbar that holds only a Close Full Screen button. If the toolbar is in your way, you can close it and return to the normal view by choosing View Full Screen.

You can take this process a step further by hiding the Windows taskbar, which is particularly useful if you have a small screen. To have Windows automatically roll the taskbar off the bottom of the screen when you're not using it, click the Start button, then choose Control Panel Taskbar and Start Menu and turn on "Auto-hide the taskbar." To bring the taskbar back, just mouse over the bottom of the screen.

You can split a window horizontally or vertically (or both). If you want to compare different rows in the same worksheet, use a horizontal split. To compare different columns in the same worksheet, use a vertical split. And if you want to be completely crazy and see four different parts of your worksheet at once, you can use a horizontal and a vertical splitbut that's usually too confusing to be much help.

Excel gives you two ways to split the windows. Here's the easy way:

Find the splitter controls on the right side of the screen, as shown in Figure 6-4.

If you don't see the scroll bars or splitter controls, make sure your worksheet window is maximized (click the box in the upper-right corner of the worksheet itself).

Figure 6-4. Every Excel window contains both horizontal and vertical splitter controls.
Drag either control to split the window into two panes. As you drag, Excel displays a gray bar showing where it will divide the window. Release the splitter control when you're happy with the layout. (At this point, you don't need to worry about whether you can actually view the data you want to compare; you're simply splitting up the window.)

If you want to split the window into an upper and lower portion, drag the vertical control down to the location where you want to split the window.

If you want to split the window into a left and right portion, drag the horizontal control left to the location where you want to split the window.

Note: If for any reason you do want to split the window into four panes, use both controls. The order you follow is not important.
If you don't like the layout you've created, simply move the splitter bars by dragging them just as you did before.

Within each pane, scroll to the cells you want to see.

For example, if you have a 100-row table that you split horizontally in order to compare the top five rows and the bottom five, scroll to the top of the upper pane, then scroll to the bottom of the lower pane. (The two panes are replicas of each other; Excel is just showing you different parts of the same worksheet.)

Tip: To move from one pane to the next , click with the mouse, or press F6. To jump backward, press Shift+F6.

Using the scroll bars in panes can take some getting used to. When the window is split in two panes, Excel synchronizes scrolling between both panes in one direction . For example, if you split the window into top and bottom halves , Excel gives you just one horizontal scroll bar (at the bottom of the screen), which controls both panes (Figure 6-5). Thus, when you scroll to the left or right, Excel moves both panes horizontally. On the other hand, Excel gives you separate vertical scroll bars for each pane, letting you independently move up and down within each pane.

Figure 6-5. Here you can see the data in row 1 and 710 at the same time. As you move from column to column, both panes move in synch, letting you see, for instance, the phone number information in both panes at once. (You can scroll up or down separately in each pane.)

Note: If you want the data in one panefor example, column titlesto remain in place, you can freeze that pane. The next section tells you how.

The reverse is true with a vertical split; in this case, you get one vertical scroll bar and two horizontal bars, and Excel synchronizes both panes when you move up or down. With four panes, life gets a little more complicated. In this case, when you scroll left or right, the frame that's just above or just below the current frame moves, too. When you scroll up or down, the frame that's to the left or to the right moves with you. Try it out.

Tip: If you want to remove your panes, just drag the splitter bars back to the edges of the window, or choose Window

Remove Split.

Split. When you do, Excel will carve the window into four equal panes. You can change the pane sizes as described above, or use Window

Remove Split to return to normal.

Note: If you use Excel's worksheet navigation toolslike the Go To and Find commandsall your panes move to the newly found spot. For example, if you use the Find command in one pane to scroll to a new cell, the other panes will display the same cell .

6.1.3. Freezing Columns or Rows

Excel has another neat trick up its sleeve to help you manage large worksheets: freezing . Freezing is a simpler way to make sure that a specific set of rows or columns remains visible at all times. When you freeze data, it remains fixed in place in the Excel window, even as you move to another location in the worksheet in a different pane. For example, say you want to keep visible the first row that contains column titles. When you freeze that row, you can always tell what's in each column even when you've scrolled down several screenfuls. Similarly, if your first column holds identifying labels, you might want to freeze it so that when you scroll off to the right, you don't lose track of what you're looking at.

Note: Excel lets you print out worksheets with a particular row or column fixed in place. Page Section 6.2.2.4 tells you how.

You can freeze rows at the top of your worksheet, or columns at the left of your worksheet, but Excel restricts you in a few ways:

You can freeze rows or columns only in groups. That means you can't freeze column A and C without freezing column B. (You can, of course, freeze just one row or column.)
You must freeze columns starting from column A on the left side of the worksheet. When freezing rows, you must always start from row 1. Thus, you can't start freezing columns at, say, G, or rows at, say, 13. However, if the row you want to hold is, for example, the third row, you can freeze it in place at the top of the worksheetyou just won't be able to see the rows above it. Likewise, if you freeze the fifth column, Excel will hide the first four.

Note: As far as Excel is concerned , frozen rows and columns are a variation on panes (described earlier). When you freeze data, Excel creates a vertical pane for columns or a horizontal pane for rows. It then fixes that pane so you can't scroll through it.

To freeze a row or set of rows at the top of your worksheet, follow these steps:

Make sure the row or rows you want to freeze are visible and at the top of your worksheet.

For example, if you want to freeze rows 2 and 3 in place, make sure they're visible at the top of your worksheet. Remember, rows are frozen starting at row 1. That means that if you scroll down so that row 1 isn't visible, and you freeze row 2 and row 3 at the top of your worksheet, Excel also freezes row 1and keeps it hidden so you can't scroll up to see it.
Move to the first row you want unfrozen , then move left to column A.

At this point, you're getting into position so that Excel knows where to create the freeze.
Select Window Freeze Panes.

Excel splits the worksheet, but instead of displaying a gray bar (as it does when you create panes), it uses a solid black line to divide the frozen rows from the rest of the worksheet. As you scroll down the worksheet, the frozen rows remain in place.

To unfreeze the rows, just select Window Unfreeze Panes.

Freezing columns works the same way:

Make sure the column or columns you want to freeze are visible and at the left of your worksheet.

For example, if you want to freeze columns B and C in place, make sure they're visible at the edge of your worksheet. Remember, columns are frozen starting at column A. That means that if you scroll over so that column A isn't visible, and you freeze columns B and C on the left side of your worksheet, Excel also freezes column Aand keeps it hidden so you can't scroll over to see it.
Move to the first column you want unfrozen , then move up to row 1.

At this point, you're getting into position so that Excel knows where to create the freeze.

Select Window Freeze Panes.

Excel splits the worksheet, but instead of displaying a gray bar (as it does when you create panes), Excel uses a solid black line to divide the frozen columns from the rest of the worksheet. As you scroll across the worksheet, the frozen columns remain in place.

To unfreeze the columns, select Window Unfreeze Panes.

Tip: You can also create a horizontal or vertical pane by using one of the splitter bars, and then freezing that pane. Just drag the splitter bar to the appropriate position, and select Window

Freeze Panes.

Figure 6-6. Here, both column A and row 1 are frozen, and thus always remain visible. The easiest way to create these frozen regions is to scroll to the top of the worksheet, position the active cell at B2, and choose Window Freeze Panes. Excel then automatically freezes the rows above and the columns to the left in separate panes.

6.1.4. Hiding Data

In some cases your problem isn't that you need to keep data visible, but that you need to hide it. For example, you might have a column of numbers that you need only for a calculation but don't want to see when you edit or print the sheet. Excel provides the perfect solution: hiding rows and columns. Hiding doesn't delete information, it just temporarily tucks it out of view, and you can restore hidden information any time you need it.

Technically, hiding a row or column is just a special type of resizing (Figure 1-4). When you instruct Excel to hide a column, it simply shrinks the column down to a width of 0. Similarly, when you hide a row, Excel compresses the row height.

Note: You can also hide an entire worksheet of data. See Chapter 5 for details.

You can hide data a few ways:

To hide a column, right-click the column header (the letter button on the top of the column) and choose Hide. Or, put your cursor in any row in that column and select Format Column Hide.
To hide a row, right-click the row header (the number button at the left of the row) and choose Hide. Or, put your cursor in any column in that row and select Format Row Hide.
To hide multiple rows or columns, just select all the ones you want to disappear before choosing Hide.

To unhide a column or row, select the range that includes the hidden cells. For example, if you hid column B, select columns A and C by dragging over the numeric row headers. Then choose Format Column [or Row] Unhide, or just right click the selection and choose Unhide, to make a column or row visible. Excel then highlights the column or row so you can see which information you've restored.

Forgetting that you've hidden data is as easy as forgetting where you put your keys. While Excel doesn't include a hand-clapper to help you locate your cells, it does offer a few clues, as shown in Figure 6-7.

Tip: To unhide all columns (or rows) in a worksheet, select the entire worksheet (by clicking the square in the top right corner of the grid), and then select Format

Column

Unhide or Format

Column

Unhide.

Figure 6-7. This worksheet jumps directly from column A to column O, which tells you that B through N are hidden. If you look carefully , you can spot another telltale sign: the border between the column A and O headers is slightly larger than usual. If you're particularly nimble -fingered, you can drag this border to resize the hidden column back into view.

Tip: Excel doesn't let you hide individual cells. However, there are workarounds that Excel gurus use. The first one is to format the cell so that the text is white (because white lettering on a white background is invisible). Another solution is to format the cell with the custom number format ;;; (which doesn't show anything for positive, negative, or text values; see Section 4.1.4 for more on custom formatting). If you use either of these tricks, you can still see the cell content by moving to the cell and looking in the Formula bar.

6.1.5. Saving View Settings

If you regularly tweak things like the zoom, visible columns, and the number of panes, you can easily spend more time adjusting your worksheet than editing it. Fortunately, Excel lets you save your view settings with custom views . Custom views let you save a combination of view settings in a workbook. You can store as many custom views as you want. When you want to use a particular view you've created, simply select it from a list and Excel applies your settings.

Custom views are particularly useful when you frequently switch views for different tasks , like editing and printing. For example, if you like to edit with several panes open and all your data visible, but you like to print your data in one pane with some columns hidden, custom views let you quickly switch between the two layouts.

Note: You can't save a custom view for one worksheet and apply it to another.

Custom views can save the following settings:

The location of the active cell. (In other words, your position in the worksheet. For example, if you've scrolled to the bottom of a 65,000-row spreadsheet, the custom view returns you to the active cell in a hurry.)
The current cell selection.
Column widths and row heights, including hidden columns and rows.
Frozen panes.
View settings in the View tab of the Options dialog box. (Choose Tools Options to configure these.)
Print settings (Section 6.2.2), like the page margins.
Filter settings, which affect what information is shown in a data list (in Chapter 13).

To create a custom view, follow these steps:

Adjust an open worksheet for your viewing pleasure .

Set the zoom, hide or freeze columns and rows, and move to the place in the worksheet where you want to edit.
Choose View Custom View.

The Custom Views dialog box appears, showing you a list of all the views defined for this workbook. If you haven't created any yet, this list will be empty.
Click the Add button.

The Add View dialog box appears.
Type in a name for your custom view.

Your name can be anything, but consider something that will remind you of your view settings (like "50 percent Zoom"), or the task that this view is designed for (like "All Data at a Glance"). A poor choice is one that won't mean anything to you later ("View One" or "Zoom with a View") or something obscure like "'57 Chevy."

The Add View dialog box also gives you the chance to specify print settings or hidden rows and columns that Excel shouldn't save as part of the view. Turn off the appropriate checkboxes if you don't want to retain this information. Say you hide column A, but you clear the "Hidden rows, columns, and filter settings" checkbox because you don't want to save this as part of the view. The next time you restore the view, Excel won't make any changes to the visibility of column A. If it's hidden, it stays hidden; if it's visible, it stays visible. On the other hand, if you want column A to always be hidden when you apply your new custom view, keep the "Hidden rows, columns, and filter settings" checkbox turned on when you save it.

After you've typed your view name and dealt with the inclusion settings, click OK to create your new view. Excel adds your view to the list.
Click Close.

You're now ready to use your shiny new view or add another (readjust your settings and follow this procedure again).

Applying your views is a snap. Simply select View Custom Views to return to the Custom Views dialog box (Figure 6-8), then select your view from the list and click Show. Because Excel stores views with the workbook, they'll always be available when you open the file, even if you take that file to another computer.

Figure 6-8. You can use this dialog box to show or delete existing views or to create new ones (click Add, then follow the procedure from step 4 above).

Tip: For some examples of custom views in action, visit www.missingmanuals.com and download CustomViews.xls, a sample spreadsheet with a litany of custom views already set up.

6.1.6. Viewing Multiple Workbooks at Once

In its usual state, Excel lets you view only one open workbook file at a time. If you want to compare two or more workbooks, you have to switch between them using the Windows taskbar. But this action is a pain, especially if you want to compare two worksheets side-by-side.

But Excel does provide a handy tool that lets you place several open workbooks inside one large Excel window, and save your arrangement. This setup is called a custom workspace . With custom workspaces, you can arrange all the workbooks you need for a particular task the way you like them, and then save that arrangement of windows in a special workspace file. Then when it's time to get back to work on your project, you simply open the workspace file, and Excel restores all the windows exactly the way you left them.

Note: You can use a custom workspace to work on different parts of a single workbook at once. However, custom workspaces really come in handy if you need to work on multiples files simultaneously.

Before you can save your workspace, you must first create it, by following these steps:

Open all the spreadsheet files you want to make part of your workspace. Close all other Excel files.

Your files initially appear as usual: the active file in the main Excel window and the other files listed separately under the Window menu.

Should you want different worksheets from the same workbook to be part of your workspace, you must open duplicate versions of the workbook. To do this, go to the workbook and select Window New Window. Excel opens a second (or third, or fourth...) window that shows the same workbook. Don't worry thoughany change you make in one window automatically appears in the others, because there's still just one open workbook. The only way you can tell that you have more than one window open for the same workbook is too look at the title bar of the window, which adds a colon and a number. For example, when you open a second view on MyBeanieBabies.xls, you'll see the window title MyBeanieBabies.xls:2.
Select Window Arrange from the menu.

The Arrange Windows dialog box appears.
Choose an Arrange option and click OK.
- Horizontal , as shown in Figure 6-9, stacks the windows from top to bottom. Excel arranges the windows one above the other, each occupying the full width of the Excel window (similar to when you split a worksheet with the horizontal splitter bar).
- Vertical instructs Excel to tile the windows from left to right.
- Tiled arranges the windows in a grid pattern whose composition changes depending on the number of files you're arranging.
- Cascade layers the windows on top of each other with just a smidge of each window showing.

If you've opened multiple windows on the same workbook, you can select the "Windows of active workbook" option to tell Excel to ignore any other open workbooks.

Figure 6-9. These spreadsheets have been arranged horizontally. Book2.xls is the active window. To return to the standard, one-file view, just double-click the blue title bar on any window, or click its Maximize button at the right.

If you'd like to save a particular set of arranged windows, Excel lets you do so by creating a custom workspace . However, unlike a custom view, you can't save custom workspaces inside an individual spreadsheet file. Therefore, you must save custom workspaces as separate workspace files , which specify those files you've included and the position of their windows. (These files, which use the .xlw file extension, don't actually contain any of the individual spreadsheet file data.) When you open the custom workspace file, Excel automatically loads all the files you were using and returns them to their original locations.

Once you've completed the above sequence of steps to create your workspace, you can save it any time. Just select File Save Workspace and choose a file name. You open a workspace file in the same way you open a spreadsheet: by choosing File Open from the Excel menu, or by double-clicking the file on your desktop or in Windows Explorer.

Note: If you ever decide to delete a workspace file, bear in mind that you're only deleting the information about the customized window arrangement. The individual Excel files themselves are stored separately.

Workspaces have two minor quirks you should be aware of:

The workspace file stores the location of the Excel files it uses. If you move one of these files somewhere else, the workspace won't be able to find it and load it.
If you open a workspace file and then change the window arrangement or open new worksheets, Excel won't prompt you to save the new workspace settings. Instead, you need to explicitly choose File Save Workspace from the menu again.

Tip: You can use custom workspaces as a shortcut to open multiple files you want to work on at the same timeeven if you don't want to use Excel's window-arranging features. To do so, just open all the files into separate windows, and then save the workspace.