Troubleshooting formulas in Excel is easy thanks to the Formula Auditing Tools. Make sure to learn these tips and apply them as soon as today.
Video: Four Essential Tools to make sure your formulas in Excel are correct
Tip 1 – Display formulas instead of values
From the Tools menu, select Formula Auditing and click Formula Auditing Mode.
Tip 2 – Display the Formula Auditing Toolbar (and start using it more)
From the Tools menu, select Formula Auditing and click Show Formula Auditing Toolbar.
From left to right, the Formula Auditing Toolbar commands are:
- Error checking
- Trace Precedents
- Remove Precedent Arrows
- Trace Dependents
- Remove Dependent Arrows
- Remove All Arrows
- Trace Error
- Insert Comment
- Circle Invalid Data
- Clear Validation Circles
- Show Watch Window
- Evaluate Formula
Tip 3 – Find out which formulas depend on a particular cell (Trace Dependents) and which cells or values are used in formulas (Trace Precedents)
On the Formula Auditing Toolbar (see above)
- select a cell and click Trace Dependents to show arrows pointing at formulas using this cell or value.
- select a formula and click Trace Precedents to display arrows showing the cells involved in the calculation
Tip 4 – Evaluate Formula to understand the order of calculation used in Excel
On the Formula Auditing Toolbar, click Evaluate Formula.
The underlined text is what Excel evaluates (calculates) next. To continue, click Evaluate until the result is displayed.
If you found this article interesting, share it with people you know, subscribe to the site feed or browse more Excel tips
