facebook google plus twitter
Webucator's Free Advanced Microsoft Excel 2013 Tutorial

Lesson: Auditing Worksheets

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

In this lesson, you will learn about auditing worksheets.

Lesson Goals

  • Audit formulas.
  • Trace precedents to determine which cells impact a specific cell.
  • Trace dependents to determine which cells are impacted by a specific cell.
  • Remove precedent and dependent arrows.
  • Show formulas.
  • Check for errors frequently found in formulas.

Formula Auditing Commands

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 in upcoming activities.

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:Trace Precedents
  3. The following screen shot shows the precedents for cell B11:Precedents
  4. By clicking Trace Precedents a second time, we see another level of precedents (the precedents for the cells directly preceding the first cell):Trace Precedents
  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:Arrow
  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:Go To Dialog Box
  7. Select the sheet and click OK to go to the specific cell in the other sheet:Sheet
  8. To remove the arrows showing precedents, click Remove Arrows:Remove Arrows

Tracing Precedents

Duration: 5 to 15 minutes.

In this exercise, you will practice tracing precedents.

  1. Open Auditing Formulas.xlsx from your Excel2013.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:Trace Precedents
    3. The preceding cells are indicated by the arrow: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:Go To Dialog Box
  3. Remove the arrows showing the precedents for cell B5 by clicking Remove Arrows: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:Formula Auditing Group
    3. The preceding cells are indicated by the arrow:Preceding Cells
  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:Arrow Pointing to Cells

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:Trace Dependents
  3. The following screen shot shows the dependents for cell C3:Dependents for Cell
  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):Trace Dependents
  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:Remove Arrows

Tracing Dependents

Duration: 5 to 10 minutes.

In this exercise, you will practice tracing dependents.

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

Solution:

  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:Trace Dependents
    3. The dependent cells are indicated by the arrow: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:Trace Dependents
  3. Remove the arrows by clicking Remove Arrows: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:Show Formulas
  3. Instead of values, you can now see the formulas in each cell:Formulas in Cells
  4. To go back to showing values, simply click the Show Formulas command again.