Getting the Oops! Out of Spreadsheets

preadsheets, the lingua franca of the world of business, are pressed into service not only to describe a company’s financial history but also to tell its future—which is why an error in even one spreadsheet cell can be disastrous. Yet, as CPAs know only too painfully, most spreadsheets do contain errors. Although some may be small and initially appear insignificant, even the tiniest slip can grow into a totally erroneous financial picture as the spreadsheet program computes data and performs further calculations based on that one small error. Making matters worse, many spreadsheets are templates, or models, to which users continually add information. If the original contains an error, each new data input amplifies that original error.

So what’s one to do?

While there are ways to ferret out and correct most errors—and this article illustrates some of the most effective ways to do that (using Microsoft’s Excel 97, version 8.0 to illustrate the steps)—CPAs should be aware that no foolproof solutions exist. At best, errors can be minimized using the processes shown in this article, so the prudent user should stay alert to the danger and employ all available tools to find them.

CHECKING THE RANGE

Excel makes it possible to validate input data—that is, perform input range checks—even without writing sophisticated software code. To perform a data validation, place your cursor in the target cell and click on the Data button on your menu bar and then on Validation. A dialog window opens with three tabs: Settings, Input Message and Error Alert. By selecting Settings, you can choose the data type (whole number, date, time) you want to validate and the upper and lower ranges for the input value. In addition to fixed numbers, you can use Excel formulas, so if you want to enter one of the last seven years you can use the formula =YEAR(NOW())-7 in the Minimum text field and =YEAR(NOW())-1 in the Maximum field .

Then click on the Input Message tab and enter the message you want displayed when the cell is activated.

If you want to mark all cells in the spreadsheet for which input validation rules are defined, click on Edit, Go To (shortcut key: F5). Then click on Special and, in the next dialog window, select Data validation. You can choose between All (which selects all cells containing any input validation) and Same (which selects only cells containing exactly the same input validation as the active cell).

It’s also possible to copy only the input validation rule from one cell to another. To do this, select the cell containing the rule you want to reproduce, copy it (Control + C), go to the destination cell and click on Edit, Paste Special and Validation. In that way you can store some useful input validation rules for later use; they can even be used in other sheets or workbooks.

NAMES AND COMMENTS

Another way to minimize errors—and also improve readability—is to add explanatory comments to key cells (Shift + F2) and use cell names (Sales, for example) instead of G5. The shortcut key to cell names is Ctrl + F3. If your comment doesn’t appear after you’ve created it, click on Tools, Options and View; then, in the Comments section, set the visibility of cell comments to either Comment indicator only (producing little red marks in the upper right corner of commented cells and comments that appear only when the cursor passes over those cells) or to Comment & indicator (both the little red marks and comments that appear all the time .

ROUNDED NUMBERS

Another frequent source of problems is the rounding option. Although rounding off numbers doesn’t alter the underlying numerical code (so subsequent calculations are not compromised), rounding affects how numbers are displayed. The confusion arises when some numbers are rounded off and others are not or, worse, when different cells display the rounded-off numbers in different ways.

Therefore, when building spreadsheet models, it’s best to deselect the option to round off numbers. Follow this rule: Always use a consistent number of decimal places (say, two rather than one) and employ commas to separate thousands—”#,###.##.” Failure to maintain decimal consistency can produce irritating—and sometimes very confusing—results.

EXAMINING A SPREADSHEET

Every spreadsheet—especially a template—should be audited for errors very carefully. Begin by reviewing the formulas. One easy way to do this is to use this menu command sequence: Edit and Go To. Then click on Special and select Formulas, deselecting the options Numbers, Text and Logicals while leaving selected only the option Errors.

If, after clicking OK, a dialog window reports, “No cells were found,” you can at least be assured that the spreadsheet contains no trivial syntactic formula errors—that is, formulas referencing text entries or something similar. But if you receive an error message, search out its source. Excel has audit tools to track down errors.

Warning. The Auditing function appears to be far more complicated than it really is. If the following description leaves you confused—and anxious about even trying it out—you should reconsider that decision. If you jump in and test the function, you’ll probably find, to your surprise and delight, that much of it is quite intuitive. As with many intuitive processes, the steps are hard to explain, but the process becomes clear when you watch it being done. For example, try to follow verbal directions for tying a bow; it’s so much easier to learn how to do it by seeing someone tie a bow. So, read the following and resolve to test it in a spreadsheet: It’s likely both your questions and your anxiety will evaporate.

Tip. If you find yourself using the Excel audit function often, you can save time by opening the Auditing toolbar; clicking on Tools, Customize and Toolbars; and placing a check in Auditing. If you use it less often, go to Tools, Auditing, Show Auditing Toolbar; this puts the Auditing toolbar on your screen just for the current session.

The nine functions of the Auditing toolbar are (left to right) trace precedents (that is, locate the source of data in the selected cell), remove precedent arrows,  trace dependents (data subsequently calculated by the data in the selected cell), remove dependent arrows, remove all arrows, trace error, insert new cell comment, circle invalid input data (in cells containing data that don’t conform to their validation rules) and clear all validation circles in the active spreadsheet.

When you find an error message (which usually begins at the first cell with the # sign), activate that cell and press the exclamation-point symbol in the Auditing toolbar (the trace error symbol). That commands Excel to draw a line of arrows, which displays the complete dependency chain for the cell containing the error message. Red arrows show the propagation of an error message and blue arrows show correct input values into cell formulas. The source of the error is located in the cell where a blue arrow points in and a red one leads out.

Although this source-of-error cell is always the first in the dependency chain that contains the error message, it’s not necessarily the first cell containing wrong data. To find the logical (and not syntactical) source of error, you must look one step up from the cell that generates the first error message.

If the lines are too long—spanning great portions of the spreadsheet and making them difficult to follow—double click on the arrow heads or on their back ends (the colored dots). That takes you right to the other end of each arrow; the formula references to different sheets are represented by little table symbols dropped right onto the sheet to which the arrows point; double clicking those symbols activates the spreadsheet being referenced.

You can customize the input validation rules in the Data Validation dialog box by clicking on the register tab Error Alert. The drop-down list box called Style gives three options for the consequences of input data that violate the validation rules: Stop means no data violating the validation rules are accepted; Warning and Information means that when the user enters input data that are out of range, only a message box with warning text is displayed but Excel nevertheless accepts the input data. Input data that violate the data validation rules can be located by clicking on the second symbol button from the right in the Auditing toolbar.

IMPORTED DATA

Spreadsheet data that are imported should be examined for consistency and plausibility by comparing the imported data with data from previous business years or from a regression line model in the case of prospective data. When controlling balance sheet ratios, check the numbers by calculating them twice—the second time using the balance sheet equation of assets = liabilities.

Spreadsheet software is a powerful tool. Recognize its strengths, and it adds significant leverage to your accounting skills. But ignore its limitations in tracking down the errors you inadvertently introduce, and it can speed you to disaster.