6.1. Controlling Your View
So far, most of the sample worksheets in this book have included only a small amount of data. But as you expand your real-life data with dozens of columns, and hundreds or even thousands of rows, editing becomes much trickier. The most challenging problems are keeping track of your place 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.
Excel's zoom feature lets you control how much data you see in the window. When you reduce the zoom percentagesay, from 100 percent to 10 percent Excel 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 (enlarge) to 400 percent and zoom out (shrink) all the way down 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. So, for example, if you see 20 rows at 100 percent, you see 10 rows at 200 percent.
Note: Changing the zoom affects how your data appears in the Excel window, but it doesn't have any effect on how Excel prints or calculates your data.
Frequent trips to the Zoom dialog box are 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.
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.
The control lists a handful of percentages you can choose from, or you can just type in your own percentage. To tell Excel to adjust the zoom automatically so your highlighted cells fit perfectly into the whole window, you can select an area and then, on the Zoom control, choose Selection. (You can perform this same trick by highlighting some cells, opening the Zoom dialog box, and choosing "Fit selection.")
Tip: The Zoom control on the toolbar always displays the current zoom percentage.
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.
Here's how it works. First, select the range of cells you want to magnify; then, on the Zoom control, choose Selection to tell Excel to expand the range to fill the entire window (Figure 6-3). (You can also perform this trick by highlighting your cells and then opening the Zoom dialog box (View Zoom) and choosing "Fit selection.").
Figure 6-3. Top: To magnify a range of cells, first select them, as shown here. Then, on the Zoom control (indicated by cursor), choose Selection to have Excel expand the range to fill the entire window.
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 Intelli-Mouse (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 zoom in, and when you roll the wheel down, you zoom out.
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 aren't near each other. For example, if you want to focus on both row 1 and row 138 at the same time, zooming won't help. Instead, try splitting your Excel window into multiple panesseparate 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.
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:
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 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 screens. Similarly, if your first column holds identifying labels, you may 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. Section 184.108.40.206 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:
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:
Freezing columns works the same way:
Figure 6-6. Here, Excel has frozen both column A and row 1, so the order IDs and column headings always remain visible.
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 may 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. 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 by selecting Format Sheet Hide. See Section 5.1.2 for details.
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 at a glance which information youve restored.
Forgetting that you've hidden data is as easy as forgetting where you put your car 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, missing column or row headers (a jump from A to, say, O) tell you data's gone missing; so does an extra-thick column border.
Figure 6-7. This worksheet jumps directly from column A to column O, which tells you that Excel has hidden B through N. If you look carefully, you can spot another telltale sign: the border between the column A and O headers is slightly larger than usual. You can drag this border to resize the hidden column back into view.
Tip: To unhide all columns (or rows) in a worksheet, select the entire worksheet (by clicking the square in the top-left corner of the grid), and then select Format Column Unhide or Format Row Unhide (depending on whether you hid columns or rows).
Tip: Excel doesn't let you hide individual cells. However, Excel gurus have figured out a couple of workarounds. 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 Excel: The Missing Manual 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:
To create a custom view, follow these steps:
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, theyll 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.
If you goof and want to return to Excel's standard view of your worksheet, simply delete your custom view by selecting View Custom Views to display the Custom Views dialog box, choosing the custom view you want to get rid of, and clicking Delete.
Tip: For some examples of custom views in action, visit www.missingmanuals.com and download CustomViews.xls, a sample spreadsheet with a bunch 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. (For more on workbooks and worksheets, flip to Section 4.3.5.)
Fortunately, Excel provides a handy tool that lets you place several open workbooks inside one large Excel window, and then 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:
If you've opened multiple windows on the same workbook, you can select the "Windows of active workbook" option (located on the Arrange Windows dialog box) to tell Excel to ignore any other open workbooks.
Figure 6-9. Excel has arranged these spreadsheets horizontally. Book2.xls (as you can tell by the dark window and active cell border) 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. Instead, you must save custom workspaces as separate workspace files. (These files, which use the .xlw file extension, don't actually contain any of the individual spreadsheet file data; all they contain are details about what spreadsheet files you want to see and how you want to see them.) When you open a 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 deleting only the information about the customized window arrangement. Excel stores the individual spreadsheet files separately.
Workspaces have two minor quirks you should be aware of:
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.