Office 2007: Keep Excel Data Visible At All Times

When you’re working on a large worksheet it’s easy to lose track of where important details are. As soon as the worksheet grows beyond screen size, valuable information can disappear out of view.

There are, however, ways that you can configure Excel so that the information that you need to refer to frequently is visible, regardless of where you happen to be.

» Fix Your Headings

One problem you’ll encounter with any large worksheet that has column and row headings is that these disappear as soon as you move away from the top left corner of the sheet. This makes it hard to understand the information you’re looking at. Displaying the row and column headings permanently on the screen will solve this.

To do this, click in the cell immediately below the heading row and immediately to the right of the column containing the row headings. Click the View tab on the Excel 2007 Ribbon and click Freeze Panes > Freeze Panes.

Two lines, one horizontal and one vertical, appear on the worksheet dividing the headings from the data. Scroll down or across your sheet and you’ll see that regardless of where you go the selected columns and rows stay stationary making it easier to identify the data in front of you.

Frozen panes are saved with the worksheet so you need do this only once. To unfreeze the panes, choose View > Freeze Panes > Unfreeze Panes.

» Split the Worksheet

Another option for displaying different pieces of one worksheet on the screen is the Split tool. Find it in the View area of the Ribbon. To begin, click where you want the screen to split and click Split.

If you were in the first column of the worksheet, the split appears horizontally dividing the worksheet in two. If you were in the first row of the worksheet, it will now be split in two vertically. If you were in the middle of the worksheet, it’s now split into four independent screens.

You can scroll each screen independently of the others to control what you see in each area but at the same time, they’re synced so the data always lines up correctly.

So, for example, if you have a series of dates in column A you can split the screen and show some early dates in one screen and much later dates in another — essentially hiding from view the dates between the two.

The split lines can be moved so you can drag them around to manage where the workbook is split and how much of the worksheet shows in each area. To undo the arrangement click the Split button again to remove the lines.

» Move a Chart, Excel 2007 Style

If what you’re most interested in seeing is figures in a chart you might find it hard to see the chart when it is located on the worksheet — you’ll have to scroll to find it. If the chart were on its own sheet, a single click would be all that would be required to view it.

In Excel 2003, it was easy to create a chart on a separate worksheet because that was an option in the Chart Wizard. In Excel 2007 there is no chart wizard as such so it’s not an option.

To move a chart to its own sheet, click the chart to select it, right click and choose Move Chart. When the Move Chart dialog appears, select New Sheet: Chart 1 and click Ok.

The chart will appear on its own worksheet and you can switch to view the data or the chart by clicking the appropriate sheet tab.

Must Read

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends, and analysis.