A Checklist for Use in Reviewing or Inspecting 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 rathr 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 preinstalled 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 work sheet, and noticing whether or not it is the cell we expect it to be.     Top

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.

Contact me to discuss your specific situation, by email at rbrenner@ChacoCanyon.com or by telephone at (617) 491-6289, or Toll-free at (866) 378-5470 in the continental US.

---

Are you a writer, editor or publisher on deadline? Are you looking for an article that will get people talking and get compliments flying your way? You can have 500 words in your inbox in one hour. License any article from this Web site. More info