It's difficult to imagine a business today — even a small business — that's run effectively without the use of spreadsheets. We use spreadsheets to track and report financial status, to forecast business performance, to model business processes — even to do engineering computations. Yet with all their uses, little is written about how to ensure that what is captured in a spreadsheet is correct, or that it is what we meant to capture.

To address the analogous problem in the software industry, software engineers conduct reviews and inspections, which are now recognized as an effective way to increase quality, enhance reliability, and manage costs. Much has been written about the advantages of reviews and inspections for software engineering: see the Web site of the Software Engineering Institute for a valuable overview.

Although reviews and inspections aren't universally used, even in the software industry, organizations that do employ reviews and inspections are typically the leaders in quality and innovation.

A review or inspection of a spreadsheet entails a detailed examination of every one of its features — all constants, all macros, all styles and formatting, all formulas — everything. A small group of no more than eight knowledgeable colleagues conducts the review. Their task is to identify issues to be addressed by the creator of the work product.

Below are some FAQs (frequently asked questions) about reviews in general, and some additional specifics about reviews and inspections for spreadsheets.

• What is a review? What is an inspection?
• Why do reviews and inspections work?
• Who decides when to review or inspect a work product?
• What roles are needed to conduct a review or inspection?
• What is the output of a review or inspection?
• Who sees that issues are resolved?
• What is the role of management?
• Won't reviews just create more bureaucracy?
• It might work for software, but aren't spreadsheets different?

A review of a work product is a structured discussion of its purpose, contents and presentation. It's conducted by a small group of colleagues whose sole responsibility and focus is the completeness and correctness of the work product relative to a predefined set of standards.

An inspection of a work product is a review that is a more detailed look at the elements of the work product. It may cover less territory, or fewer of the dimensions of the work product. For example, it might cover only the formulas and constants, but not the formatting of a worksheet.

Organizations that make a regular practice of reviewing and inspecting work products find these benefits:

• They uncover issues, omissions and deviations from guidelines or standards early in the development of the work products and their host projects. Early discovery reduces the cost and frequency of repair work.
• Work products that are reviewed adhere more closely to standards of style and design, which makes it easier for anyone in the organization to understand how the work products work and what they're intended to do.
• The people who participate in reviews and inspections learn about how the things they review are designed and intended to work, which makes them more valuable to the organization because they know more. They also learn new techniques that they can apply in their own work.

The answer to this one is unequivocal: the author. Not management, not the project leader, not the schedule — the author. It's important to leave this decision to the author to guarantee that "we review no product before its time" and to ensure that we do not waste the time of the reviewers.

The review team is led by a Moderator, who is responsible for recruiting the team, finding a meeting room, setting a time and date, and running the meeting itself. After the meeting, the Moderator works with the author to resolve any issues that are uncovered in the review.

A Scribe is responsible for recording each of the issues uncovered by the review team, and for distributing the issues list immediately after the review. The rest of the review team members are Reader/Reviewers. They're responsible for reading the material prior to the review, for identifying issues in advance, and for reporting them during the review meeting, along with any other issues they might uncover then.

The output of a review or inspection is a list of Issues — items that the team considered worthy of note. They may be major issues or minor issues. When minor issues are repaired, no further review is required — they're considered to have been resolved.

Major issues are a bit trickier. If the review team thinks some issue or issues are important enough, it can require that the product reenter review when the issues are resolved. Alternatively, the team can decide that further review isn't necessary, as long as the issues are resolved.

So the output of a review or inspection is twofold:

• An Issues list, with each issue categorized as Major or Minor
• A recommendation as to the necessity of reconvening the review once the Issues are resolved.

There are at least two possibilities:

• The Moderator can work with the author to make sure that all issues are addressed.
• You can designate an "issues bloodhound" [Freedman 82] to follow up on the author's resolution of the issues.

Whatever you do, make sure someone other than the author has this responsibility. Most important: the issue resolution monitor should not be affiliated with the team that produced the work product.

Management's support is critical to the success of any program of reviews or inspections. For example, management can assist in making review efforts a priority high enough that the members of the review team can set aside time to execute the review. That said, what about the question of active management participation in a specific review or inspection? Generally, management should not take any role in any specific review or inspection. Even mere presence in the room can be problematic.

Often, management has a conflict of interest relative to participation in a review or inspection. That conflict can arise from responsibility for personal performance reviews, from sponsorship of the product, or from affiliation with those who have such responsibilities.

Even if a manager feels personally capable of resolving these conflicts of interest, the other members of the review team may feel certain inhibitions in the presence of management. Such inhibitions inevitably degrade the quality of the review output. And since it's difficult to determine whether or not team members feel these inhibitions, the presence of management in a review creates an unmeasurable risk.

