MrExcel.com - Your One Stop for Excel Tips & Solutions

Monitor Distant Cells

June 09, 2022 - by

Monitor Distant Cells

Problem: I have a massively large spreadsheet. I’m working on calculations in the top of the spreadsheet but need to monitor results in several other worksheets. It is a pain to travel back and forth to monitor those cells. Is there another way to do this?

Strategy: The Watch Window is a favorite tool of VBA programmers, and Microsoft added it to the regular Excel interface. Here’s how you use it:

  • 1. Select Formulas, Watch Window. The Watch Window, a floating dialog box that you can move around your screen, will appear.

  • 2. Click Add Watch.

  • 3. Using the Add Watch dialog, navigate to and touch the cell that you want to watch. Alternatively, you can first navigate to the cell, click Add Watch, and click Add.

For each cell that you add to the Add Watch dialog, you can always see the formula and the result of that formula in the Watch Window. You can add cells from other sheets and even from other workbooks.

The Watch Window icon is on the right edge of the Formula Auditing group. When displayed, the Watch Window dialog floats above the grid, showing current value of any watched cells. You can add cells to the Watch list by using the Add Watch button in the panel.
Figure 508. Watch various cells.

Additional Details: The cells listed in the Watch Window act as bookmarks! You can double-click a cell and jump to the cell, even if it is on another worksheet.

Gotcha: If you change the numeric format of a number, it does not automatically appear in the Watch Window. However, if you double-click the Value in the Watch Window, it will update.

Additional Details: You can resize the column widths in the Watch Window, as necessary. Further, you can resize the entire Watch Window, and you can even dock it to the top, bottom, or side of the worksheet. Grab the title bar and drag the Watch Window off the edge of the window. In this figure, the window is docked on the left side of the screen. There is easily room for 3 dozen cells to appear.

Columns in the Watch window include Sheet, Name, Cell, and Value. The Name column will reflect any named ranges that you've assigned.
Figure 509. Dock the watch window.

This article is an excerpt from Power Excel With MrExcel

Title photo by Michał Jakubowski on Unsplash