i-nth - How to validate spreadsheets

Authors

Mark G. Simkin

There are 7 methods for validating spreadsheet models for accuracy and completeness:

Specialized spreadsheet auditing software enables the user to:

Trace circular computations by following the logic through cells that are highlighted sequentially by the software.
Identify duplicate formulas.
Document spreadsheet models by listing the cell coordinates for all named cell ranges.
Display blocks of cell formulas rather than the results of those formulas.
Trace the origin or disposition of spreadsheet values by highlighting for any given cell "precedent cells" or "dependent cells".

While validation is time-consuming, it may save time and money lost due to inaccurate data.

Some spreadsheet auditing software packages can help the user to check the accuracy of a spreadsheet by performing these edit tests:

Identifying cells or formulas that reference empty cells, cells that fall outside the active range of the spreadsheet or out-of-bounds cells.
Identifying cells that contain numeric data that aren't referenced by other cells.
Identifying predefined formulas or other mathematical expressions that use text labels as arguments.
Identifying string formulas that use numeric data as arguments.
Identifying "range wraps" - which happen, for example, when formulas in spreadsheet rows that are moved to the top of a spreadsheet accidentially end up referencing cells at the bottom.
Identifying specific cells that contain ERR messages.
Separating those ERR messages that are carryforwards of other errors from ERR messages that signal other types of programming problems.
Identifying cell functions whose arguments include cell ranges in other rows or columns.
Identifying named cell ranges that overlap.

1987, Journal of Accountancy, Volume 164, Number 5, November, pages 130-138

Not available