Ten Steps to Creating Reliable Spreadsheets

31/07/2018

Stop worrying about how sophisticated your spreadsheets are. What you (and your company) should worry about is how reliable your spreadsheet is. Here are ten things you can do to ensure your data is right and that you (and your company) aren't depending on a unreliable spreadsheet.

Embarrassingly enough, I run both my personal finances and those of my consulting business from a couple of Excel spreadsheets. Obviously, I need those spreadsheets to be right -- an unreliable spreadsheet (one with errors) could be disastrous to either my business or my personal credit rating. I really should go out and buy a real accounting package but, let's face it, no accounting package is going to work in the peculiar way that I want. I have proof of that: Just ask my accountant who shows up every year to do my taxes and heaves a big sigh when he sees what I provide him with.

I'm not going to give up my spreadsheets, though. And I'm not alone in depending on spreadsheets: In one survey 85% of business leaders report they use spreadsheets for reporting, budgeting, and forecasting. Obviously, their businesses can't afford for their spreadsheets to be unreliable.

How Unreliable are Your Spreadsheets?

The reason I find my dependence on spreadsheets embarrassing is because of the number of times I've made fun of people who have errors in their spreadsheets (if you're interested here's a list of errors that cost companies millions of dollars). Certainly, I've made some mistakes with my spreadsheets over the years (though my errors are in the "hundreds of dollars" range, not millions). I've entered expenses as revenue, for example, made changes in one part of the spreadsheet without making corresponding changes in another, and just entered some bad data.

I console myself by realizing that I'm not alone in making mistakes. Various studies have shown that error rates in spreadsheets range from 20% to 100%. I grant you that the upper limit of "100% of spreadsheets examined have at least one error" is only typical of spreadsheets with thousands of formulas - that's not me and (hopefully) not you. However, that low number of "20% of spreadsheets examined have an error" is only achieved by ignoring errors that aren't considered "serious." In other words, the other 80% of the spreadsheets probably were unreliable, they just weren't considered seriously unreliable.

Putting that altogether, it's pretty much a certainty that your spreadsheets have errors. To use a Star Trek metaphor, spreadsheets are the Holodeck of business tools: You're just as likely to shoot yourself in the foot as do something useful when you depend on unreliable spreadsheets. Recognizing that you have a problem is the first step in solving it and I've spent a lot of time making my spreadsheets into something I can trust.

Eliminating Unreliable Spreadsheets

Here are ten things you can do to make your spreadsheet more reliable (and if ten seems like a lot, don't panic: You're probably already doing several of these). I've saved the most important two to the end.

1. Name cells: And then use those names in your formulas. A formula that references the wrong cell is easy to create (e.g. typing A20, when you wanted A21). Referencing the wrong cell by name is not only harder to do, meaningful names make bad references easier to spot. In addition, names follow cells around as you cut, copy, and paste.
2. Don't put real values in formulas: Numbers, or any other kind of value, in a formula is an accident waiting to happen. Values in formulas are hidden can be mistaken or just forgotten about (and, as a result, can go out of date). Put all values in cells where they can be seen, labelled, and reviewed. Then give that cell a name and use that name in your formula.
3. Clearly label every cell you can: A cell with a great description beside it is less likely to have the wrong value in it. If you've given the cell a name (and why haven't you?) then include the name of the cell in the label so the name is as visible as the value. Make sure that you arrange your spreadsheet so any cell you enter data into has its label visible while you're entering data.
4. Reduce data entry: If any change requires making multiple, related updates in your spreadsheet, rewrite your formulas so that you only have to make one update. Using the Indirect and Offset functions in your formulas can help here.
5. Limit data entry cells: If you can't eliminate multiple entries, set up your spreadsheet so that you make all of your entries in one place (this lets you see them in single glance) and have your formulas reference them from there. Then set up some cells right beside your data entry area to give you feedback on the results of your updates. If your spreadsheet requires keeping old data around then move any new data from your data entry range to that "old data" range but only after you've seen that the new data is correct.
6. Stop entering data: Ignore the previous entries in this list on reducing data entry. Instead, stop entering data altogether. Wherever possible import data directly from databases or downloaded files. Turn on Developer options and use dropdown lists to limit data entry choices.
7. Protect cells that shouldn't be changed: Typically, in any spreadsheet, only a small number of cells need to be changed. It should be impossible to change other cells without explicitly removing protection. Without protections then, when fingers slip, unexpected data gets entered and formulas get corrupted.
8. Automate, Automate, Automate: Yes, learning VBA is a pain and macro-enabled spreadsheets may be forbidden by your organization...but anything done in code is guaranteed to be done the same way every time. Your macros may be doing the wrong thing but, once you spot the problem and fix your code, it stays fixed.
9. Audit your spreadsheet: How do you know that your spreadsheet is correct? If you can check it against outside data, start doing that; If you can check one part of your spreadsheet against another part, do that too. And don't try to check data by just looking at the data - this is another area that you should be automating. Use conditional formatting to have "bad" numbers pop out at you (I use red lettering on a hot pink background).
10. Change your attitude about mistakes: When you find a mistake, don't just fix it. Stop it. Figure out a way to make sure you can't ever have that mistake again or for your auditing routines to find it. Then think about where else you can apply that fix and apply it there, also.

Your Future

You may have noticed that I labelled that last section "Things You Can Start Doing." That's because keeping your spreadsheets from being unreliable is an ongoing battle.

For example, it took me two years of tinkering with one of my personal finance spreadsheets before I finally figured out how, for a sinking account that holds cash to be used for semi-regular expenses, I could check that the numbers were correct. It was only a month ago that I found, at Excel Help HQ, some code that I could use to ensure that all the items in my accounts receivable worksheet were correctly referenced in my cash flow worksheet. The tools we discuss in Learning Tree's Microsoft Excel Training Course course have been invaluable to me in helping me build good audit routines and eliminate opportunities for error.

Obviously, your company needs your spreadsheets to be right. Don't let yourself or your company down. Think "reliable" before you think "sophisticated."