Formula Auditing Tools in Excel

Excel is a powerful tool for managing and analyzing data, but when we receive a file with complex formulas, it can be challenging to understand how the values are calculated. This is where the formula auditing tools in Excel come in handy.

In order to understand relationships between formulas and cells, we can either click on the cells one by one, or we can opt for the formula auditing tools in Excel to find out where the values have come from to create the formulas or where the values are being used. 

Formula auditing tools in Excel allow users to perform the following:

• Check formula errors
• Trace formula precedents and dependents
• Display worksheet formulas 
• Evaluate formulas
• Remove precedent and dependent arrows

Formula auditing tools

Formula auditing tools in Excel are a set of features that help users examine and analyze the formulas used in their worksheets. They are found on the Formulas tab of the Excel ribbon, within the Formula Auditing command group. Using these tools, users can quickly identify and troubleshoot errors in their Excel formulas, saving time and preventing mistakes. 

In this article, we will cover seven Excel formula auditing commands, including Trace Precedents and Dependents, Remove Arrows, Show Formulas, Error Checking, Evaluate Formula, and Watch Window.

The Trace Precedents command identifies cells used in the formula, while Trace Dependents identify cells that depend on the formula.

Evaluate Formula shows how Excel evaluates a formula step by step, and Error Checking automatically checks for errors in formulas and suggests corrections.

Let’s dive in!

1. Trace Precedents

The Trace Precedents command reveals cells that are used within a formula. Those cells are called precedent cells.

Here’s an example of how to trace precedent cells in Excel.

The net income of employee Janice Young is $175,175. 

To check which cells are used to calculate this value, (the precedents), perform the following: 

• Click on cell B14, the net income.
• Go to the Formulas tab, and select ‘Trace Precedents’.

Results:

A tracer arrow appears. If it is blue, it means there’s no error in the formula. If the arrow is red, this indicates an error.

The tracer arrow reveals the cells used to calculate the active cell (B14). In our case, Cell B9 and Cell B12 are used to calculate cell B14; they are the precedent cells to cell B14. Their relationships are revealed by the trace precedent feature. 

2. Trace Dependents

The Trace Dependents command reveals cells that depend on a specific cell. 

To check cells that depend upon a specific cell in their formula (the dependents) perform the following: 

• Click on cell B9, the Gross Salary.
• Go to the Formulas tab and select ‘Trace Precedents’.

Results:

The same tracer arrow in blue appears. There’s no error in the formula. 

The tracer arrow reveals that there are cells that depend upon B9 in their calculation. In our case, cells B12 and B14 are dependent cells to cell B9. Payable salary tax is dependent on the gross salary, and so is net income. The relationship between these three cells is revealed by the Trace Dependent command. 

3. Remove Arrows

After checking the relationships between cells, to remove the tracer arrow, simply go back to the Formulas tab and click ‘Remove arrows’. 

There are 3 options in total:

1. Remove Arrows: to remove all tracer arrows drawn by Trace Precedents and Trace Dependents.
2. Remove Precedent Arrows: to remove tracer arrows drawn by Trace Precedents only.
3. Remove Dependent Arrows: to remove tracer arrows drawn by Trace Dependents only.

4. Show Formulas

By default, functions and formulas in Excel display the results of their calculations within their respective cells. Instead of clicking on each cell one by one to check their formula in the formula bar, you can use ‘Show Formulas’ under the Formula Auditing group.

Results:

All cells within the worksheet that contain formulas will now display those formulas instead of the result of the calculation.

5. Error Checking

Sometimes, when there are errors in formulas, they return a #DIV/0 or #VALUE! as a result instead of the expected value. The Error Checking command has several options for detailed error checking.

To find out what the errors are and to fix the formulas, the ‘Error Checking’ command is here to help. 

To begin with, enter value x into cell B11. As a result, we see two dependent cells, B12 and B14, both displaying the #VALUE! error. 

To check the errors in the formulas, click on cell B12, go to the Formulas tab and select ‘Error Checking’. You will then see the Error Checking dialog box.

Excel has identified the error in cell B12 wherein a value used in the formula was the wrong data type. In our case, cell B11 is of the wrong data type, as it should be a number or a percentage instead of a text value.

You can choose any of the following options to continue:

Help on this Error: 

This will take you to a Microsoft webpage specific to the type of error you are experiencing, and suggestions for fixing the error. 

Show Calculation Steps

This command takes you through each stage of the formula to evaluate where the error(s) occurred. Steps that will result in an error are underlined. For more complex formulas, you can click the "Evaluate" button to pinpoint the error-causing cell. (See Evaluate Formula below.)

Ignore Error: 

This tells Excel to overlook this error so that the cell is no longer flagged.

Edit in Formula Bar: 

This means you will make the necessary changes manually in the Formula Bar.

---

Learn more: 10 Most common Excel errors and how to fix them

---

6. Evaluate Formula

Sometimes, a value or a result in Excel comes from multiple intermediate calculations and formulas. To understand how the formula comes up with the final result, you can use the ‘Evaluate Formula’ function, where different parts of a formula are evaluated individually.  

Using the same example, perform the following:

• Click on cell B14.
• Go to the Formulas tab and select ‘Evaluate Formula’.

The Evaluate Formula window will appear, indicating that cell B14 is a result of cell B9 minus cell B12.

Click Evaluate to see the result of the cell, in our example, it is cell B9, which is $269,500. 

Or click Step In, to see the preceding cells of cell B9, which we can see below. Cell B9 is the sum of the total of cell B2 to B7. 

7. Watch Window

The Watch Window command doesn't do any actual formula auditing but is a useful and convenient tool, especially on large spreadsheets. It allows you to monitor specific cells and their values without having to scroll to the location of that cell. The window can hover over your worksheet, or you can drag it to a corner of your screen to dock it to the side or bottom of the Excel window.

Formula auditing shortcut keys

So now we know the benefits of using Formula Auditing tools to indicate the relationships between formulas and cells. To perform formula auditing, instead of going to the Formula Tabs with a mouse, there are a few shortcut keys that perform the same functions. Using the shortcut keys drastically increases the speed, and hence, shortens work time. 

Command	Shortcut	Description
Trace Precedents	Ctrl + [	This command will select the precedent cells but will not draw trace arrows.
Trace Dependents	Ctrl + ]	This command will select the dependent cells but will not draw trace arrows.
Show Formulas	Ctrl + ~	Use this shortcut to select or deselect the Show Formulas command.

Summary

Formula auditing can help you better understand how your Excel formulas work. The seven formula auditing tools that we covered show you which cells are related to the other, and how the final values are calculated. They also help trace and rectify errors. 

Formula auditing tools in Excel are powerful and essential for anyone working with complex spreadsheets. By mastering these tools, you can save time, reduce errors, and gain a deeper understanding of how formulas work. At GoSkills, we offer a wide range of learning resources, including Excel courses, that can help you improve your data analysis abilities.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial