What is Formula Auditing in Excel

Best Excel Tutorial

11 years ago

In this article you will learn everything about formula auditing. It is especially about Trace Precedents and Trace Dependences which can help you to understand what is going on in the worksheet.

Formula Auditing is a feature in Excel that allows users to check and evaluate the formulas in their spreadsheets to ensure accuracy and troubleshoot errors. It helps users to understand how formulas are working and identify any mistakes in the calculation process.

In the Formulas tab you can see a section called Formula Auditing. They include:

Trace Precedents: Trace Precedents allows you to identify all the cells that a particular formula depends on.
Trace Dependents shows all the cells that rely on the selected cell’s value.
Evaluate Formula feature allows you to step through a complex formula one component at a time.
Error Checking scans your worksheet for errors in formulas, such as division by zero, invalid cell references, or incorrect data types.
Watch Window allows you to monitor the value of specific cells while working elsewhere in your workbook.
Show Formulas: by default, Excel displays the results of formulas in cells. The Show Formulas tool toggles this setting, allowing you to display the actual formulas instead of the results.
Remove Arrows: After using Trace Precedents or Trace Dependents, arrows appear on your worksheet to indicate cell relationships. To clear these arrows and declutter your view, use the Remove Arrows option.

By using these Formula Auditing tools, users can easily identify and fix errors in their spreadsheets, ensuring accurate calculations and reducing the risk of mistakes in important data analysis.

Table of Contents

Toggle

Trace Precedents – Which cells are contributing to this formula?

Commands contained there in provide an analysis of the formulas from your workbook. For example, in a situation when you are in a cell that contains a specific formula, and you want to check the cell to which it refers. Then you can use the Trace Precedents command. This will display the arrows from all the cells that are used in the formula to cells analyzed.

Excel tells us, from which the data depends on our formula by selecting the color cell source. Below cell C2 is directly dependent on B2 and C12.

Using the same function again, Excel puts another arrow until the all the cells with formulas that have an influence on our cell, will be referred to their predecessors.

Using Trace Precedents can help you understand the structure of your spreadsheet and identify any potential errors or issues with your formulas. It’s a powerful tool for debugging and troubleshooting complex worksheets.

Tracking dependencies – which formulas use this cell?

The dependency tracking shows that cells depend indicated, the opposite of tracking predecessors. To do it use the Trace Dependents button.

The following example shows how the cells change, if you change the value in cell B2.

Using Trace Dependents can help you understand the impact of a change in a cell on other cells in your spreadsheet. It’s a powerful tool for identifying potential issues or unintended consequences of your calculations.