Practical 10 – Auditing Formulas with Trace Precedents and Dependents

1. Objective

The primary objective of this practical is to familiarize students with Excel’s powerful formula auditing tools: Trace Precedents and Trace Dependents. Students will learn to:

·         Identify which cells contribute to a formula’s result using Trace Precedents.

·         Identify which formulas use the value of a specific cell using Trace Dependents.

·         Understand the flow of data and calculations within a complex spreadsheet.

·         Effectively troubleshoot errors and verify the accuracy of formulas.

·         Navigate and remove tracing arrows.

2. Introduction

In complex Excel worksheets, formulas often depend on values from many other cells, and a single cell’s value might be used in multiple calculations elsewhere. Understanding these relationships is crucial for auditing, debugging, and confidently modifying spreadsheets. Excel’s "Trace Precedents" and "Trace Dependents" tools provide a visual map of these interconnections, making it easy to see where data comes from (precedents) and where it goes (dependents). This practical will demonstrate how to use these tools on your academic marksheet to gain a deeper insight into its underlying calculations.

3. Pre-requisites

·         Successful completion of Practical 3 and Practical 6.

·         Basic understanding of Microsoft Excel interface and formula entry.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

Ensure you have your completed marksheet from Practical 3 open. We will use this table to demonstrate and practice these auditing tools.

Step 5.1: Opening the Marksheet

1.     Open the Marksheet: Open the Excel workbook containing your Marksheet from Practical 3. Ensure all formulas for Total Marks, Percentage, Grade, and Result are present and calculating correctly.

Step 5.2: Using Trace Precedents

Trace Precedents helps you identify all the cells that contribute to the value of the currently selected cell. It shows arrows pointing from the precedent cells to the active cell.

1.     Select a Cell with a Formula: Click on cell G4 (Alice’s Total Marks). This cell contains the formula =SUM(B4:F4).

2.     Activate Trace Precedents:

o    Go to the Formulas tab on the Excel ribbon.

o    In the Formula Auditing group, click the Trace Precedents button.

o    Observation: Blue arrows will appear, pointing from cells B4 through F4 to cell G4. This visually confirms that Alice’s total marks are derived from her scores in Subject 1 to Subject 5.

3.     Trace Another Level:

o    Now, click on cell H4 (Alice’s Percentage). This cell contains a formula that uses G4.

o    Click Trace Precedents again.

o    Observation: Arrows will now point from G4 and the cell containing 500 (the maximum marks, if explicitly referenced) to H4. This shows that the percentage is dependent on the total marks.

4.     Keep Tracing (if applicable): If H4 was dependent on other formulas, clicking Trace Precedents multiple times can show deeper levels of dependency.

Step 5.3: Using Trace Dependents

Trace Dependents helps you identify all the cells that use the value of the currently selected cell in their formulas. It shows arrows pointing from the active cell to its dependent cells.

1.     Clear Existing Arrows: Before tracing dependents, it’s good practice to clear any existing arrows. On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

2.     Select a Cell (Input/Intermediate): Click on cell B4 (Alice’s score in Subject 1).

3.     Activate Trace Dependents:

o    Go to the Formulas tab on the Excel ribbon.

o    In the Formula Auditing group, click the Trace Dependents button.

o    Observation: An arrow will appear, pointing from B4 to G4 (Alice’s Total Marks), indicating that G4 uses the value of B4.

4.     Trace Another Level:

o    Now, click on cell G4 (Alice’s Total Marks).

o    Click Trace Dependents again.

o    Observation: Arrows will now point from G4 to H4 (Alice’s Percentage) and potentially to I4 and J4 if those formulas directly reference G4 or H4 which, in turn, references G4. This shows where Alice’s total marks are being used.

5.     Identify Dependents of an Intermediate Calculation:

o    Click on cell H4 (Alice’s Percentage).

o    Click Trace Dependents.

o    Observation: Arrows will point from H4 to I4 and J4, confirming that both her grade and pass/fail status depend on her percentage.

Step 5.4: Removing Arrows

After you’ve finished auditing, you can easily remove the tracing arrows.

1.     Remove All Arrows: On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.

o    Observation: All the blue and red arrows will disappear from your worksheet.

2.     Remove Arrows in Steps: Clicking the small dropdown arrow next to Remove Arrows gives you options to Remove Precedent Arrows or Remove Dependent Arrows specifically. This can be useful in complex scenarios if you only want to clear certain types of arrows.

Step 5.5: Understanding Error Tracing

If a formula results in an error (e.g., #DIV/0!), the Error Checking and Trace Error features can help you identify the source of the problem.

1.     Introduce an Error: In cell B4, temporarily change Alice’s Subject 1 marks to 0. Then, in a blank cell (e.g., O4), type =G4/0 and press Enter. This will generate a #DIV/0! error.

2.     Trace Error:

o    Select cell O4 (which contains the error).

o    On the Formulas tab, in the Formula Auditing group, click the Error Checking dropdown and select Trace Error.

o    Observation: Arrows will appear, pointing from the cells that cause the error to the error cell. In this case, an arrow might point from G4 (though G4 isn’t the direct cause, 0 is) and from the cell containing 0 (if you typed =G4/A_Cell_With_Zero).

3.     Clear Errors and Arrows: Undo the changes or delete the error formula. Click Remove Arrows.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet:

1.     Total Marks Audit:

o    For a different student (e.g., Bob), select his Total Marks cell (G5).

o    Use Trace Precedents to see which subject scores contribute to his total. Take a screenshot.

o    Clear arrows.

2.     Subject Impact Analysis:

o    Select Subject 3 score for Alice (D4).

o    Use Trace Dependents to identify where this specific score is used in her calculations (e.g., total marks, percentage, grade, result). Take a screenshot.

o    Clear arrows.

3.     Pass/Fail Determination:

o    Select the Result cell for a student who Passed (J4).

o    Use Trace Precedents to see how their result was determined. Observe the chain of dependencies back to the raw subject scores.

o    Clear arrows.

4.     Grade Calculation:

o    Select the Grade cell for a student who received Grade B (I5).

o    Use Trace Precedents to understand which percentage value led to that grade.

o    Clear arrows.

5.     Debugging Challenge:

o    Intentionally make an error in one of your formulas (e.g., accidentally delete one subject score from B4, making G4 show #VALUE!).

o    Use Trace Error to pinpoint the source of the error. Once identified, correct the error.

o    Clear arrows.

7. Assessment / Deliverables

·         Submit the Excel workbook (.xlsx file) with your Marksheet.

·         Provide screenshots for Exercise 1 and Exercise 2, clearly showing the tracing arrows.

·         Be prepared to demonstrate to your instructor how to use Trace Precedents, Trace Dependents, and Remove Arrows, and how to interpret the tracing arrows.

·         Be able to explain how these tools are useful for auditing and debugging spreadsheets.