Excel: Monitor Distant Cells

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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 was added to Excel without much fanfare in 2002. This 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.

  1. 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.

  2. Dock the watch window.