Lesson: Auditing Worksheets

Large spreadsheets are often very complicated with formulas referring to many cells each of which contain their own formulas referring to other cells that might be contained in other worksheets or even other workbooks. Excel's Formula Auditing commands are very helpful when working in large worksheets.

The two most common uses of the Formula Auditing commands are:

1. To locate the cause of errors. Often an error appearing in one cell is caused by an error in another cell. The Formula Auditing commands can help you find the cell causing the error.
2. To better understand worksheets and information in specific cells. In a complex worksheet, it can be difficult to ascertain where results come from. The Formula Auditing commands can help you understand how different cells in the worksheet relate to each other.

Detailed instructions for using the most commonly used Formula Auditing commands follow.

Tracing Precedents

Tracing precedents in Microsoft Excel allows you to visually see which cells impact a given cell. To trace precedents:

1. Select the cell to trace precedents for.
2. On the Formulas tab, in the Formula Auditing group, click the Trace Precedents command:
3. The following screen shot shows the precedents for cell B11:
4. By clicking Trace Precedents a second time, we see another level of precedents (the precedents for the cells directly preceding the first cell):
5. Clicking Trace Precedents subsequent times shows additional levels of precedents. If one of the preceding cells is on another sheet, Excel will add an arrow pointing to an image of another sheet:
6. By double-clicking the small circle next to the image of the sheet, you can open the Go To dialog box to see which sheet the cell refers to:
7. Select the sheet and click OK to go to the specific cell in the other sheet:
8. To remove the arrows showing precedents, click Remove Arrows:

Tracing Precedents

In this exercise, you will practice tracing precedents.

1. Open Auditing Formulas.xlsx from your Excel2019.3/Exercises folder.
2. What are the first-level precedents for cell B5?
3. What are the second-level precedents for cell B5?
4. What are the first-level precedents for cell B11?
5. What are the second-level precedents for cell B11?

Solution:

Tracing Dependents

Tracing dependents in Microsoft Excel allows you to visually see which cells are impacted by a given cell. To trace dependents:

1. Select the cell to trace dependents for.
2. On the Formulas tab, in the Formula Auditing group, click the Trace Dependents command:
3. The following screen shot shows the dependents for cell C3:
4. By clicking Trace Dependents a second time, we see another level of dependents (the dependents for the cells directly dependent upon the first cell):
5. As with precedents, clicking Trace Dependents subsequent times shows additional levels of dependents. If one of the dependent cells is on another sheet, Excel will add an arrow pointing to an image of another sheet.
6. To remove the arrows showing dependents, click Remove Arrows:

Tracing Dependents

In this exercise, you will practice tracing dependents.

1. Open Auditing Formulas.xlsx from your Excel2019.3/Exercises folder.
2. What are the first-level dependents for cell B4?
3. What are the second-level dependents for cell B4?

Showing Formulas

Sometimes it is useful to see the formulas in all the cells in a worksheet rather than the values. To show formulas:

1. Select any cell in the worksheet in which you want to show the formulas.
2. On the Formulas tab, in the Formula Auditing group, click the Show Formulas command:
3. Instead of values, you can now see the formulas in each cell:
4. To go back to showing values, simply click the Show Formulas command again.