Formula Auditing in Excel

---

Excel Auditing enables you to analyze massive worksheets, interpret nested formulas, identify the causes of errors, check inconsistencies, and trace cell dependency in the current sheet. Additionally, all the formulas defined in the worksheet can be viewed through the Formula Auditing features Show Formula. Manually checking the formula and errors for individual cells is tedious and time-consuming. The Excel Auditing tool can review scenarios like Stock market data, Weather forecasting sheets, Profit /Loss product sheets, and financial reports.

Various Excel Auditing Tools

• Trace Precedents − It populates blue arrows showing the cell range affecting the active cell's result.
• Trace Dependents − It populates blue arrows showing the cell range affected by the active cell's result.
• Remove Arrows − It allows you to eliminate the arrows.
• Error Checking − It inspects the common errors gradually found in the active worksheet.
• Evaluate Formula − It enables you to compute the complex formula, and its subpart can be evaluated independently.
• Show Formula − You can view all the formulas defined in a specific worksheet.
• Watch Window − You can monitor the cells on the Watch window while working on the other worksheets.

Trace Precedents and Trace Dependents

Step 1 − Consider the sample dataset, which consists of columns named Particulars and "Year 2023," to which many formulas are applied.

Step 2 − Click on the B13 cell, go to the "Formula" tab, and select the "Trace Precedents" under the "Formula Auditing" group. After that, the range of cells used in the C13 formula, that is =SUM(C8:C12), is traced by the arrows.

Step 3 − After that, click on the "Remove Arrows" to remove the highlighted arrows.

Step 4 − Click the C7 cell and select the "Trace Dependents" option under the "Formula Auditing" group.

Therefore, arrows highlight all the cells dependent on the B7 cell. The formula "=B7-B13" is used in the B14 cell.

Select the "Remove Arrows" to delete the arrows the trace dependents drew.

Therefore, the arrows have been removed as shown below −

Can we view all the defined formulas in this worksheet?

Yes, using the Show Formula, all the formula is displayed in the worksheet.

Click on the "Show Formula" under the "Formula Auditing" group.

Therefore, all the formulas are visible in the Sheet4s B column worksheet.

Watch Window Auditing Tool

You can closely monitor the cells and their formulas without frequently going to the cell location. The Watch Window is helpful for inspecting the results and formulas of the cells in a lengthy workbook. The Watch Window tool is available in Excel 2016 and its latest versions.

Step 1 − Select the "Formulas" tab and click the "Watch Window" under the "Formula Auditing" group.

Step 2 − After that, the "Watch Window" dialog box will appear, where you can select "Add Watch" to add the specific cell.

Step 3 − Another dialog box, Add Watch, will open. Select the B14 cell and hit the Add button.

Therefore, B14 is inserted into the Watch Window, and the complete details of the B14 cell, such as its sheet name, result, and formula, are specified. Similarly, you can add other cells in the Watch Window to inspect them closely.

To delete a cell from the Watch Window, select the row of the B14 cell and click the Delete button.

Therefore, the B14 cell is deleted from the Watch Window.