How many times have you found cells with errors or the famous ##### entry? These can all be attributable to errors or improper formatting. In this tutorial we are going to focus on the error part. In Excel you have the ability to compose simple formulas that add a list of numbers or you can create formulas to determine the loan balance of a mortgage after 35 payments. When we start to create complex formulas that span several lines it can become quite cumbersome to track errors or problems with said formulas. Luckily Excel 2010 and previous versions of Excel ship with a slew of powerful tools to help us catch those errors and correct them.
Below I will highlight the 6 main tools found in the Formula Auditing Group in the Formulas tab and show you how to use them to discover and correct errors with the formulas you’re working on.
If you have any comments or questions, please post them below.
Note: I used Excel 2010 for this tutorial.
1. To use the Auditing Tools in Excel click on the formula you wish to audit.
2. Click on the Formulas tab in the Office Ribbon.
A. Use one of the tools in the Formula Auditing group. Don’t worry we are going to go over each of these tools below.
3. When you click on the Trace Precedents tool for a selected cell (containing a formula), a set of arrows will appear pointing to the cell. These arrows indicate all of the cells (a), (b), (c), (d), (e) that are included in the formula for the selected (A) cell. These are the “precedentsâ€. This helps you determine where the values that make up your formula come from. You can always figure out which cells the formula relies on by viewing the formula, but this is much more appealing for the visually inclined.
4. The Trace Dependents tool tells you which cells rely or are dependent on the selected cell. This is a great tool when you’re going through a spreadsheet deleting unnecessary information, it can help you determine which cells matter and which don’t. Note how the cell labeled as (A) is a dependent for Cells (a) and (b).
5. If the number of arrows starts to clutter the spreadsheet while auditing formulas you can always click on the Remove Arrows button.
6. Select which arrows you wish to remove.
- Remove Arrows – Removes Precedent and Dependent Arrows.
- Remove Precedent Arrows.
- Remove Dependent Arrows.
Screenshot of cells in normal view.
7. When you click on the Show Formulas button all the cells containing formulas will be displayed.
8. In order to make sure that the formulas you are using are all correct you can click on the Error Checking button.
9. When the Error Checking window opens you will be able to locate any formulas with errors or problems.
You can then go a step further and use the Evaluate Formula tool to evaluate the formula step-by-step.
10. Click on the cell containing the formula you want to evaluate.
11. Click on the Evaluate Formula button. A window will open.
A. You have the option to Evaluate the formula or Stepping In a formula. Both options are a good way to visualize what exactly is occurring in the formula you are auditing.
Evaluate – Will show you each calculation step as it is made within a nested formula.
Step In –The Step In button is great to evaluate a reference in a formula that points to another formula.
