Key Features to Compare Auditing Tools
Formulas and Structure
- Explicitly shows how a formula has been copied.
- Shows structure on the actual spreadsheet.
Working with separate map reports is awkward, especially when editing
- Identifies and highlights isomorphic formula copies in
larger models. (The Spreadsheet Detective uses bold and
- Maintains and restores any existing cell
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 coloring.
- 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
formulas that follow the subtotal are just copies. (E.g..
shading in [SDetective_Help.xls]Shaded!I37.)
- Provides an effective, condensed view of a larger worksheet.
Many models have more columns than can be displayed at once, even with
a zoom that is still legible.
- Really understands array formulas.
Especially single cell array formulas, as shown in the Help Formulas
worksheet. These can be very misleading otherwise.
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 "G137 + D321" instead of "Costs +
- Does not require Named Ranges or other constructs that are
not directly tied to the spreadsheet model. Things that
cannot be directly seen quickly degrade as a model evolves.
- 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
algorithms to pick the right cells for labels. E.g. it knows
is a likely date, but not 5002. Labels that occur in the
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.
Non-Modal Formula Dialog
- Has a mechanism to understand the current formula without
having run a report or start a special dialog.
- Provides a quick and efficient mechanism to navigate to a
formulas precedents, and their precedents' precedents recursively.
- Also provides a Natural Language facility that makes
navigating to each cell's precedents unnecessary.
- Also provides an easy mechanism to navigate to a formula's
Dependents. Make cells that are not referenced by
any distinct formula obvious.
- Provides a mechanism to see referenced cell's values
without having to navigate to them.
- Be able to graphically show precedents and dependents of
the active cell (audit arrows).
- Be able to show how a formula has been copied thoughout a
spreadsheet dynamically as well as in reports.
- Be able to assist with updating copies of formulas that
need to be kept consistent.
- Provide a comprehensive set of tests that look for dubious
constructs and highlight them.
- Take care not to highlight commonly correct but unusual
structures. For example, it is common to include an empty row
at the end of a range that is being Summed, but not in the middle.
- Detect subtle issues such as common techniques to
fraudulently hide values.
- Show test results both graphically and in reports.
- Handle inserted and deleted rows and columns properly.
Do not assume, for example, that all labels are in the first row and
- Provide a clear, graphical mechanism to be able to identify
what has changed.
It can be very difficult to interpret comparisons otherwise.
- Also provide a report of differences that makes it easy to
find them in a larger model.
- Group changes to copied formulas, do not show them
all as individual changes.
This is important. One formula changed and then copied twenty
times is just one change.
- Normally only show changes to formulas and input values,
not derived values.
Changing one input may cause hundreds of derived values to change,
making it impossible to see the wood from the trees.
- Provide a report of differences that makes them easy to
- Show changes to named range definitions as well as formulas.
(But not if just a result of inserted/deleted rows/columns.)
- Be able to compare whole workbooks, individual worksheets
or regions within a worksheet.
Models often have repeated chunks within them, so it is important to be
able to compare within a model as well as between them.
Audit Process Control
- Provide a way to mark formulas
as Correct, Incorrect, Dubious or Outstanding.
- Allow incorrect formulas to be grouped with tags. Often a single modeling issue will affect several formulas.
- Provide graphical report of
marked cells. Both detailed worksheet and condensed maps.
- Provide a report of all
formulas that need examination, and their current status.
- 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 any errors obvious.
- Manipulate Named Ranges
In particular, to be able to rename them and have formulas that use
them automatically be updated.
- Large Scale Model Overviews
To see how data flows between worksheets in complex models.
- Provide safe workbook security.
not get caught out by a security penetration. Models need to
encrypted, but in a way that guarantees that you can decrypt them in
years to come.
- Discover workbooks and the workbooks they depend on.
It can be difficult to know what is connected to what in large folders
- Sensitivity Analysis.
Many errors can be highlighted by looking at a model's first derivative.
- Provide convenient worksheet navigation in large workbooks.
And utilities for changing properties of multiple sheets at once, such
as worksheet protection.
Features Missing in the Spreadsheet Detective
- The Spreadsheet Detective is a mature, fully featured
But we are always interested to receive requests from users. The more we do the more that can be done.