Office XP Tips: Manage Large Spreadsheets

Excel 2002 offers a great new feature that helps you keep an eye on otherwise out-of-view high jinks: the Watch Window toolbar. The Watch Window displays the value and formula in any cell (or cells) you specify. The toolbar is great for keeping an eye on a cell that contains an important value that changes as you input data.

For instance, suppose your fundraising worksheet includes a formula that generates a running total of the money your organization needs to raise to build a new baseball diamond. As you raise money, you fill in the data in the appropriate row, which affects the grand total. You want to be able to keep a close eye on that grand total, but it's in cell J35, which is out of view most of the time. Instead of scrolling to cell J35 every time you want to check on the total, you can just open the Watch Window toolbar and specify that cell as the one to watch.

I Spy

To open the Watch Window toolbar and specify a cell to monitor all in one fell swoop, right-click the cell and choose Add Watch from the pop-up menu. Excel opens the toolbar and displays important information about the cell, including its workbook, worksheet, cell name (if you've assigned one), value, and formula (if it contains one).

As you edit the cell--or other cells that affect it--the Watch Window toolbar immediately reflects the cell's new value. To watch another cell, click Add Watch to open the Add Watch dialog box, click on any worksheet cell to select it (outside the dialog box), and then click Add. To remove a cell from the Watch Window toolbar, select it and click Delete Watch. You can open or close the Watch Window toolbar at any time by right-clicking any toolbar and choosing Watch Window from the pop-up menu. If you prefer the menu route, select Tools, Formula Auditing, Show Watch Window (or Hide Watch Window). And here's yet another way: Select View, Toolbars, Watch Window. To change the on-screen position of the Watch Window, click its title bar and drag it to a new location. To change its size, click any side or corner and drag in any direction.

Fine-Tuning the Display

Although the Watch Window toolbar works well as a floating toolbar, you can anchor it to any side of your worksheet window if you prefer to work with it that way. To make it an Excel window, click its title bar and drag it to the right, left, top, or bottom of the Excel window until it anchors to the worksheet. Once it's there, you can resize it by dragging one of its borders. If you want the floating toolbar back, just double-click on the Watch Window title bar.

Whether you're using an anchored or a floating toolbar, you can easily adjust the size of the columns in the Watch Window by clicking its cell dividers and dragging right or left. This allows you to fine-tune the toolbar's display to minimize the on-screen real estate it takes up. For instance, if you want to monitor the value in cell J35, you could anchor the Watch Window toolbar to the right side of the screen, click and drag to reduce the size of the toolbar so that only two columns are visible, then scroll into view the "Cell" and "Value" columns, adjusting their width as necessary. This allows you to keep an eye on the value in cell J35 without taking up much valuable worksheet space at all.

A Formula-Grabbing Shortcut

If your worksheet has a lot of formulas and you want to open the Watch Window toolbar, you might as well go hog wild and spy on all the cells with formulas. Instead of hunting for all the cells that contain formulas and adding them to the Watch Window toolbar individually, here's a shortcut: Choose Edit, Go To, and click the Special button in the Go To dialog box. In the Go To Special dialog box, click Formulas and click OK. Excel returns you to your spreadsheet with all of the formula-containing cells selected. Now open the Watch Window toolbar and click Add Watch. The selected cells will already be specified in the Add Watch dialog box, so just click Add to add them all to the Watch Window toolbar.

Two Bonus Tips

Here are two final Watch Window toolbar tips. Want to sort the information in the Watch Window toolbar? Click any column heading to perform an ascending sort according to that column. This can be very handy if you want to compare data--say, regional sales totals. Finally, you can jump immediately to any cell in your worksheet by double-clicking its entry in the Watch Window toolbar.