Tips for Excel, Word, PowerPoint and Other Applications
Using the Watch Function in Excel
Why It Matters To You
Do you find yourself changing key inputs to to a model and always tabbing back and forth between worksheets to see what changed? Sometimes your models or analyses will have inputs spread across multiple worksheets, even multiple workbooks, and it can be difficult to keep track of how each input impacts your bottom line. Fortunately, there is a neat function, located on the Auditing Toolbar, called Watch, that allows you to monitor the value of a cell(s) regardless of where you are in the workbook.
How To ...
Let's start out by finding the Formula Auditing Toolbar. Go into your Main Menu: View: Toolbars, and then click on Formula Auditing. You should end up with this toolbar on your screen, which you can simply drag this up or down to dock on your work pane. I like to keep mine on the top of my Excel window near the Formatting Toolbar.
For this exercise, I created a simple little model that estimates my monthly gas costs. Remember, your inputs could be anywhere, I just put them together in for simplicity sake.
To start the Watch function, click the button on the Auditing Toolbar that looks like a pair of binoculars. This will launch the Watch Window below.
Click on the Add Watch button and you'll see a window named, Add Watch. Highlight the range or discrete set of cells you want to watch and then click OK. I found that if I wanted to watch a cell in another workbook, I wasn't able to navigate to the target cell from the Add Watch window, but if I had a cell reference in a local workbook, I could just copy the cell address and paste it into the Add Watch window.
Click Add and you will return to the Watch Window. Here we see the Book, Sheet, and Cell address of the target cell, its value, and the formula, if applicable, that generates it.
As mentioned earlier, you can also select multiple cells to watch
For even more utility, give each target cell a name (ALT, I, N, D) and you'll never have to guess what each cell represents. You can also resize column widths or the size of the Watch Window to better see information.
For modellers and analysts, this is a great tool to use that is simple to set up and easy to use.
Notes
| Last updated | 6/20/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | Naming Ranges |