How to Make Spreadsheets Error-Proof

Making those handy number-crunches more productive. Commercial spreadsheet application programs probably are accountants' most widely used computer tools. But how accurate are the numbers they generate? While the underlying programs that run spreadsheet software are flawless, are the myriad custom formulas that users build into a typical spreadsheet equally sound? Research by a major accounting firm found that over 90% of spreadsheets larger than 150 rows contained at least one significant formula mistake. And it takes just one small error--a single misplaced code--to produce wildly erroneous results. Such errors can be devastating because the data often are the foundation on which many organizations base their key decisions. It's possible to make spreadsheets not only virtually error-proof, but easier to use and more understandable. This article tells how it's done. Unlike other information system programs, which typically are developed through extensive design and testing, spreadsheet files are mostly cobbled together on an as-needed basis by ordinary users. As a result, most spreadsheet files are relatively inefficient, difficult to use and hard to maintain. Also, since they generally lack the inherent controls and audit trails typically found in commercial information systems, there is no way to ensure the integrity, of their data. For example, what controls normally prevent the entry of invalid data or the accidental overwriting of a formula cell with a number? Usually none. PLAYING THE ODDS Using such a spreadsheet is much like playing Russian roulette. While it may work fine most of the time, at some stage, when that minor, undetected error emerges, it's going to produce a wrong answer--and it's unlikely that anyone is going to catch it. But it doesn't have to be that way. There are techniques that even ordinary spreadsheet users--those without special programming training--can apply to improve the quality of spreadsheets and reduce--if not eliminate--the risk of errors. All the examples shown in this article are generated in Microsoft's Excel, but other spreadsheet applications have similar functions. Let's look at some of those techniques. Basic design. When laying out a spreadsheet, divide it into three areas--input, calculation and output. Each of these areas is discussed in more detail later. They should be laid out diagonally so they share no common rows or columns, as shown in exhibit 1, below. Why use this structure rather than a conventional vertical or horizontal design, where all data and formulas are arranged either side-by-side or one under the other? Because in a side-by-side or stacked arrangement, adding or deleting rows or columns invites problems. For example, if you are not careful when you delete or add a row' or column, it could affect all the data in those rows or columns. An alternative technique is to exploit the three-dimensional capabilities of today's popular spreadsheets--using multiple worksheets in the same file. While this technique requires more complex formulas (because they must refer to cells in other worksheets), the payoff in security often is worth the effort. Here's how to set up the input area: Begin by dividing it into two discrete parts. One should contain data that change infrequently or never; the other should contain data that a user will change regularly. The input area should not contain formulas or calculations. This helps overcome one of the major problems with spreadsheets-- version control. This design also addresses an inherent shortcoming of spreadsheets: They were not designed to keep an audit trail of changes to data. But if you maintain a separate data-only input area, you can extract and save this section; in effect you produce your own audit trail. To enable easy identification of different spreadsheet versions, headers or footers on printouts should show the date and time of printing. …