Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.
The Formula Auditing section of the Formulas tab gives you a visual of which cells are involved in a specific formula. This would be useful for more complex formulas or perhaps for crowded worksheets.
In our example, we will use the same worksheet from the Function Library and Defined Names tutorials: Golf Scores for 2019 Season.
We are going to click on cell B13 which contains a formula and select the Trace Precedents command. As you can see in the image below, B13 is the average of cells B3 through B11. The trace precedents command places a line on the cells involved with the formula with an arrow on the ending result.
To remove the arrow or precendents, simply select the Remove Arrows command in the Formula Auditing section.
Now, we will select cell K13, which is the total average for all of the players’ scores for the 2019 season. We want to know which cells are affected by the value of K13, so we will select the Trace Dependents command in the Formula Auditing section. As you can see in the image below, cells L3 through L11 are impacted by the value in K13. This means, if the value in K13 changed, the value in cells L3 through L11 would change as well.
Again, to remove the arrows or dependents from your worksheet, select the Remove Arrows command.
Another useful command in the Formula Auditing section is the Show Formulas command. By selecting Show Formulas, each cell containing a formula will now show the formula versus the value. This might make sense if you needed to locate your formulas or print out your formulas for reference. When we select Show Formulas, this is what our worksheet looks like:
Select Show Formulas again to turn the formulas back into values.
The final two commands in the Formula Auditing section are Error Checking and Evaluate Formula. Use Error Checking to check for common errors that occur in formulas. Evaluate Formula will show the Evaluate Formula dialog box to debug a formula by evaluating each part of the formula individually.
This concludes the Formula Auditing tutorial.
Click next to continue to the Excel 2010 Calculation tutorial.