Webucator's Free Advanced Microsoft Excel Tutorial

# Lesson: Auditing Worksheets

Welcome to our free Advanced Microsoft Excel tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2019 Training course.

In this lesson, you will learn about auditing worksheets.

Lesson Goals

• Learn to audit formulas.
• Learn to trace precedents to determine which cells impact a specific cell.
• Learn to trace dependents to determine which cells are impacted by a specific cell.
• Learn to remove precedent and dependent arrows.
• Learn to show formulas.
• Learn to check for errors frequently found in formulas.

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

Duration: 5 to 15 minutes.

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:

1. The first-level precedents for cell B5 are cells B3 and B4. To figure this out:
1. Select cell B5.
2. On the Formulas tab, in the Formula Auditing group, click the Trace Precedents command:
3. The preceding cells are indicated by the arrow:
2. The second-level precedents for cell B5 are cells B12 and B13 in the sheet named "Revenue". To figure this out:
1. Click Trace Precedents again.
2. Double-click the small circles next to the images of the sheets in order to open the Go To dialog box showing the sheets and cells:
3. Remove the arrows showing the precedents for cell B5 by clicking Remove Arrows:
4. The first-level precedents for cell B11 are cells B5 and B10. To figure this out:
1. Select cell B11.
2. On the Formulas tab, in the Formula Auditing group, click the Trace Precedents command:
3. The preceding cells are indicated by the arrow:
5. The second-level precedents for cell B11 are cells B3:B4 and cells B7:B9. To figure this out:
1. Click Trace Precedents again.
2. The preceding cells are indicated by the arrows pointing to B5 and B10:

## 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

Duration: 5 to 10 minutes.

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?
1. The first-level dependent for cell B4 is cell B5. To figure this out:
1. Select cell B4.
2. On the Formulas tab, in the Formula Auditing group, click the Trace Dependents command:
3. The dependent cells are indicated by the arrow:
2. The second-level dependents for cell B4 are cells B8:E8 and cells B9 and B11. To figure this out:
1. Click Trace Dependents again.
2. The dependent cells are indicated by the arrows:
3. Remove the arrows by clicking Remove Arrows:

## 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.