In this post we highlight many of the common options of controlling how you view your data in Microsoft Excel. From dealing with multiple views on the same data sheet, freezing panes to make your headings always visible, to managing and working with mutiple sheets and workbooks.
Creating Multiple Views
In many cases, you might find it helpful to work with different sections of your worksheet at the same time. You might, for example, want to keep the labels in row 4 visible while you scroll down to look at information located in row 35. You do this by applying either split bars or freezing panes.
Applying Split Bars
When you apply split bars to a worksheet, Excel creates identical copies of the worksheet side by side. Split bars are illustrated in Figure 1‑3. If you apply either a horizontal or vertical split bar, you can scroll within one pane while the other pane remains stationary. If you apply both horizontal and vertical split bars, in which four panes are created, only two panes remain stationary when you scroll within one pane. For example, if you horizontally scroll in the upper right pane, you simultaneously scroll through the lower right pane while the two left panes remain stationary.
Although the Split command can be accessed from the Windows group on the View tab, you can also manipulate split bars with the mouse using the split boxes shown below.
You can move between the different panes by simply clicking the pane in which you want to work. Because each pane is a view of the same worksheet, a change in one pane means a change to the worksheet.
To apply horizontal and vertical split bars:
- Click the cell to the right of the location for a vertical split and below the location for a horizontal split.
- From the Windows group on the View tab, choose Split.
To apply a horizontal or vertical split bar:
- Position the mouse pointer over the horizontal or vertical split box.
- When the mouse pointer changes to a split pointer, drag the split box down or left to the desired location.
To remove a split bar:
- Double-click any part of the split bar that divides the panes.
Freezing Panes
Another way to divide your worksheet into panes is by freezing sections of the worksheet. Freezing panes is useful when you are working with large tables because you can hold horizontal and vertical labels stationary while you move through the data.
To freeze panes of data:
- Select the cell below and to the right of the location for the frozen panes.
- From the Window menu, choose Freeze Panes.
Note: If the cell you select is in column A or in row 1, choosing Freeze Panes will result in two panes instead of four.
To unfreeze panes of data:
- From the Window menu, choose Unfreeze Panes.
Note: When you freeze panes, the Freeze Panes option changes to Unfreeze Panes so that you can unlock frozen rows or columns.
Viewing and Arranging Multiple Worksheet Windows
Often, it is useful to view more than one worksheet at a time. You can arrange worksheets on your screen so that you can view them simultaneously. Once you have more than one copy of your worksheet window open, you can select different worksheets to view from each worksheet window, and then arrange the windows to best suit your needs. For example, you could display four worksheets from the same workbook at once.
Once you have arranged the worksheet windows, you can move between different windows simply by clicking the window in which you want to work. It takes one click to activate a window and another click to select anything in that window.
The Arrange Windows dialog box provides options to let you set up your data on the screen. These options are summarized in the table below:
| Option | Function |
| Tiled | Arranges windows so that each fills an equal portion of the work area. |
| Horizontal | Arranges windows one below the other. |
| Vertical | Arranges windows next to each other. |
| Cascade | Stacks the windows, offset vertically with the title bar of each window showing. |
| Windows of active workbook | Provides views of only the currently active workbook when other workbooks are open. |
Table: The Arrange Windows Dialog Box Options
To view and arrange multiple worksheet windows:
- Select the Book/sheet you’d like to view full-size. From the Window group on the View tab, choose New Window.
- This opens a copy in Read Only mode.
- Repeat steps 1 and 2 for each worksheet you want to view. Closing copies and returning as required
- In the Window group, choose Arrange All.
- In the Arrange Windows dialog box, in the Arrange area, select the desired option.
- If necessary, select the Windows of active workbook check box.
- Choose OK.
Navigating in Multiple Workbooks
Sometimes you need to transfer data from one workbook to another, perhaps as a way to consolidate information about one client or product. In order to do To do this efficiently, you must open and view multiple workbooks.
Opening Multiple Workbooks
You may open as many workbooks as you want, but having too many workbooks open at the same time can slow down your system or cause your computer to lock-up. Therefore, you should only open the workbooks you need and close those with which you have finished working.
To open multiple workbooks:
- Click on the File Button on your top left hand corner.
- In the Open dialog box, select the workbook you want to open.
- Choose Open.
- Repeat steps 1 through 3 until all needed workbooks are open.
Selecting and Viewing Multiple Workbooks
When you open a workbook, it appears on the screen in its default size, usually maximized. In most cases, this will cover any other open workbooks on the system. This doesn’t mean the other workbooks aren’t there. You just can’t see them. You can easily change your view of one workbook to another. You can also view more than one workbook on the screen, using the Arrange command on the Window group.
To select multiple workbooks:
- From the Window group, select the file name of the workbook you want to view.
To view multiple workbooks:
- From the Window group, choose Arrange.
- In the Arrange Window dialog box, in the Arrange area, select the desired option.
- If necessary, deselect the Windows of active workbook check box.
- Choose OK.
Creating and Opening a Workspace
You use a workspace when you need to save a configuration of open workbooks on your system. In other words, let’s say you’ve opened two or three workbooks, have arranged them satisfactorily, and then discover that you’re out of time. Instead of repeating all the arranging the next time you start Excel, you simply save the arrangement as a workspace. The workspace file you create provides instructions to Excel about arranging and opening workbooks; however, it does not contain the workbooks themselves. When you open the workspace file, the actual workbooks appear. Because the workspace file contains pointers to the workbook locations, it’s important not to move the workbooks that are part of the workspace into new folders, unless you move them from within the workspace itself (i.e., by using the Save As command).
To create a workspace:
- Arrange all open workbooks as desired.
- From the View tab, choose Save Workspace.
- In the Save Workspace dialog box, from the Save in drop-down list, select a folder in which to store the workspace.
- In the File name drop-down combo box, type a name for the workspace.
- Choose Save.
To open a workspace:
- On the File Button, click the Open button.
- In the Open dialog box, select the file name of the workspace.
- Choose Open.
Managing Multiple Worksheets and Workbooks
As you develop your workbooks, you may find that one worksheet isn’t sufficient for the complexities of your data. In fact, it will probably prove necessary at times to move and copy entire worksheets, and then pasting them into multiple workbooks. Similarly, moving and copying data between worksheets and workbooks is equally valuable in creating and maintaining your workbooks.
Moving and Copying Worksheets
Sometimes, you’ll want to change the order of the worksheets in a workbook. For instance, assume you created a sequence of worksheets named 1993, 1992, 2000, 2001, and 1999. At the time, it might have been convenient, but now it doesn’t make much sense. Luckily, Excel provides tools to make reorganizing the workbook easy. You can reorder worksheets by moving them. Making copies of worksheets is also useful for a number of reasons. For example, you might want to experiment with a table without affecting the original data. Using a duplicate worksheet makes this easy and safe. At another time, you might need to use the copy for creating multiple worksheets of similar structure. One way to move or copy worksheets is by dragging. When you drag the sheet tab, the mouse pointer with a small sheet of paper and a small, black triangle marks the location of the dragged worksheet. As an alternate method, you can use the Move or Copy dialog box.
To move a worksheet:
Mouse method
- Drag the sheet tab of the worksheet to its new location, releasing the mouse button when the black triangle is in the desired location.
Shortcut menu method
- Right-click the tab of the worksheet you want to move.
- From the sheet tab shortcut menu, choose Move or Copy.
- In the Move or Copy dialog box, from the To book drop-down list, select the desired workbook.
- In the Before sheet list box, select the worksheet before which the moved sheet will appear.
- Choose OK.
To copy a worksheet:
Mouse method
- Press and hold Ctrl
- Drag the sheet tab of the worksheet to its new location, releasing the mouse button when the black triangle is in the desired location.
- Release Ctrl
Shortcut menu method
- Right-click the tab of the worksheet you want to copy.
- From the sheet tab shortcut menu, choose Move or Copy.
- In the Move or Copy dialog box, from the To book drop-down list, select the desired workbook.
- In the Before sheet list box, select the worksheet before which the copied sheet will appear.
- Select the Create a copy check box.
- Choose OK.
Selecting Multiple Worksheets
So far, you’ve selected only one worksheet at a time. This forces you to perform tasks such as inserting and deleting worksheets over and over. It’s often faster to insert or delete several worksheets at one time. The same applies to moving and copying worksheets. That’s why knowing how to select multiple worksheets is so important.
To select multiple worksheets:
- Select the first worksheet.
- Press and hold Ctrl
- Select additional worksheets as desired
To select all worksheets:
- Right-click any sheet tab in the workbook.
- From the sheet tab shortcut menu, choose Select All Sheets.
To deselect all worksheets:
- Right-click any sheet tab in the workbook.
- From the sheet tab shortcut menu, choose Ungroup Sheets.
Find out more on our one day Microsoft Excel training courses delivered regularly in Glasgow and Edinburgh city centres:
Find out about other Microsoft Office applications training here.