The following showcases an overview of Excel auditing tools.
Download Practice Workbook
The sample dataset showcases Principal Amount, Interest Amount, Monthly Amount, and Interest Rate of different projects.
Tool 1 – Trace Precedents
Trace Precedents is a dynamic tool to sort out the complex chain of cell relationships.
- Choose a cell (D5), and select Trace Precedents in Formulas.
- Two arrows will be displayed in (C5) and (C11) towards (D5) as the interest amount is calculated using the principal amount and interest rate.
Tool 2. Trace Dependents
This tool helps to understand the relationship between cells.
- Choose a (C11), and select Trace Dependents in Formulas.
- The arrows will be displayed from (C11) to other cells indicating that those cells depend on the value of the selected cell.
Tool 3 – Remove Arrows
- Select (C11) and click Remove Arrows in Formulas.
- Arrows will be removed.
Tool 4 – Show Formulas
- While the worksheet is open, go to Formulas and choose Show Formulas.
- All formulas will be displayed.
Tool 5 – Error Checking (Includes Error Checking, Trace Error, Circular References)
- Select (E5), and choose Error Checking in Formulas.
- Click Help on this Error to check details.
- A new window will open, showcasing details about the error and its solution.
- To trace cells containing errors, click Trace Error in Formulas.
- An arrow will indicate the error.
Tool 6 – Evaluate Formula
- Choose (E5) and select Evaluate Formula.
- A window will open.
- Click Evaluate.
- In the new window, click Evaluate again.
- The cell value will be displayed.
Tool 7 – Watch Window
A watch window can be added at the top of the spreadsheet for a specific cell.
The total monthly payment amount was calculated using the SUM function.
- Choose (C12), and use the formula.
- Press ENTER.
=SUM(E5:E9)
- Select (C12) and click Watch Window in Formulas.
- A window will open showing cell value and formula.
Things to Remember
- Hidden rows, columns, or sheets containing crucial data or formulas can impact your audit.
Frequently Asked Questions
1. Can auditing tools automatically fix errors?
Auditing tools help you identify errors and provide insights into potential issues, but they don’t automatically fix errors.
2. Are auditing tools suitable for all types of formulas?
Auditing tools are beneficial for most formulas, but complex or heavily nested formulas might require additional manual analysis.
3. Can I use auditing tools for formulas in different sheets or workbooks?
Yes, you can use auditing tools to analyze formulas that reference cells in other sheets or workbooks.
Auditing Formulas in Excel: Knowledge Hub
<< Go Back to Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
