Key Features to Compare Auditing Tools
Formulas and Structure
- Explicitly shows how a formula has been copied.
- Can shows structure on the actual spreadsheet.
Working with separate map reports is awkward, especially when editing
- 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.)
- Includes a Map that provides an effective, condensed view of a larger worksheet.
Many models have more columns than can be displayed at once, even with
- 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!)
- Understands the new Spilled formulas.
- Identifies and highlights isomorphic formula copies in
larger models. (The Spreadsheet Detective uses bold and
Analyse Individual Formulas
- Has a mechanism to understand the current formula without
having run a report or start a special dialog. (The Spreadsheet Detective's formula dialog does this.)
- Provides a quick and efficient mechanism to navigate to a
formulas precedents, and their precedents' precedents recursively.
- Provides an effective mechanism to navigate formula
dependents. It is important to recognize copied formulas that
refer to a given cell (via an absolute reference) and show them as one
reference, not many.
- Provide a semi-frozen state so that one can easily review all the precedents of a formula without losing context.
- Evaluate each sub expression within a formula in order to see how complex formulas have been calculated.
- Indicate which portions of conditional (IF) formulas are being used.
- Also provides a Natural Language facility that makes
navigating to each cell's precedents unnecessary.
- Be able to graphically show precedents and dependents of
the active cell (audit arrows).
- Be able to show how a formula has been copied throughout 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 in the report, do not show them
all as individual changes.
This is important. One formula changed and then copied twenty
times is just one change. Without this the report becomes unmanageable.
Do not show differences in formulas that are just caused by referencing cells before inserted or deleted rows or columns.
- 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.
- 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.
- Show which parts of an individual formula have changed. This can be important for larger formulas.
- Show changed formulas graphically on the Map report.
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.
- Enable the source of truth to either be the actual spreadsheet or the report.
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.
VBA Macro Assistance
- Provide a report of all VBA code, including that which might be hidden in worksheet classes.
- Report on complex structures that might update data outside the spreadsheet.
- Report on constructs that could indicate poor coding style.
- Enable all macros to be exported and imported.
- Enable changes in macros to be compared.
Scenarios / Monte Carlo
- Provide a mechanism to test a model against various scenarios.
- Many modeling issues come to light when different inputs are provided.
- Most users do not need a fully featured simulation package, but they do need a simple one.
- Enable a workbook to be marked as a template that is then copied into Instances and modified.
- Enable formulas modified or created in the Instances to be listed
without listing the formulas that came from the original template.
- This enables Instances to be reviewed effectively, without being distracted by a complex template.
- 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.