Key Features to Compare Auditing Tools
Formulas and Structure
- Explicitly shows how a formula has been
copied
Ie. whether from above or from the left. One should not have to
guess, guessing overlooks errors.
- Shows structure on the actual spreadsheet
Working with separate maps is awkward.
- Maintains and restores any existing cell
colors
The spreadsheet may already use colors to separate assets from
expenses, good customers from bad ones, validation etc. It is
important that a tool does not damage this preexisting colouring.
- Understands skipped formulas
It is common place for a subtotal row to cross the normal flow of
copied formulas. It is important that a tool recognizes that the
formulas that follow the subtotal are just copies. (Eg. Speckled
shading in [SDetective_Help.xls]Shaded!I37.) - Provides an effective, condensed Map
- Really understands array formulas
Especially single cell array formulas, as shown in the Formulas
worksheet. These can be very misleading otherwise. (Try
documenting the Formulas worksheet with other tools!)
AutoNames or "Natural Language"
- Has an effective way to describe A1 references
In a large spreadsheet, referring to the wrong cell can be very hard to
find if all you can see is "Foo!G137 + Bar!D321"
- Works well with non-standard formatting
Real spreadsheets often contain sub-blocks of formulas and
labels. A lot of care has been put in to the Detective's
algorithms to pick the right cells for labels. Eg. it knows 2005
is a likely date, but not 5002. Labels that occur in the middle
of spreadsheets should generally be handled correctly.
- Method to override AutoName creation
No heuristic algorithm will be perfect, the Detective lets you manually
override AutoNames if necessary.
Named Ranges
- Method to really validate Named Ranges
Not just provide a list of them and their A1 definitions which is
difficult to review. They need to be directly related to the
spreadsheet to make errors obvious.
- Manipulate Named Ranges
In particular, to be able to rename them and have formulas that use
them automatically be updated.
Precedents & Dependents
- Conveniently trace data flow from inputs
to outputs
Especially across worksheets.
- Look ahead to avoid navigation
It should be possible to see a cell's direct precedents and dependents
without having to go there.
- Highlights unreferenced cells
Makes cells that are not referenced by any unique formula obvious.
Other Features
- Compare Workbooks and Worksheets
With sensible handling of inserted and deleted rows and columns.
- Large Scale Model Overviews
To see how data flows between worksheets in complex models. - Can Discover workbooks and the workbooks they depend on.
- Sensitivity Analysis
Many errors can be highlighted by looking at a model's first derivative.
- Detect Common Errors
Without getting into the mind reading game, some constructs are
obviously dubious. Eg. a Sum that is adding up non-numeric cells.
Features Missing in the Spreadsheet Detective
- A Mature Product
The Spreadsheet Detective is a mature, fully featured product.
But we are always interested to receive requests from users for even
more power.
www.SpreadsheetDetective.com