Formula Auditing in Excel
The Formula Auditing is one of the most useful tools in Excel any entrepreneur or employee can use, especially while dealing with large volumes of data. Sometimes you are only given completed Excel worksheets on which you need to put extra analysis. Many times, it becomes difficult to notice all of the functions that are present in a file. When you land up with this situation, always use the Excel inbuilt Formula Audit toolbar.
The tutorial we will cover the definition of Formula Auditing, different methods for formula auditing and troubleshooting formulas, step-by-step analysis of each method.
What is Formula Auditing?
"Formula auditing is an essential tool in Excel that enables users to show the relationship between formulas and cells."
Excel Formula Auditing toolbar helps the user to quickly and easily find:
The output of Formula Auditing is presented graphically by arrow lines, thereby making the entire formula visualization effortless. It allows the user to show all the formulas in the active worksheet with a single command. In case your formulas are further referring to cells present in a different workbook, it also opens that workbook.
Microsoft Excel provides several different methods to audit a Formula. If you click on the Formulas ribbon tab of Excel, you will find the Formula Auditing section. However, many users might also need to customize the ribbon to see this option. Following are the different ways using which you can audit a formula:
All these methods help you with formula auditing and troubleshooting formulas. Moving ahead in this tutorial, we will learn in detail about each method.
#1. Trace Precedents
Trace Precedents displays tracer arrows from the cells showing the direction of information flow. You see a blue box around the cells when this method is active. However, one can press this button multiple times to catch additional levels.
Let's understand the method using an example:
As shown in the above table, we are given formula in the D2 cell for calculating the GST amount for your Service amount.
Select the required cell and press the F2 button to enable the edit mode. You will notice that the precedents cells got bordered with various colors and written in the same color and cell reference.
As a result, you will notice that cell A2 is highlighted with blue color, and the border color is also filled using the same color.
Though it is simple and easy, we have a more suitable method to check precedents for the formula cell.
Following are the steps to use Trace Precedents in your Excel worksheet:
NOTE: If there occur no trace precedents in the cell, Excel will throw an error message.
#2. Trace Dependents Function
This function lets the user see all the formulas in which a particular cell is used. For instance, let's say you have a value used in multiple formulas in your Excel worksheet. In that case, you select the formula cell, click on the Trace Dependents option, and all formula cells where that same value is used will be displayed in blue with colored arrows pointing from that cell to the formulas wherever it is used.
In the example below, we have calculated the 10% interest for the base amount and the GST amount.
Here we have used the formula =A4*E2 for the first cell and =C4*E2 for the second cell. Therefore, we can conclude that E2 is a dependent cell for the cells A4 and C4. Following are the steps to use Trace Dependents function for the cell E2 in your Excel worksheet:
#3. Remove Arrows
In the above two options, we covered Trace Precedent and Trace Dependent. Using both methods, we have displayed tracer arrows from the cells showing the direction of information flow. But what if we want to remove the arrows afterward?
Don't worry; Excel has provided an inbuilt option in the Formula Auditing section to remove the arrow quickly. Following are the steps to remove the arrows from the cell E2 in your Excel worksheet:
Note - If you want to trace dependents of a cell, make sure a formula should reference the cell in another cell; else, it will throw an error message.
#4. Showing Formulas
When you are working on an Excel worksheet that carries multiple formulas, you often need to check the formulas to ensure things are working smoothly. Excel has provided the Show Formula option in the formula auditing section to quickly display all the available formulas in your active worksheet. Sounds great, isn't it!
The worksheet below contains the GST report and 10% interest figure for our base amount.
Here we have entered the formula in three of the cells. Following are the steps to display all the cells containing formulas in your active Excel worksheet:
#5. Evaluating a Formula
Sometimes you are given a pre-made worksheet wherein complex formulas are used. To uncover the step-by-step working of a complex formula, you can utilize the Excel Evaluate Formula command.
Let's suppose we have incorporated the NPV formula in cell E4. To Evaluate the formula, follow the below-given steps:
Errors are common when we deal with functions and formulas. Therefore, evaluating a formula is essential as it checks the specified formula or function error. It is a good practice to check all the errors in your worksheet once all the calculations are done.
Let's understand the same using a simple calculation.
As you can see the above calculation has computed an error #NAME? in the cell E4. Following are the steps to check errors that occurs in your Excel worksheet using the Error checking method:
Observe the following points of the Error Checking window:?
Features of Formula Auditing
Below given are significant features of Formula Auditing:
Things to Remember