Every parent has wished they could be two places at the same time. I haven’t found a way to do that in the real world, but at least with this trick you can be in one part of a workbook and see what happens in other parts of the same workbook.
You can enter data into one area of a workbook while watching the results in a different area of the workbook. This is great for checking a complex workbook where you enter data in one sheet, but see the results in another worksheet or distant area of the same sheet. You don’t even have to use some magic keystroke while crossing your fingers and stamping your feet for good luck. This “trick” is on the menu in Excel 2007 and later. It’s just very easy to overlook.
(To find the Watch Window in Excel 2003 use Tools, Formula Auditing, Show Watch Window.)
In Excel 2007, 2010 and 2013 it’s even more visible, but still overlooked. Display the Watch Window by selecting the Formulas tab in the ribbon, then in the Formula Auditing group, click the Watch Window tool. The Watch Window will display looking similar to this figure. If you haven’t used it before it will be blank.
In the figure above a data entry area is active in the worksheet behind the Watch Window, but the Watch Window displays results from a sheet named Month Results and from the cells D10, E10 and F10 that have range names as shown. As you enter data entry values in the shaded cells of column M you can see the results in the Watch Window even though the results are on a separate worksheet. Notice the Watch Window also shows you the range name, formulas, and more of the cells you are watching.
To add cells you want to watch to the window, click the Add Watch button at the top of the window. Switch to worksheet(s) you want to watch and then select the cells you want to watch.