How to use Formula Auditing in Excel

Formula_Auditing_excel_Course_in_hindi

When you have a 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, the 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 a master of formula auditing are explained in detail below with the help of an example.

1. Trace Precedents – formula auditing

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

Precedent cells are those cells that affect the formula in the cell. Below you see an example that shows how you can trace precedents in Excel. If the Precedent is on a 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
Excel Formula Auditing Example

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

Formula Auditing - Trace Precedents Example
Excel Formula Auditing Trace Precedents

2. Trace Dependents – formula auditing

This function in Excel shows 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 was dependent to get the result.

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

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

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

Formula Auditing - Trace Dependents
Excel Formula Auditing dependents Example

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

Formula Auditing - Trace Dependents Example
Excel Formula Auditing Trace Dependents

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 the 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 option helps you to remove all the arrows that are both Precedent arrows and Dependent arrows.

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

Formula Auditing - Remove Arrows
Excel Formula Auditing Trace Precedents & Trace Dependents

To remove the 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 options you will see that all the arrows from the worksheet have disappeared.

Formula Auditing - Remove Arrows Example
Excel Formula Auditing Remove Arrow

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 the formula. But, you have an option to view the formula instead of the result.

This function is useful when you want to see all the formulas 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.

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
Excel Formula Auditing Show Formulas

5. Error Checking – formula auditing

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 a good technique to use Error-checking once your spreadsheet is ready.

Error Checking is a formula auditing function that 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. The 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
Excel 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
Excel Formula Auditing Error checking popup

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

Formula Auditing - Remove Arrows Example
Excel Formula Auditing Error checking Result

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 the formula will be displayed in the evaluation box.

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

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

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

Formula Auditing - Evaluate Formula Example
Excel Formula Auditing Evaluate Formula SC01

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
Excel Formula Auditing Evaluate Formula SC02

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

Formula Auditing - Evaluate Formula Example Part 2
Excel Formula Auditing Evaluate Formula Result

d. This means that your evaluation is completed.