Error Reduction
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, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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.
1. Organize large workbooks in different worksheets by logical section
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 logical section to a new worksheet in the
workbook.
To make each section easier to find in your workbook,
assign each tab of your workbook a relevant name. To do so,
doubleclick 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 leftmost tab of your workbook. Press Ctrl +
Page Down to view the contents of the second worksheet.
Press the key combination again to view the third worksheet, and
so on.
2. 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
incorrect 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.
3. Set up an errortest table
One
of the worksheets that I frequently add to a report is named
Errors. Its errorchecking summary range might look something
like this figure.
Here, formulas in the range B4:B6 perform the checks
described in column A. 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 ErrorFound formula in cell B8 returns FALSE if all error tests
within the boundaries are TRUE. Otherwise, it returns TRUE. The
formula in the cell is:
B8:
=IFERROR(NOT(AND(B3:B7)),TRUE)
Here, the AND function returns TRUE only if all formulas in
the range B3:B7 return TRUE. And the NOT function flips TRUE to
FALSE, and FALSE to TRUE. Also, if one of the error tests
returns an error like #REF!, then the IFERROR function returns
TRUE, indicating that an error has been found.
Yes, it seems strange to change TRUE to FALSE like this in
the ErrorFound formula. But
I've found that it's easier to set up errortesting formulas
that return TRUE if the test shows that everything is okay. And
I've also found that ErrorFound is the mostaccurate way to name
the summary results. But to use that name, I need to flip the
sign of the results.
However, you might prefer to use formulas that return FALSE
if no error is found. Using this logic, the formula in cell B8
should return FALSE only if all error tests return FALSE. Using
this logic, the formula in cell A9 would be:
B8: =IFERROR(OR(B3:B7),TRUE)
Whichever point of view you prefer to use 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(ErrorFound,"ERROR! ERROR! ERROR!","")
Here, if an error is found, the formula returns the text
shown. Otherwise, the formula returns a null string, which is
invisible in your report.
Another way is to use conditional formatting to display an
obvious red fill in your report whenever ErrorFound is TRUE.
4. Allow for floatingpoint issues in your error tests
Suppose that one of your reports shows sales by region. So if
Marketing has added a new region without telling you, your
report will be incorrect.
To test for this possibility, you should set up a formula to
check for it in your error
test area that I illustrated in the previous tip.
Unfortunately, you might find it difficult to reconcile the
sum of your reported numbers with the grand total. This is
because Excel's
floatingpoint numbers can make it difficult to reconcile
two totals exactly. Therefore, you might need to test
whether your reported numbers are merely close enough.
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.
Typically, the MaximumError value is just a few cents. But
you can make it any value you want, of course.
5. 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 labeled Total Sales, Western
Region is...
=SUM(SalesService)
Here, 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.
6. 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 the other
workbooks all linked to still other report workbooks. 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
passwordprotected shares. Others were to outofdate workbooks
saved on computers that were seldom turned on. Still others were to
workbooks on computers that no longer existed. I also found
several circularcalculation chains, one that was ten workbooks
and several computers
deep.
To minimize errors, link only to external databases, not to
other reports.
7. Use scatter plots to uncover unusual results
You can quickly and easily use scatter plots to display
outliers in a series of numbers.
To do so...
1. Select your data.
2. Choose Insert, Charts, Scatter, Scatter with only
Markers.
Chart formatting doesn't matter. Chart placement doesn't
matter. What does matter is that Excel gives you a chart of all the numbers you've selected. This
chart 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.
Tags: #excel, #Excel reporting, #errors,
#scatter plots, #reconciliation, #tips, #workbooks, #external
links, #range names
