To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Question: We use a monolithic workbook with 17 worksheets. As I fill in data on the back worksheets, I have to keep my eye on certain cells on the summary worksheet. Right now, I am constantly flipping back and forth between worksheets. Is there an easier way?
View Worksheets Side by Side
If you have one of those huge widescreen monitors, you can probably get away with viewing the Summary worksheet in a new window. The trick to seeing two worksheets from the same workbook is to use the New Window command. In Excel 2003 or older, choose Window, New Window. In Excel 2007 or later, choose View, New Window.
Choose the Arrange All command to put the windows side by side. You can display the Summary worksheet in the 2nd window. This works great if all of your key figures are in the same section of the Summary worksheet.
Using the Watch Window
The versatile Watch Window was added quietly to Excel 2003 and buried deep in the Excel menu structure where few people would find it. To display the window in Excel 2003, use Tools, Formula Auditing, Show Watch Window. In Excel 2007 or later, the Watch Window is a large icon on the right side of the Formulas tab.
To watch the current value of a cell, click the Add Watch icon in the Watch Window. Navigate to any worksheet and choose a cell. Excel will now display the name, value, and formula of that cell in the floating Watch Window as shown below. Continue adding more key indicator cells to the Watch Window. As you start changing numbers in the various worksheets, you will always be able to see the current value of the key cells in the Watch Window.
Watch Window Tricks
I always want to see the current value in the Watch Window, but I really don’t care about the cell name or the formula in the cell. Hover to the right of the Name heading and you will be able to resize that column to be smaller or even practically hidden. Once you have shrunk the unneeded columns, you can minimize the size of the Watch Window to minimize its footprint above your worksheet.
Also, double click any cell in the Watch Window to instantly navigate to that workbook, worksheet, and cell.
Bill Jelen is the author of 32 books, including Pivot Table Data Crunching, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.