How to Audit Formulas Using Excel Auditing Tools – 7 Tools

The following showcases an overview of  Excel auditing tools.

Overview of how to audit formulas using Excel auditing tools

Download Practice Workbook

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

Sample dataset of how to audit formulas using Excel auditing tools

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.

Clicking the Trace Precedents option from the Formulas tab

  • Two arrows will be displayed in (C5) and (C11) towards (D5) as the interest amount is calculated using the principal amount and interest rate.

Final result with trace precedents arrow

Tool 2. Trace Dependents

This tool helps to understand the relationship between cells.

  • Choose a (C11), and select Trace Dependents in Formulas.

Selecting Trace Dependents from the Formulas tab

  • The arrows will be displayed from (C11) to other cells indicating that those cells depend on the value of the selected cell.

Final result with trace dependent arrow

Tool 3 – Remove Arrows

  • Select (C11) and click Remove Arrows in Formulas.

Selecting Remove Arrows from the Formulas tab

  • Arrows will be removed.

Final result with removing arrows

Tool 4 – Show Formulas

  • While the worksheet is open, go to Formulas and choose Show Formulas.

Selecting Show Formulas from Formulas tab

  • All formulas will be displayed.

Final result with displaying formulas inside the spreadsheet

Tool 5 – Error Checking (Includes Error Checking, Trace Error, Circular References)

  • Select (E5), and choose Error Checking in Formulas.

Selecting Error Checking from the Formulas tab

  • Click  Help on this Error to check details.

Clicking Help on this Error option to get help for the error

  • A new window will open, showcasing details about the error and its solution.

Opening a new tab inside the browser for handling error

  • To trace cells containing errors, click Trace Error in Formulas.

Selecting Trace Error from the Formulas tab

  • An arrow will indicate the error.

Getting Trace Error arrow inside the worksheet

Tool 6 – Evaluate Formula

  • Choose  (E5) and select Evaluate Formula.

Clicking Evaluate Formulas from the Formulas tab

  • A window will open.
  • Click Evaluate.

Clicking the Evaluate option from the Evaluate Formula window

  • In the new window, click Evaluate again.

Clicking Evaluate again to evaluate the formula more deeply

  • The cell value will be displayed.

Selecting Close option to close the Formula Evaluate window

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.

Formula of SUM function to calculate sum of monthly payment

  • Select (C12) and click Watch Window in Formulas.

Clicking the Watch Window feature from the Formulas tab

  • A window will open showing cell value and formula.

Final result with watch window feature


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!
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF