How to Audit Formulas Using Excel Auditing Tools (7 Useful Tools)

Get FREE Advanced Excel Exercises with Solutions!

Auditing formulas in Excel is crucial in maintaining the precision of spreadsheet calculations. The process is simplified by utilizing Excel’s auditing tools, which provide an organized way to spot problems and track down predecessors or dependents. This comprehensive toolbox gives both inexperienced and experienced spreadsheet users the tools they need to fix calculation problems quickly.

Today in this article, I am sharing with you how to audit formulas using Excel auditing tools including trace precedents, trace dependents, remove arrows, show formulas, error checking, evaluate formulas, and watch window.

In the following, you will find an overview of how to audit formulas using Excel auditing tools.

Overview of how to audit formulas using Excel auditing tools

Download Practice Workbook

How to Audit Formulas Using Excel Auditing Tools: 7 Useful Tools

Excel auditing tools provide a suite of functionalities that will enable you to review, validate, and troubleshoot formulas and data. Suppose we have a dataset of some projects’ Principal Amount, Interest Amount, Monthly Amount, and Interest Rate. Now we will apply all the auditing tools using this data table.

Sample dataset of how to audit formulas using Excel auditing tools

1. Trace Precedents

Trace Precedents is a dynamic tool to sort out the complex chain of cell relationships in your spreadsheets. Trace Precedents provide you the ability to understand formula dependencies by highlighting the connections that have an impact on a chosen cell. As we have calculated the interest amount by multiplying the principal amount with the interest rate, let’s see what the trace precedent tool show for this calculation.

  • To do so, choose a cell (D5), and visit the Trace Precedents option from the Formulas tab.

Clicking the Trace Precedents option from the Formulas tab

  • As a result, we will see two arrows from cell (C5) and cell (C11) indicating towards cell (D5) as the interest amount is calculated using the principal amount and interest rate.

Final result with trace precedents arrow

2. Trace Dependents

In order to visually highlights the cells that depend on the value of a selected cell, you can try the Trace Dependents feature in Excel. This is a powerful tool for understanding the relationship between cells. Here, let’s see how the interest rate is dependent on the cells.

  • Simply, choose a cell (C11), and then visit the Trace Dependents feature from the Formulas tab.

Selecting Trace Dependents from the Formulas tab

  • Finally, you will see the arrows from the cell (C11) to other cells indicating the cells that are dependent on the value of the selected cell.

Final result with trace dependent arrow

3. Remove Arrows

After inserting arrows using the above features, you can also delete them by utilizing the Remove Arrows option.

  • Start with, selecting the cell (C11) and clicking the Remove Arrows option from the Formulas tab.

Selecting Remove Arrows from the Formulas tab

  • Within a blink of an eye, the arrows will be removed. It’s that simple.

Final result with removing arrows

4. Show Formulas

Show Formulas in Excel is a helpful tool that allows you to view the actual formulas within cells instead of their calculated results. This feature offers transparency into complex calculations, aiding in formula debugging and verification.

  • While the worksheet is open, visit the Formulas tab and press the Show Formulas option.

Selecting Show Formulas from Formulas tab

  • Immediately, all the cells with formulas will represent the formulas inside the cells.

Final result with displaying formulas inside the spreadsheet

5. Error Checking (Includes Error Checking, Trace Error, Circular References)

Sometimes while applying formulas, you will get errors like #DIV/0!, #VALUE!, #NAME? Errors. To check why it’s happening you can visit Error Checking option from the Formula tab.

  • Select a cell (E5), and hit the Error Checking option from the Formulas tab.

Selecting Error Checking from the Formulas tab

  • Now, you can click the Help on this Error option to check in detail about the error.

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

  • As a result, a new window will open in your browser providing you details about the error and its solution.

Opening a new tab inside the browser for handling error

  • In order to trace from which cells these are happening, click the Trace Error option from the Formulas tab.

Selecting Trace Error from the Formulas tab

  • Immediately, an arrow will appear to indicate the error and its corresponding cells.

Getting Trace Error arrow inside the worksheet

6. Evaluate Formula

When you are dealing with complex formulas and you are having trouble understanding the formulas, at that time you can visit the Evaluate Formula option to have a better understanding.

  • Simply, choose a cell (E5) consisting of the formula and hit the Evaluate Formula option.

Clicking Evaluate Formulas from the Formulas tab

  • Immediately, a window will open evaluating the formula.
  • From there to evaluate more deeply click the Evaluate option.

Clicking the Evaluate option from the Evaluate Formula window

  • Another update will come describing the formula. Click Evaluate again to get the result.

Clicking Evaluate again to evaluate the formula more deeply

  • After completing the evaluation, you will get the cell value in the window.

Selecting Close option to close the Formula Evaluate window

7. Watch Window

While working with a large dataset sometimes you might need to look over some cell values immediately and all time in a specific space. For that, you can add a watch window at the top of the spreadsheet. Here, we have calculated the total monthly payment amount using the SUM function in Excel. Now we will add a watch window for this specific cell.

  • Choose a cell (C12), apply the below formula, and hit ENTER.

Formula of SUM function to calculate sum of monthly payment

  • Now choosing the cell (C12), hit the Watch Window feature from the Formulas tab.

Clicking the Watch Window feature from the Formulas tab

  • Within a glimpse, a window will open at the top of the spreadsheet showing cell value and formula. This watch window is really a helpful tool for making a summary of your dataset. And if you scroll or jump to another sheet the watch window will always be visible on that place.

Final result with watch window feature

Advantages of Using Excel Auditing Tools

  • Auditing tools like “Trace Dependents” and “Trace Precedents” help identify cells that contribute to a specific formula, making it easier to locate errors.
  • The “Show Formulas” feature allows you to view the actual formulas in cells, aiming toward understanding complex calculations.
  • Tools like “Error Checking” automatically detect and highlight common errors like division by zero or incorrect cell references, aiding in quick error resolution.

Things to Remember

  • While auditing be aware of hidden rows, columns, or sheets that might contain crucial data or formulas. These hidden elements can impact your audit.


In conclusion, auditing tools offer a dynamic toolkit for solving complexities within spreadsheets. With proper visualizing dependencies, revealing formulas, and facilitating step-by-step evaluations, these tools will empower you to identify, rectify, and prevent errors efficiently. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.

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. Just ensure that those external references are accessible and accurate.

Auditing Formulas in Excel: Knowledge Hub

<< Go Back to Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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