60-Second Skills #3: Watch Window
Ever want to be omniscient? Would you settle for being able to see through worksheets in Excel?
Excel has a neat tool called Watch Window that can enable you to keep an eye on changing cell values on another tab (or in an out-of-view part of the current tab) while you fiddle with inputs and calculations on the current tab.
Example: Let’s say that I am on my Assumptions Input tab, and want to see how the IRR for the Third Party Investor changes as I make changes to the rent or expense growth rates. The problem is that the Third Party Investor IRR resides on my Waterfall #1 tab, so I would have to keep flipping back and forth to see how the IRR was impacted with each new input on the Assumptions tab.
Flip no more, my friends. Instead, go to the Formulas tab on the menu ribbon, and click on Watch Window.
When the Watch Window itself appears, click on Add Watch, and then navigate to the cell you wish to monitor, and then click Add. Now you can keep an eye on the value at all times. This is something I do when I am in deep surgery on a model and want to make sure that the model isn’t crashing based on formula changes (so I will monitor the project level IRR to make sure it doesn’t turn into #ERROR or the like).
Enjoy, and post back with other skills that you think would benefit your fellow Excel ninjas.
P.S. You can “dock” the Watch Window at the bottom of your menu ribbon, but in my opinion it takes up too much screen real estate, so I usually tolerate it in “floating” mode.