Formula Auditing in Excel
Formula auditing is an effective option in Excel that helps you understand the relationship between formulas and cells. When you troubleshoot excel formulas or audit spreadsheets, different formula auditing tools will be of great help. You can find out the cell that is causing an error in calculation by identifying the relationship between the formulas and the cells in your worksheet. By identifying the relationship, you can test your formula to identify cells that provide the inputs for the formula (precedents) and cells that depend on the results of the formula (dependents).
You can find Formula Auditing tools in Excel, if you go to Formulas -> Formula Auditing. Different formula auditing options available in Excel are Trace Precedents, Trace Dependents, Show Formulas, Error Checking and Evaluate Formula. To illustrate the concept, let’s use a complex nested IF function that calculates the grade of students based on their score. Let’s look at the data given below.
Let’s add the formula =IF(C2>=90,$H$5,IF(C2>=80, $H$6, IF(C2>=60,$H$7,IF(C2>=40,$H$8,$H$9)))) in the cell D2 to calculate the grade of the first student and copy the formula in cells D3:D11.
Now, click on cell D2 and then go to Formulas -> Formula Auditing ->Trace Precedents and you will get a screen as given below.
Here, Excel has drawn arrows to the cells that are referred in the formula in the cell D2. You have used values in cells C2, H5, H6, H7, H8 and H9 for calculating grade. Now, you are able to ensure that you provided correct inputs to the formula while calculating the grade. You can remove those blue arrows by going to Formulas -> Formula Auditing -> Remove Arrows.
Next, let’s check the reverse, i.e. whether a particular input is used in the formula. For this, click on cell C4. We know that the value in cell C4 is used in the calculation of the formula in cell D4. So, click on cell C4 and then go to Formulas -> Formula Auditing -> Trace Dependents and you will get the following window.
This means that the value in cell C4 is used by the formula in cell D4 for calculating the results. You can use the same Remove Arrows option in Formulas ->Formula Auditing to remove the blue arrow.
If you normally click on any cell containing a formula, you will be seeing the result in the cell and the formula in the formula bar. But, if you wish to see the formula inside the cell and not the value, then click the particular cell and go to Formulas ->Formula Auditing -> Show Formulas and you will see the formula as given below.
In case of complex formulas, it would be difficult to check whether you get what you need. In such cases, you can use Evaluate Formula option to evaluate the formula part by part, so that you can get the results for each section and ensure that you have written the formula correctly. Let’s try this with our nested IF function. Click on the cell D2 and then go to Formulas -> Formula Auditing -> Evaluate Formula and the Evaluate Formula window will be opened. If you look at the formula carefully, you can see that the first C2 (C2 in logical test condition is underlined as given below (circled in red).
If you click the Evaluate button, C2 will be replaced with the actual value in cell C2. Again, click Evaluate to check whether 63 is greater than or equal to 90. As the condition returns false, it goes to the value-if-false condition and repeats the process. You will get the window as shown below at a specific stage.
If you look at this window, you can see that the IF condition checks whether 63 is greater than or equal to 60. As the function returns TRUE, it will return the value in cell H7.
Read More about Excel here