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

 Show Values - Excel Formula Auditing Tools Show Values - Excel Formula Auditing Tools  

From the Tools menu, select Formula Auditing and click Formula Auditing Mode.

Show Values or Show Formulas with Formula Auditing Mode in Excel - Excel Formula Auditing Tools

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.

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.

Trace Dependents - Excel Formula Auditing Toolbar

Trace Dependents - Excel Formula Auditing Toolbar

  • select a formula and click Trace Precedents   to display arrows showing the cells involved in the calculation

Trace Precedents - Excel Formula Auditing Toolbar

Trace Precedents - Excel Formula Auditing Toolbar

Tip 4 – Evaluate Formula to understand the order of calculation used in Excel

On the Formula Auditing Toolbar, click Evaluate Formula.

Evaluate Formula - Excel Formula Auditing Toolbar

The underlined text is what Excel evaluates (calculates) next. To continue, click Evaluate until the result is displayed.

Toolbar  Screenshot - 30_06_2010 , 22_48_49

If you found this article interesting, share it with people you know, subscribe to the site feed or browse more Excel tips