6.1. Controlling Your ViewSo far, most of the worksheets in this book have included only a small amount of data. But as you cram your worksheets 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. ZoomingExcel'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 most easily adjust the zoom percent by using the zoom slider in the bottom-right part of the status bar. The zoom slide also displays the current zoom percentage. But if you want to specify the exact zoom percentage by hand (say, 142 percent), then you can choose View Zoom Zoom. A Zoom dialog box appears (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, then 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. You can also zoom in on a range of cells. When 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 a bird's eye view of all your data, and you want to swoop in on a particular section, Excel lets you expand a portion to fit your screen. To zoom in on a group of cells, first select some cells (Figure 6-2), and then choose View Zoom Zoom to Selection (Figure 6-3). (You can perform this same trick by highlighting some cells, opening the Zoom dialog box, and then choosing "Fit selection.") Make sure you select a large section of the worksheetif you select a small group, youll end up with a truly jumbo- sized zoom.
Tip: If you're using a mouse with a scroll wheel, you can zoom with the wheel. Just hold down the Ctrl key, and roll the scroll wheel up (to zoom in) or down (to zoom out).
6.1.2. Viewing Distant Parts of a Spreadsheet at OnceZooming 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, then 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. When 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). When 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, then 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:
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. Tip: 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.
Note: If you want to remove your panes, then just drag the splitter bars back to the edges of the window, or double-click it. You can also create panes by using the ribbon command View Window Split. When you do, Excel carves the window into four equal panes. You can change the pane sizes as described above, or use View Window Split again to return to normal. Note: If you use Excel's worksheet navigation toolslike the Go To and Find commands all 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 RowsExcel has another neat trick up its sleeve to help you manage large worksheets: freezing . Freezing is a simpler way to make sure 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 columneven when you've scrolled down several screenfuls. 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. Tip: Excel lets you print out worksheets with a particular row or column fixed in place. Section 6.2.5.3 tells you how. You can freeze rows at the top of your worksheet, or columns at the left of your worksheet, but Excel does limit your freezing options 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, just follow these steps:
Freezing columns works the same way:
Tip: If you're freezing just the first row or the leftmost column, then there's no need to go through this whole process. Instead, you can use the handy View Freeze Panes Freeze Top Row or View Freeze Panes Freeze First Column. | |||||||||||||||||
|
In some cases your problem isn't that you need to keep data visible, but that you need to hide it. For example, say you 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. 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.
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 then choose Hide. Or, put your cursor in any row in that column, and then select Home Cells Format Hide & Unhide Hide Columns.
To hide a row, right-click the row header (the number button at the left of the row), and then choose Hide. Or, put your cursor in any column in that row, and then select Home Cells Format Hide & Unhide Hide Rows.
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 Home Cells Format Hide & Unhide Unhide Columns (or Unhide Rows). Or just right-click the selection, and then choose Unhide. Either way, Excel makes the missing columns or rows visible and then highlights them so you can see which information youve restored.
Figure 6-7. This worksheet jumps directly from column A to column O, which tells you that B through N are hidden. |
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.
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, then the custom view returns you to the active cell in a hurry.)
The currently selected cell (or cells).
Column widths and row heights, including hidden columns and rows.
Frozen panes (Section 6.1.3).
View settings, like the zoom percentage, which you set using the ribbon's View tab.
Print settings, like the page margins.
Filter settings, which affect what information Excel shows in a data list (see Chapter 8).
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 Workbook Views 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 is empty.
Click the Add button .
The Add View dialog box appears.
Type in a name for your custom view .
You can use any name, but consider something that'll 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").
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, then 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 Workbook Views Custom Views to return to the Custom Views dialog box (Figure 6-8), and 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.
|