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
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
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
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
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
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
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
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:
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
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, the ABS function 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
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
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
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.