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, 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 + 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 error-test table
One of the worksheets that I frequently add to a report is named Errors. Its error-checking 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 error-testing formulas that return TRUE if the test shows that everything is okay. And I’ve also found that ErrorFound is the most-accurate 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 floating-point 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 floating-point 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 password-protected shares. Others were to out-of-date workbooks saved on 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 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.
