Formula Auditing in Excel

Formula Auditing in Excel

Formula Auditing | Evaluate Formula

When you have large set of data and need to evaluate the data also you need to do extra analysis it becomes difficult to know where all the functions in the file are coming from. Formula Auditing helps you out with this problem. Formula Auditing in Excel helps you to know the relationship between formula and the cell.

Formula Auditing too is an in-built function in Excel as like the other functions in Excel. Formula Auditing will help to get the spreadsheet properly audited.

In the earlier version i.e. in the Excel 2003 version, Formula Auditing toolbar can be seen by right clicking on the toolbar. Formula Auditing is a powerful component in Excel. There are several ways through which you can carry out formula auditing in Excel. To see the list of ways for carrying out formula auditing: Go to the Formula Tab > Formula Auditing group you will see six different ways.

1. Trace Precedents

2. Trace Dependents Remove Arrow

3. Remove Arrow

4. Show Formula

5. Error Checking

6. Evaluate Formula

The different ways through which you can become master of formula auditing are explained in detail below with the help of example.

1. Trace Precedents – formula auditing

This function in Excel shows arrow that indicates which cell affects the currently selected cells. When you activate the cell you see blue hoax or arrow which shows the direction of the flow. To activate this function: Select the cell that contains formula and Click on Trace Precedents in the Formula tab.

Tip: The shortcut to see the Precedents cells in Excel is CTRL + [ or ALT + M + D.

Precedents cells are those cells that affect the formula in the cell. Below you see an example which shows how you can trace precedents in Excel. If the Precedent is on different sheet, a worksheet icon will appear at the beginning of the arrow.

a. Click on the cell in the spreadsheet. Here we click on cell D2.

Formula Auditing - Trace Precedents

b. Go to the Formula tab > Click on Trace Precedents and The Result appear with blue arrow.

Formula Auditing - Trace Precedents Example

NOTE: If there are no Trace Precedents, you will receive an error message.

2. Trace Dependents – formula auditing

This function in Excel show arrows that indicate which cells are affected by the value of the currently selected cells. It shows arrows that indicate which cell is dependent on the selected cell.

If you have multiple formulas in the spreadsheet you can click on the Trace Dependents function, you can trace all the formulas in the spreadsheet. You can clearly know which cell where dependent to get the result.

TIP: Pres Ctrl +] to trace the Dependents cells. Or use the Shortcut ALT + M + A

To activate this function: Select the cell that contains the formula and Click on Trace Dependents in the Formula Auditing group under Formula tab.

Dependents are those cells on which the formula is depended. This function is of great use while we want to delete unnecessary information from the spreadsheet. Below you see an example which shows how you can trace dependents in Excel.

a. In our example, we have selected cell B7.

Formula Auditing - Trace Dependents

b. Go to the Formula tab > Click on Trace Dependents and The result will appear in the blue arrow.

Formula Auditing - Trace Dependents Example

NOTE: For tracing dependents, they should be referred by a formula in a cell.

3. Remove Arrow – formula auditing

Remove Arrow function can only take place once you have applied Trace Precedents or Trace dependents function in Excel. Remove arrow function is used to remove the lines that are shown. You see a drop down arrow next to remove arrow that shows three options:

a. Remove Trace Precedents Arrows: This option removes all the arrows that are related to trace precedents.

b. Remove Trace Dependents Arrows: This option removes all the arrows that are related to trace dependents.

c. Remove All Arrows: This options helps you to remove all the arrows that is both Precedent arrows and Dependent arrows.

Here we have arrows that shows Trace Precedents as well as Trace Dependents.

Formula Auditing - Remove Arrows

To remove arrow you need to execute the following steps:

Go to the formula tab > in the formula auditing group > Click on Remove Arrow and As you click on any of the option you will see that all the arrows from the worksheet has disappeared.

Formula Auditing - Remove Arrows Example

4. Show Formulas – formula auditing

Excel shows the result of the formula and not the formula. It is the default setting to show the result instead of formula. But, you have an option to view the formula instead of the result.

TIP: The Excel shortcut key to show the formula is CTRL + (`). This key is above the Tab key.

This function is useful when you want to see all the formula used in the spreadsheet at a glance. Instead of clicking on individual cells and looking into the formula bar for the formula used, all you can do it click on the Show Formula function.

Go to the Formula tab > Click on Show formula in the Formula Auditing group.

NOTE: After you have clicked on Show Formula, Clicking on the Show formula again will get you back to the original form.

a. Click anywhere in the spreadsheet.

b.Go to the Formula Tab > Click on Show formula and The formula used in each cell of the spreadsheet will be displayed.

Formula Auditing - Show Formulas

5. Error Checking – formula auditing

The Error checking by default is enabled in our Excel file. When you are not sure why the error has occurred in the formula you can do so by applying error checking. It is good technique to use Error checking once your spreadsheet is ready.

Error Checking is a formula auditing function which is available in the Formula tab under the group head Formula Auditing. As you click on the function Error Checking dialog box will open that will guide you to do what next.

Error checking checks for the common error that appear while using the formula. The drop down menu also shows you an option to trace the error. Trace error option will help you to trace the cells which are linked.

a. Click anywhere in the spreadsheet to activate the spreadsheet.

b. You see an error in the cell D7 of the spreadsheet.

Formula Auditing - Error Checking Example

c. Go to the Formula tab > Click on Error Checking and Error checking dialog box appear that shows where is the error in the spreadsheet and what is the error about.

Formula Auditing - Error Checking Dialog Box

d. Correct the error and you will the correct answer.

Formula Auditing - Remove Arrows Example

6. Evaluate Formula – formula auditing

Evaluate Formula is used to debug a complex formula, evaluating each part of the formula individually. Stepping through the formula part by part can help you verify its calculation correctly. Clicking on Evaluate Formula will open an Evaluate Formula dialog box where formula will be displayed in the evaluation box.

Click on the Evaluate button several times the formula gets evaluated. The underlined part in the formula will be executed next.

After Pressing Evaluate several times. You might see Restart button at the place of Evaluate button. This means that the evaluation is complete.

a. Click on any cell in the spreadsheet and click on Evaluate formula in the formula tab. and In the Evaluate Formula dialog box, you see the formula in the Evaluation box. The underline portion in the evaluation box is the part of the formula we are evaluating.

Formula Auditing - Evaluate Formula Example

b. Click on Evaluate, you will see the other part of the formula that is being evaluated next.

Formula Auditing - Evaluate Formula Example Part 1

c. Continue with the evaluate option until you find Restart option.

Formula Auditing - Evaluate Formula Example Part 2

d. This means that your evaluation is completed.