What is Formula Auditing in Excel?
Formula Auditing in Excel is an option in that allows you to understand the relationships between various formulas and cells.
It maybe done in the following ways:
- Trace Precedents
- Trace Dependents
- Remove Arrows
- Show Formulas
- Error Checking
- Evaluate Formulas
Where to Find Formula Auditing in Excel?
Select the ‘Formulas’ menu. You can find the ‘Formula Auditing’ group towards the right-hand side.
How to Audit Formula in Excel?
Trace Precedents:
Supposing you have reached a particular value but you don’t know how, you can use this option to have arrows point at the reason for the result. Select ‘Trace Precedents’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu, after choosing the cell for which you want to know the previous operations that led to the result. Arrows will indicate the result. Repeat the step to know how you reached this previous value.
Trace Dependents
If you want to know the various values that are dependent on the particular cell you are at, you can use this option. Select ‘Trace Dependents’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu, after choosing the cell for which you want to know the dependents. Arrows will indicate the result.
Remove Arrows
To remove these arrow, select ‘Remove Arrows’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu.
Show Formulas
To know the formulas that led to the value in the cell you are using, select ‘Show Formulas’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu.
Error Checking
To know if there have been errors while calculating the formulas, select ‘Error Checking’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu. The following dialogue box will appear in case of error.
In such a situation you have the following options:
- Get help from Excel to sort this
- See the calculation steps and try to find what went wrong
- Ignore the error
- Edit the formula in the Formula Bar
Evaluate Formula
To evaluate each part of the formula, select ‘Evaluate Formula’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu.
One or more dialogue boxes like the one below will appear. Click ‘Evaluate’ for each one.
Example
Sudeep has been asked to pay Rs. 780 for a group project, the data of which is given in an Excel Sheet. How can he know why he has to pay so much?
Solution
He can select ‘Trace Precedents’ option from the ‘Formula Auditing’ group in the ‘Formulas’ menu, after choosing the cell containing 780. Arrows will indicate the reasons for him to pay that amount.