A reasonable question — bureaucracy is a thing to be avoided. So let's think about this carefully. What is really bad about bureaucracy?

• It adds cost without adding value.
• It slows us down without justification.
• It frustrates those of us who are just trying to do our jobs — to get real work done.

Whenever we add formality to any process — whenever we add controls — we run the risk of adding bureaucracy. But the way we're describing reviews, the risk is minimal. Control of reviews is left in the hands of those executing them. Since there is no direct management involvement, review issues remain local to the review team. This makes it difficult for the review process to drag out, to frustrate the author or the author's team. Even so, since the review team itself could be viewed as an element of bureaucracy, let's examine the "three negatives" of bureaucracy itemized above.

Does the review process add cost without adding value? I think not. The added value is clear — issues are identified early, at a time when they can be fixed at much lower cost than otherwise. Does it slow things down without justification? It does indeed slow things down, but with justification — reduced issue rates. Does it frustrate us? At first, it will seem frustrating, especially to authors. But as we experience its benefits, we come to welcome the reviews, because they save us from the even more frustrating experience of having to correct a design after it's implemented.

As an example of this saved frustration, imagine this scenario. You're a general contractor building a home. You have a choice — you can bring in the city inspector to review the interior wiring either before you install the wallboard, or after. If the inspector finds a problem after the wallboard is installed, you'll have to rip out the wallboard to fix the wiring, then reinstall the wallboard. If the inspector finds the problem before you install the wallboard, then you just fix the problem. Now which way would you rather do it?

Those of you who say "Why bother with the inspector?" might want to consider what happens to your construction business if there is an undiscovered wiring problem and the house bursts into flame two weeks after you turn it over to a customer.

Certainly spreadsheet applications, such as Excel, are software. But the spreadsheets you build yourself, using spreadhseet applications, are also software. They are indeed a different kind of software, but they are software. When a spreadsheet is reviewed or inspected, it's compared to a standard of quality. That standard is represented by a checklist of properties that the spreadsheet should have, and not-properties that the spreadsheet should not have. These properties and not-properties can vary from organization to organization, or they can even vary within an organization depending on the work product. I've prepared a sample of desirable and undesirable properties, in the form of a checklist for spreadsheets.

A few simple commands are handy when reviewing a workbook. All are available from menus, but knowing the keystroke equivalents helps a lot.

Excel provides a variety of tools for ensuring reliability. Although we won't discuss them, they include data validation, examination of precedents and dependents, and tracing. You can read about these topics in online help. Tracing is especially useful. Use the menu command Tools>Formula Auditing>Show Formula Auditing Toolbar (Excel 2003), or Tools>Auditing>Show Auditing Toolbar (Excel 2004) to expose the Auditing Toolbar. In Excel 2007, the tools are on Formulas>Formula Auditing.

Note:

In Excel 2004 for the Mac, the command Tools>Auditing>Show Auditing Toolbar is disabled (grayed) if no workbook is activated, or if more than one sheet is selected. If you see that the command is grayed, verify that a workbook is activated, and that only one worksheet is selected.

Inspecting names in a workbook can be time-consuming, if you're restricted to using the name definition dialog box to examine the names. Part of the problem is that the dialog box truncates long names, which makes it impossible for you to see them in their entirety. But there is a way to paste directly into a worksheet a list of all names visible from that worksheet. This includes both local and global names, although the command provides no way to distinguish them.

Begin by selecting a cell that will be the upper left corner of the pasted list. Since you never know how many names there might be, choose a cell that is at the bottom of the used area of the worksheet, so that the pasted list won't overwrite worksheet data. Then choose the menu command Insert>Name>Paste… and click Paste List in Excel 2003 or 2004. In excel 2007, use the command Formulas>Use in Formula>Paste Names.... A list of names and what they refer to is then inserted into the worksheet.

In addition, the file report.xls, which you are to use for reporting issues for Problem Set 7, contains some tools for inspecting a workbook.

When you inspect a spreadsheet, you have to look at lots of detail. It's impossible to remember what to check. A checklist for reviewing worksheets includes all aspects that you want to make certain are right.

Naturally, it can vary from organization to organization, and even from project to project. This checklist is therefore just an example of what might be one organization's list of checkable items, but it's the one we'll use for the homework on reviews and inspections.

The sheet's name begins with an alphabetic character. It can contain numbers, letters and the period (.) character. No other characters are allowed. Each word component of the name follows a pattern of initial upper case letter, followed by lower case letters or numbers. Sheet names are 10 characters long or fewer.

