Formula Auditing Tools in Excel
As we all know that MS Excel is mainly used and famous for its function, formulas, and macros. But what if we are getting some issue while writing the formula, or we are not able to get the desired result in a cell as we have not formulated the function correctly. That is why MS Excel provides a lot of built-in tools for formula auditing and troubleshooting formulas.
The tools which we can use for auditing and formula troubleshooting in excel are:
- Trace Precedents
- Trace Dependents
- Remove Arrows
- Show Formulas
- Error Checking
- Evaluate 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 your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Auditing Tools in Excel (wallstreetmojo.com)
Examples of Auditing Tools in Excel
We will learn about each of the above auditing tools, one by one, using some examples in excel.
#1 – Trace Precedents
Suppose we have the following formula in D2 cell for calculating interest for an FD account in a bank.
If we want to check the precedents for the formula, then we can press F2 to get into edit mode after selecting the required cell so that precedents cells got bordered with various colors and in the same color, cell reference is written.
We can see that A2 is written with blue color 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.
To trace precedents, we can use the ‘Trace Precedents’ command in the ‘Formula Auditing’ group under the ‘Formulas’ tab.
We need to select the formula cell and then 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
To remove these arrows, we can use the ‘Remove Arrows’ command in the ‘Formula Auditing’ group under the ‘Formulas’ tab.
#3 – Trace Dependents
This command is used to trace the cell, which is dependent on the selected cell.
Let’s 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.
We can see that in the above image, we have applied a formula for calculating interest with Amount 1 and specified interest percentage and duration in the year.
We will copy the formula and paste it in the adjacent cells for amount 2, amount 3, and amount 4. It can be noticed that we have used an absolute cell referenceAbsolute Cell ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more for G2 and I2 cells as we do not want to get changed these references while copying and pasting.
Now, if we want to check if which cells are dependent on the G2 cell, then 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.
Now 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 instead of formula results, we can see the formula. For amounts, the currency format is not visible.
To deactivate this mode, press ‘Ctrl+~’ again, or click on the ‘Show Formulas’ command.
#5 – Error Checking
This command is used to check the error in the specified formula or function.
Let’s take an example to understand this.
See the below image where we have an error in the function applied for the result.
Now to solve this error, 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.
Steps would be:
Select the cell where the formula or function is written, then click on ‘Error Checking.’
As we click on command, we get the following dialog box captioned as ‘Error Checking.’
In the above dialog box, it can be seen that there is some invalid name error. The formula contains the unrecognized text.
If we are using the function or constructed the formula for the first time, then we can click on ‘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 and understand the cause and find all the possible solutions.
As we click on this button now, we will find the following page.
On this page, we get to know about the error that this error comes when
- The formula refers to a name that has not been defined. It means the function name or named range has not been defined 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 following dialog box displays when we click on 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.
As we can see in the above image, the ‘IIF’ expression is evaluated to be an error, which is ‘#NAME?’. Now next expression or reference, i.e., B2, got underlined. If we click on the ‘Step In’ button, then we can check the internal details of a step also 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 on the ‘Evaluate’ button, we get the result of the function applied.
- We got an error as a result, and as we analyzed the function step by step, we got to know that there is some error in ‘IIF.’ For this, 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 need to choose the appropriate function.
After selecting the ‘If’ function, we get the following dialog box with text boxes for argument, and we will fill all the details.
After clicking on ‘Ok,’ we get the result in the cell. We will copy down the function for all the students.
Things to Remember
- If we activate the ‘Show Formulas’ command, the dates are also shown in the number format.
- While evaluating the formula, we can also use F9 as a shortcut in excel.
Recommended Articles
This has been a guide to Auditing Tools in Excel. Here 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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
47.1K subscribers