A Checklist for Use in Reviewing or Inspecting Spreadsheets

Software engineers use checklists to review or inspect their work products. Pilots use checklists to prepare their aircraft for flight. Checklists improve reliability of spreadsheet reviews and inspections by relieving us of reliance on memory to make certain we haven't forgotten something.

Below are some samples of items that you might use for your checklist for inspecting or reviewing spreadsheet models or tools. For your organization, some of these items are perfect just as they are, and some are irrelevant or not quite right. Use this list as a menu of items from which you can build a checklist suitable for your own organization. Keep in mind that it might be helpful to have several checklists, each designed for a particular kind of work product.

I've divided them into categories based on features of most spreadsheets.

Workbook organization and format

The product adheres to the conventions of your organization for

• Worksheet names
• Worksheet order
• Version numbering
  • It is numbered as required
  • If it uses other products, it uses the right version of each
• Summary field contents
• Copyright notices
• Style definitions

Worksheets

Page layouts

Relevant factors include

• Landscape or portrait orientation
• Use of print titles
• Positioning of page breaks
• Worksheet organization
• Print area definition

Page header and footer

Do you have a conventional header and footer? Does it include version information?

Security and protection

Are appropriate portions of the worksheet protected and locked? Does the worksheet have appropriate password security?

Version numbering

Does each worksheet have its own version number? Is its number reflected in the workbook version number?

Naming conventions and cell formats

The product adheres to the conventions of your organization for

• Row captions and column headings
• Date and number formats
• Use of named ranges
• Choice of range names
• Use of standard macros
• Use of color
• Standard abbreviations

Formulas

• All constants are stored in named cells.
• All constants are referenced by name.
• The worksheet is free of error symbols.
• All sufficiently complex formulas are used no more than once. Others are installed as macros or named formulas.
• All formulas in a given row are similar (identical in R1C1 mode).
• All cross-sheet and cross-workbook references are by names rather than explicit cell references.
• Intermediate calculations are implicit, implemented as nested calls to worksheet functions.
• Array formulas are used where possible.

Infrastructure

If any of your analysis tools require support to be pre-installed in the workbook, this infrastructure is present and correct.

For example, you may require that a portion of each worksheet be reserved for storing constants and model parameters, and that this portion have a name such as "Parameters". Or perhaps each sheet has a name "SheetType" defined to enable specific macros to determine what kind of worksheet each is.

You might want to make a list of all such infrastructure features.

Construction Waste

Sometimes, as a spreadsheet project is developed for the first time, or as it is being maintained, we introduce into the worksheet or workbook certain artifacts that have no useful role in the completed project. For example, we might define a style, and then decide later not to use it.

Just as it is important to remove such "construction waste" from the work site after we construct a building, we should remove it from our spreadsheet work site. Benefits of this cleanup include reduced file sizes and memory footprint, and exposure of unexpected problems. For example, we might find that a particular cell far outside the actual used area of a worksheet has a format defined for it, even though it is empty. This makes the used area of the worksheet much larger than it needs to be. We could check for this by selecting the last cell of the worksheet, and noticing whether or not it is the cell we expect it to be.     Top

Contact me

Would you like to know more about reviewing or inspecting spreadsheets in your organization? Could you benefit from some expertise in developing checklists specifically for your needs? Through consulting, workshops, or coaching, I can help your people learn to use reviews and inspections to reduce defects, reduce effort, and increase reliability of spreadsheet work products.