How to Audit Formulas Using Excel Auditing Tools – 7 Tools

The following is an overview:

Download Practice Workbook

The sample dataset showcases Principal Amount, Interest Amount, Monthly Amount, and Interest Rate of different projects.

Tool 1 – Trace Precedents

• 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

• 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

• Choose C12 and use the formula.
• Press ENTER.

• 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

• Trace Formula in Excel
• Monitor Cells Using Excel Watch Window
• Excel VBA Watch Window

<< Go Back to Excel Formulas | Learn Excel