Excel Formula Auditing

Tips for improving data quality through Excel Formula Auditing

By Jeff Robson

Excel formula auditing is important!  If you regularly create or review spreadsheets, you’ll know just how many errors there can be!

Experts estimate that on average, about 80% of complex spreadsheets contain some kind of material error.

So if you’re relying on these for important decisions, you really need to get them properly reviewed or audited. If you’re wanting someone to do this for you, just contact us. If you’d like to do this process yourself, here is a great tip that will help you.

Excel Formula Auditing

If you need to analyse formula and track the links to and from a cell use the Excel Formula Auditing Toolbar.
In Excel 2003 you can view the toolbar by right clicking the toolbar and selecting Formula Auditing. In Excel 2007+ it appears in the Formula Ribbon tab.

Excel formula auditing ribbon

The toolbar buttons allow you to see all the cells that link to a cell (Trace Precedents) and displays all the cells that this cell links to (Trace Dependents).

You can double click the audit lines to follow the links. The lines that are shown can be removed with the Remove Arrows toolbar icon.

Excel formula auditing example

The Evaluate Formula toolbar icon (at the end of the Excel Formula Auditing toolbar) allows you to investigate a single formula.

When you use it the Evaluate button calculates the selected part of the formula. The Step In and Step Out buttons allow you to follow the links and see other formulas in the other cells that link to the cell in question.

Excel formula auditing example

More Powerful Excel Formula Auditing

If you’re auditing very complex formulas, you may want an even more powerful Excel formula auditing tool. We suggest taking a look at Spreadsheet Detective.  It’s inexpensive and there’s a free evaluation version.

It’s simple to use, allows drilldown into formulas, and makes it easy to trace formulas through multiple sheets … plus a host of other features!

Get Help

If you’d like someone to do this complicated, detailed process for you, just contact us and we’d be happy to have a confidential discussion about your project.