If you want to monitor specific formula behavior, Excel offers Watch Window feature where you can add cell references and track the changes when made to respective references. To bring watch window in Excel follow the steps as below:
- Navigate Formulas ribbon tab
- Locate Formula Auditing group
- Click on Watch Window button
- A popup will appear as shown below
Lets have a look at the various functions available in Watch Window shown above:
- Add Watch…: It adds selected cell range under watch list as shown below.
- Delete Watch: deletes selected range from the list
- Watch List: the area where you can see your watch list
What I just did here? I selected the cell “E8” having value error (to understand errors in excel please see my previous article “Detect Errors in Excel“) then clicked on Add Watch button under Watch Window, a popup of Range Selector comes to confirm selected range, say OK and you are done. Now we will manipulate the formula (“=VBAOVERALL /F4“) of selected cell (i.e. “E8”) and see how watch value gets changed under watch list as shown below. Don’t confuse in the formula “VBAOVERALL” is a name range which is missing/deleted from current sheet cause we have #VALUE error.
I removed the Name Range (“VBAOVERALL“) and reference it with cell “E3“, as soon as I hit enter the value in watch window gets changed.