Office 2007: Keep Excel Data Visible At All Times

by Helen Bradley

With a little imagination and skill you can work on one part of a spreadsheet and see data in other areas at the same time.

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.

» Watch a Value

If you need to keep your eye on a particular figure in your worksheet, set up a watch window to monitor it. Choose Formulas > Watch Window from the Ribbon. This creates a watch window that can be moved so that it floats over the top of the worksheet or remains anchored at the top.

Click the Add Watch button, select the cell whose contents you want to watch, and click Add. The cell details will appear in the watch window including its location, contents and formula.

Regardless of where you are in the workbook, the watch window sits on the screen displaying the cell details.

» DIY Watch Window

You can also create your own version of the watch window using a textbox. From the Insert tab on the Ribbon click the TextBox icon and draw a textbox on the screen. Click in the text box, and on the formula bar type the cell reference of the cell to monitor — e.g. type =A1 to monitor cell A1.

You must type the value in the formula bar, not directly into the textbox for this to work. Now you'll see the value from cell A1 in the textbox.

If the cell is on another sheet — for example, if you want to see A1 in sheet1 — type ='sheet1'!A1 in the formula bar with the textbox selected. The display is live so it will always show the most current value in that cell.

» Take a Picture

To see more than one cell, use the Excel Camera tool and take a photo of the cells to watch. The camera is not on Excel's new ribbon so you must add it to your Quick Access Toolbar by selecting the Office button, clicking Excel Options > Customize and from the Choose commands from: dropdown list choose Commands Not In The Ribbon.

Locate and select the Camera icon and click Add to add it to your Quick Access Toolbar. Click OK when you are done.

To take your picture, display the sheet containing the data to monitor, select the data with your mouse, and click the Camera icon. Immediately scroll to the sheet where you want to see this data and click somewhere on it. This pastes a snapshot of the cells you had selected as a picture floating over the worksheet.

You can move this snapshot around and right click and format it, for example, applying a fill to it so you can see it easily. You can also drag its sizing handles to adjust its size if desired. The data in the snapshot is linked to the original data so if the original data changes, the picture version will change too.

You can take a snapshot of a chart too. However, this won't work if you select the chart, so you have to select the cells around the edges and underneath the chart and take a snapshot of them. You can delete a snapshot at any time when you've finished with it, and deleting it will have no effect on the original data in the worksheet.

As you can see, with a little imagination and skill you can have your cake and eat it, too — you can work on one worksheet and see data on another sheet at the same time.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.

Adapted from winplanet.com.

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!

This article was originally published on Thursday Jul 3rd 2008
Mobile Site | Full Site