18.7: Formula Auditing Group
- Page ID
The Formula Auditing group helps the user understand which cells are being used by formulas and check for errors to make sure formulas are calculating accurately.
The Trace Precedents icon Displays arrows that indicate what cells affect the value of the currently selected cell. Usually, these cells are included in a formula. As provided in the previous example, a Trace President shows that the Sum of cells A1-A5 is 15 and the cells included are outlined.
The Trace Dependents icon Displays arrows that indicate what cells are affected by the value in the currently selected cell. Trace Dependents is the opposite of Trace Precedents.
The Remove Arrows icon Button with Drop-Down. The button removes all the arrows drawn by the trace precedents and trace dependents. The drop-down contains the commands for more fine-tuning: Remove Arrows, Remove Precedent Arrows and Remove Dependent Arrows.
The Show Formulas icon - (Ctrl + ') allows the user to toggle between showing formulas in the cells and the result of the formula in all cells in a worksheet.
The Error Checking icon with more drop-down options displays the "Error Checking" dialog box. If you have created a formula that is not working correctly, the Error Checking dialog box can assist. As demonstrated below, the formula was changed to include the addition of cell B1, which contains no data. Error checking provides an analysis of the formula, and options to correct the error.
The drop-down contains the commands: Error Checking, Trace Error, and Circular References. All commands will only activate if the worksheet contains an error.
The Evaluate Formula icon displays the Evaluate Formula dialog box. In the previous example provided, the formula did not include a value in cell B1, so it was incorrect, the Evaluate formula dialog box provides information that the problem is cell B1 because it is empty. The buttons Step in and Step Out allow the user to review each aspect of the formula to determine where the error is (in this case it is in Cell B1 as described in the dialog box.
Finally, the Watch Window icon - Displays the. Watch Window
Allows you to view the contents of cells and their results as you make changes in other places in the document. Using the watch window, you can Add Watch by selecting a range of cells and click Add Watch. This will allow the user to continue to view the value of those cells as they work in other places in the workbook. This can be helpful if a user is working in a complicated formula that may change a value that is very important.