SpreadsheetAuditing

A spreadsheet auditing tool should help analyse, assess and understand workbooks by providing essential background information. The information will help end-users make informed decisions about audit processes that they may wish to use.

At a minimal level there should be 2 trivial checks any spreadsheet should be able to make on its formulas:

1. no formula refers to an empty cell - except as part of a larger SUM() perhaps
2. no nonempty cell is unreferenced - except those that are intended as final results or displays

Ideally A well designed spreadsheet model should have a clear flow from input sheets to output sheets.

High Level Information

• System data: Excel version, paths in use, current Excel settings.
• Workbook data: File name, path to file, size on disk in Kbytes.
• Workbook information: Title, subject, author, keywords, editing time and comments. (Windows specific?)
• Worksheets: Lists of worksheets names and the area covered, number of occupied cells. Number of cells in use and total active area.
• Ranges: names and addresses.
• Links to other workbooks.
• Cross worksheet references.
• Identification and separate listing of formulas that reference other workbooks.
• Flagging of cells that are (not) referenced by any unique formula.
• Highlighting of formulas copied between different worksheets in three dimensional models.

Auditing Maps

Provide visual documentation of a spreadsheets structure and contents. Also aid in finding your way around unfamiliar spreadsheets.

One way to do this is to produce a visual overview of all the cells in use, what's in them (formulas, numbers, errors, text, etc.).

Cell categories:

• t - text string
• v - numerical value / numbers
• d - date
• b - boolean / logical values
• f - formula
• e - errors http://www.mcs.vuw.ac.nz/~db/excel.html#errors

Example from [XLAUD].

Equivalence with cells in adjacent cells (and possible non-adjacent cells). Also consider top-originating areas as demonstracted in [MSA]. This could involve showing how formulas have been copied throughout a spreadsheet based on the equivalence classes.

Example from [MSA].

Highlight areas covered by range names. (Not currently supported by toolkit)

Display a statistical summary for each worksheet. A text listing of all the range names showing which cell they begin in and the areas they cover.

Cell Roots - Given that root cells are those which don't reference other cells themselves, collectivly they have the greatest effecton the rest of the spreadsheet. Small changes in their values can yield the largest changes elsewhere. As Alex suggested, a possibly good visualisation would show how many cells are dependent on a particular cell.

Circular References can currently be found using the toolkit. It's important to know which cells are involved, where they are located, if they cross worksheets (undesirable for a deliberate circular reference), and if iterative calculation has been enabled by the user.

Links

• Hints for Building Readable, Manageable Models http://www.frontsys.com/readable.htm http://www.mcs.vuw.ac.nz/~db/reference.html?short_title=HBRMM
• Modeling Spreadsheet Audit: A Rigorous Approach to Automatic Visualization http://www.mcs.vuw.ac.nz/~db/reference.html?short_title=MSA
• The Spreadsheet Detective. Commercial auditing tool. http://www.uq.net.au/detective/home.html http://www.mcs.vuw.ac.nz/~db/reference.html?short_title=SSD
• The Excel Auditor. Commercial auditing tool by Byg Software Limited. http://www.bygsoftware.com/auditor/auditor.htm http://www.mcs.vuw.ac.nz/~db/reference.html?short_title=XLAUD
• Professional's Guide to Robust Spreadsheets. Ronny Richardson http://www.manning.com/Richardson/ ISBN 0-13262320-X (amazon.com, search)
• Quantrix and Excel: 3 Key Differences http://www.quantrix.com/r-info-2-2#wp