Home >  Exploring Excel  > 

Seven Ways to Avoid Errors
In Excel Reports & Analyses

Here are some quick and easy ways to avoid errors in your Excel
reports and analyses.

by Charley Kyd
May, 2006
(Email Comments)

You can find many stories on the web about problems with spreadsheet errors. So I thought it would be useful to review some practical ways to avoid those errors.

These ideas only scratch the surface, of course. As I accumulate additional ideas I'll pass them on.
 

Organize your workbook by function

When you create a large workbook with several reports and side calculations, it's possible for your design to take two extremes.

First, you could cram all of those displays and calculations into one or two very cluttered worksheets. Second, you could create many, many worksheets, each containing some small piece of the whole. Either extreme creates confusion, which leads to errors.

The first extreme is more common than the second. Therefore, as a general rule, most complex workbooks could be simplified by moving each major function or display to a new worksheet in the workbook.

To make each major function easier to find in your workbook, assign each tab of your workbook a relevant name. To do so, double-click on a tab to highlight its original text; then enter your new text.

If each worksheet contains one logical section, you can easily review the entire report or analysis. To do so, start with the left-most tab of your workbook. Press Ctrl + PageDown to view the contents of the second worksheet. Press the key combination again to view the third worksheet, and so on.
 

Back up workbook generations

Have you ever saved changes in a workbook, then realized that your changes were incorrect? And because you've saved those changes you now have a real disaster on your hands?

Whenever I create a new report or analysis, I'll usually name it something like MyReport01.xls. Then, after I make substantial changes, I'll save the workbook as MyReport02.xls, then MyReport03.xls, and so on.

Quite often, I'll realize that the current generation of my report has veered off in the wrong direction. So I'll return to an earlier generation, save it as the next number in the sequence, and continue my development.

To illustrate this approach, the workbook that generates the Free Dashboard shown to the left above is "Web Statements 17.xls". 
 

Perform reasonability checks of inputs and outputs

One of the worksheets that I frequently add to a report typically is called Errors. Its error-checking summary range might look something like this figure.

Here, formulas in the range A3:A7 perform the checks described in column B. (Some of the formulas could link to tables or side calculations within the same worksheet.) If a test shows that the item is okay, its formula returns TRUE. Otherwise, it returns FALSE.

The gray rows mark the top and bottom boundary of the error testing formulas. You can add new tests by inserting rows between these boundaries.

The formula in cell A9 returns TRUE if all error tests within the boundaries are TRUE. Otherwise, it returns FALSE. The formula in cell A9 is merely:

=AND(A2:A8)

Some people prefer to use formulas that return FALSE if no error is found. Using this logic, the formula in cell A9 should return FALSE only if all error tests return FALSE. Using this logic, the formula in cell A9 would be:

=OR(A2:A8) 

Whichever point of view you prefer when you test for errors, your final step should be to make an error obvious in your report. One approach is to enter a formula like this in an empty cell near the top of your report:

=IF(NoErrors, "", "ERROR! ERROR! ERROR!")

Here, if no errors are found, the formula returns a null string, which is invisible in your report. Otherwise, it returns the text shown.

Reconcile your results with an ultimate truth

Suppose that total sales for your company consists of the sales of various product lines plus sales for miscellaneous categories, like repairs or documentation. When you're reporting sales by product line, how do you know that someone hasn't added a new product line without telling you?

One way to protect yourself from this problem is to maintain a reconciliation area in your workbook. There, you add the total of your reported product line sales to miscellaneous sales, and then compare that total with an "ultimate truth"...the value of Total Sales from your General Ledger. If the totals don't match, then you know that an error has occurred.

Unfortunately, you might find it difficult to reconcile the sum of your reported numbers with the grand total. This is because adjustments and timing issues can make such numbers difficult to reconcile to the penny. Therefore, you might need to test whether your reported numbers are merely close enough to the ultimate truth.

From an Excel perspective, an easy way to do that is to use a formula something like this:

=ABS(ReportTotal - CheckTotal) <= MaximumError

Here, ABS returns the absolute value of the difference between the two totals. If the difference is smaller than the maximum value you specify, the formula returns TRUE; otherwise, it returns FALSE.
 

Use range names

Suppose a value in a report is labeled Total Sales, Western Region. And suppose the formula for this result is: =SUM($M$5:$M$34)

Is that formula pointing to the correct data?

It's impossible to know, of course, without going to the cell address and examining its data. And after you examine the reference for that formula, you really should examine the many other references in the many other formulas in your spreadsheet.

Just how likely is it that you'll do all that work?

Instead, suppose the formula is: =SUM(SalesService)  Now, it's immediately obvious that a formula with one label is returning completely different data. The error is obvious.

If you assign understandable names to key ranges in your spreadsheet, and then use those names in your formulas, your formulas will be much easier to understand. And the errors in your formulas will be much more obvious.

Manage external links intelligently

Depending on how they're managed, links to other workbooks can reduce errors or increase them.

If you maintain workbook databases, it makes sense to create your reports in workbooks separate from your databases. That way, many different reports can link to those same databases. If any one of the reports uncovers an error in your data, all reports will benefit from the correction.

On the other hand, it's very easy to go overboard with external links. Several years ago, I discovered a client's workbook that linked to eight other workbooks. Those workbooks linked to other workbooks, and to each other. And so on.

By the time I had finished tracing through all of the links, I discovered more than 60 workbooks in a spider web of interconnections. Many links were to workbooks on password-protected shares. Others were to out-of-date workbooks saved computers that were seldom turned on. Still others were to workbooks on computers that no longer existed. I also found several circular-calculation chains, one that was ten workbooks deep.

To minimize errors, link only to external databases, not to other workbooks that are linked to other workbooks that are linked to...
 

Use line graphs to uncover unusual results

You can quickly and easily use a line graph to display outliers in a series of numbers. After you select your data, you can create the chart with three clicks:

1. Click the Chart Wizard button in your toolbar, or choose Insert Chart.

2. Click the Line chart type.

3. Click Finish, accepting all defaults.

Chart formatting doesn't matter. Chart placement doesn't matter. What does matter is that Excel gives you a line that charts all the numbers you've selected. This line makes outliers -- unusually large or small numbers -- stand out from the crowd.

These certainly aren't the only ways to avoid errors in Excel. If you have other suggestions, please let me know.

(Email Comments)