Formula Auditing Tools in Excel

MS Excel is mainly used and famous for its function, formulas, and Macros. But what if we are getting some issues while writing the formula, or we cannot get the desired result in a cell as we have not formulated the function correctly? That is why MS Excel provides many built-in tools for auditing and troubleshooting formulas.

The tools which we can use for auditing and formula troubleshooting in ExcelTroubleshooting In ExcelTroubleshooting in Excel helps when we tend to get some errors or unexpected results associated with the formula we use in Excel. read more are:

  • Trace PrecedentsTrace DependentsRemove ArrowsShow FormulasError CheckingEvaluate FormulaEvaluate FormulaThe Evaluate formula in Excel is used to analyze and understand any fundamental Excel formula such as SUM, COUNT, COUNTA, and AVERAGE. For this, you may use the F9 key to break down the formula and evaluate it step by step, or you may use the “Evaluate” tool.read more

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Auditing Tools in Excel (wallstreetmojo.com)

Examples of Auditing Tools in Excel

We will learn about each of the above auditing tools using some examples in Excel.

#1 – Trace Precedents

Suppose we have the following formula in the D2 cell for calculating interest for an FD account in a bank.

If we want to check the formula’s precedents, we can press F2 to get into edit mode after selecting the required cell so that precedents cells get bordered with various colors and written in the same color and cell reference.

We can see that A2 is written with blue in the formula cell, and with the same color, the A2 cell is bordered.

In the same way,

B2 cell has a red color.

C2 cell has a purple color.

This way is good. But we have a more convenient way to check precedents for the formula cell.

We can use the “Trace Precedents” command in the “Formula Auditing” group under the “Formulas” tab to trace precedents.

Select the formula cell and click on the “Trace Precedents” command. Then, you can see an arrow, as shown below.

We can see that precedent cells are highlighted with blue dots.

#2 – Remove Arrows

We can use the “Remove Arrows” command in the “Formula Auditing” group under the “Formulas” tab to remove these arrows.

#3 – Trace Dependents

This command traces the cell, dependent on the selected cell.

Let us use this command using an example.

Suppose we have four amounts that we can invest in. We want to know how much interest we can earn if we invest.

In the above image, we can see that we have applied a formula for calculating interest with Amount 1 and specified interest percentage and duration in the year.

If we want to check which cells are dependent on the G2 cell, we will use the “Trace Dependents” command available in the “Formula Auditing” group under the “Formulas” tab.

Select the G2 cell and click on the “Trace Dependents” command.

In the above image, we can see the arrow lines where arrows indicate which cells are dependent on the cells.

We will remove the arrow lines using the ‘Remove Arrows’ command.

#4 – Show Formulas

We can use this command to display formulas written in the excel sheet. The shortcut key for this command is ‘Ctrl+~.’

See the below image, where we can see the formulas in the cell.

We can see that we can see the formula instead of the formula results. For amounts, the currency format is not visible.

Press ‘Ctrl+~’ again or click on the ‘Show Formulas’ command to deactivate this mode.

#5 – Error Checking

This command is used to check the specified formula or function error.

Let us take an example to understand this.

See the below image where we have an error in the function applied for the result.

We will use the ‘Error CheckingError CheckingWhen we insert a formula and forget to include some required input, we get an error in Excel. Some of the most common errors in Excel formulas are #NAME?, #DIV/0!, #REF!, #NULL!, #N/A, ######, #VALUE! and #NUM!.read more‘ command to solve this error.

The steps would be:

Select the cell where the formula or function is written, then click “Error Checking.”

As we click on the command, we get the dialog box “Error Checking.”

In the above dialog box, it can be seen that there is some invalid name error. Therefore, the formula contains the unrecognized text.

Suppose we use the function or construct the formula for the first time. In that case, we can click on the“Help on this Error” button, which will open the help page for the function in the browser, where we can see all the related information online, understand the cause, and find all the possible solutions.

We will find the following page as we click on this button now.

On this page, we get to know about the error that this error occurs when:

  • The formula refers to a name that has not been defined. For example, the function name or named range has not been described earlier.The formula has a typo in the defined name. It means that there is some typing error.

If we have used the function earlier and know about the function, then we can click on the “Show Calculation Steps” button to check how the evaluation of the function results in an error.

If we click on this button, the following steps are displayed:

  • The next dialog box displays when we click the “Show Calculation Steps” button.

  • After clicking on the ‘Evaluate’ button, the underlined expression, i.e., ‘IIF,’ gets evaluated and gives the following information as displayed in the dialog box.

Does the above image show that the “IIF” expression is evaluated as an error; #NAME? The following expression or reference, i.e., B2, was underlined. If we click the “Step In” button, we can check the internal details and come out by pressing the “Step Out” button.

Now, we will click on the “Evaluate” button to check the result of the underlined expression. After clicking, we get the following result.

  • After clicking the “Evaluate” button, we get the result of the applied function.

  • We got an error. As a result, as we analyzed the function step by step, we learned that there was some error in “IIF.” We can use the “Insert Function” command in the “Function Library” group under the “Formulas” tab.

As we typed the “IF,” we got a similar function in the list; we needed to choose the appropriate function.

After selecting the “IF” function, we get the following dialog box with text boxes for argument, and we need to fill in all the details.

After clicking on “OK,“ we get the result in the cell. Next, we will copy down the function for all the students.

Things to Remember

  • The dates are also shown in the number format if we activate the ‘Show Formulas’ command.While evaluating the formula, we can also use “F9” as a shortcut in ExcelShortcut In ExcelAn Excel shortcut is a technique of performing a manual task in a quicker way.read more.

This article is a guide to Auditing Tools in Excel. We discuss 5 different auditing tools, including show formulas, error checking, trace precedents, etc., with some examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Quick Analysis Tools in ExcelPERCENTRANK in ExcelExcel Watch WindowExcel Structured References