All range names begin with an alphabetic character. Names can contain numbers, letters and the period (.) character. No other characters are allowed. Names are in mixed case.

For parameter names, each word component of the name follows a pattern of initial upper case letter, followed by lower case letters or numbers.

Range names use these abbreviations:

Links across sheets always use named ranges. There are no cross-sheet links using explicit cell references.

Row captions are right justified, unless they caption an empty row. If all other cells in the row are empty, the row caption need not be justified. Row caption cells must be directly adjacent to the first cell of the data they caption. Row captions are in mixed case.

Column headings are centered, in mixed case.

Numeric data cells are right aligned or general aligned. Numeric caption, comment or heading cells can have any alignment, except as required by other checklist items.

Dollar amounts are formatted as $1,000. No pennies are indicated except when the precision is required for meaning, as in the per piece cost of an item or product.

Percentages are formatted as 27%.

No underline or strikethrough font styles.

Linked blank text displays as blank, not 0.

Worksheets are spell-checked and corrected.

No worksheet cells contain error values.

There are no numeric constants in formulas, except in parameter cells, parameter arrays, or arguments of index or offset or similar situations.

All numeric constants are held in named cells, and referred to by name. An exception is granted for cells containing 0.

All total cells sum the "correct" ranges.

Array formulas are used where possible. The set of those cells that should be in the array are considered to be a single occurrence.

There are no defined names that no longer exist (#REF!).

There are no circular reference errors.

As a manager, you'll probably want to begin a practice of reviewing and inspecting your spreadsheet models. If you try it, you'll run into several problems. Changing the modeling process is the most significant difficulty, followed by the relatively simple task of tracking the data that the inspections produce.

The issue of organizational change arises whenever you change anything. It's a serious issue, and it isn't a topic for this course. We do have something to offer though, to address the problem. Your chances of success in introducing inspections in your organization are greatly enhanced if you provide the right tools to facilitate the inspections. One of those tools is something like the file report.xls.

This file provides each of the reader/reviewers with a convenient place to note all of the issues they find. It contains a worksheet for each worksheet in the workbook they're inspecting, as well as a complete copy of the checklist.

In our copy of this tool, we also have to provide a way of scoring your work, so there are some tools for that. These tools aren't needed in a real inspection.

The key to a successful inspection is a systematic approach. Two approaches are possible. In the first approach, you inspect every worksheet thoroughly, checking it against all items in the checklist. In the second, you inspect every worksheet for each item of the checklist, one checklist item at a time. It turns out to be much more efficient to concentrate on one item of the checklist at a time, examining every worksheet in turn.

To begin, fill out the Summary page of the inspection workbook. This page includes all workbook issues, and some information about the workbook you're inspecting. You'll need to fill in the name of that workbook, and your own name, in the indicated cells. Put a copy of the workbook you're inspecting into the same directory that holds the inspection workbook. Then load that file into Excel, and inspect it for workbook issues.

When you find a workbook issue, indicate it by entering the name of the worksheet on which it occurs, either in the "first occurrence" column if it's the first of that issue type, or otherwise in the "additional occurrences" column. If you have more than one entry in an "additional occurrences" cell, separate them by commas.

This procedure is the same for each issue, but I'll describe it here for just one issue. Decide which issue you're inspecting for, and select the first worksheet's inspection page in the inspection workbook. When you click the Go button, Excel activates the corresponding page in the workbook you're inspecting. When you then press Command+M (Mac) or Ctrl+Tab (Windows), Excel takes you back to the inspection workbook. Using these two commands, you can hop back and forth between the two workbooks.

Once you've found a range in which there is an issue, select it, and press Command+M (Mac) or Ctrl+Tab (Windows) to return to the inspection workbook. Make sure that the active cell is someplace in the row that corresponds to the issue you're focusing on. Then click the button Insert Issue References to insert the locations into the "First Occurrence" and "Additional Occurrences" cells.

After you've made the entry, you can go back to the worksheet you're inspecting with the Go button, and return, using Command+M (Mac) or Ctrl+Tab (Windows), to enter what you find. When you're finished with that sheet, click Next to move onto the next sheet. Clicking Prev takes you back one sheet.

If you find no occurrences of the issue, enter "None found" in the Comment column, so you'll know later that you did inspect for that item. If you find occurrences that pertain to names, enter those names in the appropriate columns.

Freedman 82

Freedman, Daniel P. and Gerald M. Weinberg. Handbook of Walkthroughs, Inspections, and Technical Reviews, 3rd ed. Boston: Little, Brown, 1982.