Sometimes we have a great deal of data in the workbook. Everything is important but most important is the last row of the data containing, say, the daily sum of sales, or percentage of profits in each share in the columns. You may want to monitor the value of a particular cell in your workbook. But when you scroll down a big worksheet, or you move from one worksheet to another worksheet in your workbook, the cells that you wanted to monitor all the time disappear from your view. A feature, known as “Watch Window”, may help you out in this case to monitor cells using Excel Watch Window.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
How to Open Excel Watch Window
For example, there are 83 rows (of data) in this worksheet. The 83rd row shows the total sales amount. You may change some data in the sales column, and you may want to know the total amount, in that case. But for that, you will have to travel to that last row again and again which is inconvenient. In this situation, you can use the Watch Window in this case.
- Go to Formulas ribbon, then Formula Auditing group and click on the Watch Window
A window like this will appear soon and it is called the Watch Window.
How to Manage Excel Watch Window
Here you will learn how to add cells, delete cells, change position, and close the Watch Window.
Add Cells in Excel Watch Window
In the watch window, you will get a button- Add Watch, by using this you can add any number of cells to watch.
- Right now there are no cells in the list. Click on the Add Watch button, Add Watch dialog box appears.
- For now, I select the F83 cell and then click on Add button.
You see cell F83 is added in the Watch Window. You get the whole identity of the cell that you have added to the watch window.
See the image below. At first, the name of the workbook (under the Book column), then the Sheet name, if the cell has any defined name, you will find the name under the Name column, then the Cell reference, on the next column you will get the Value of the cell, and finally, under the Formula column, you will see the formula if the watched cell has any in it.
- Now change some data in the Sales column, and see what happens in the Watch Window. We inserted 0 in Cell F81.
See, the value of the selected cell is also changed. It will update automatically whenever we change any cell.
In this way, you can add multiple cells to your Watch Window if you need.
Change Position of Watch Window
The watch window is a task pane. You can dock this task pane at any place on your worksheet.
- Take your mouse pointer over the upper side of the Watch Window, your cursor will turn into a 4-headed-arrow. Just press your mouse, drag it, and then drop it where you want to place it.
You see (from the image below), I have added the Watch Window below the Ribbon.
You can resize the window, by just taking the mouse over any border or any corner of the window.
Delete Cells from the List
You can delete any watch just by following two clicks only.
- Select the watch from the list of the Watch window and then just click on the Delete Watch button.
The watch of the cell is no more.
Close Watch Window
Closing the Watch Window is nothing but the way of closing any other Microsoft window.
- To close the watch window, just choose the cross at the upper right corner of the window or click on the little down arrow and choose “Close” from the list. Excel remembers the cells that are in the Watch Window. Again, open the Watch Window, and you will see the on-watch-cells are still on the list.
How to Create VBA Watch Window
Now we’ll learn how to add a Watch Window in VBA. By using this we can monitor the value of any variable anywhere in our codes. To show it, we used a code that can make a function to convert Kg to Gram. There’s a variable- ConvertKgtog, we’ll add a Watch Window for this.
- First, click on the variable.
- Later, click as follows: Debug > Add Watch.
A few moments later, you will get a dialog box like the following image.
- It will automatically choose the Expression name. Also, you can type manually.
- The rest of the default option is fine for now, you can change them if you need. Finally, Just press OK.
In a while, the Watches section will appear on the lower section of the VBA window. It contains some column headings- Expressing, Value, Type, and Context. See, our variable is added to the list.
- Now if you run the code line by line by pressing the F8 key, you will get the value of the variable when any line is containing the variable.
Take a look, in the second last line, the value is 0.
For the last line, the value is 20000.
That’s all for the article. I hope the above procedures will be good enough to monitor cells using Excel Watch Window. Feel free to ask any questions in the comment section and give me feedback. Visit ExcelDemy to explore more.