I love Excel spreadsheets. There’s nothing more satisfying than turning a blank Excel grid into a fountain of data and conclusions. Invite over pivot tables, external data, and macros, and suddenly you have an entire business analysis party. You can confidently make big dollar decisions from small and simple cells – or so we think.
Knowing my dependence and appreciation of spreadsheets, a good friend sent me a very interesting Wall Street Journal article, 88% of Spreadsheets Have Errors by Jeremy Olshan. In the article, Olshan exposes how almost all spreadsheets have at least one error in them, and large spreadsheets are usually filled with mistakes. We often are so rushed to get the calculated conclusion to other people that we don’t take time to check our formulas and have someone else proofread our work. Miscalculations and mistrust of analysis can often be the result. The article mentions major economic research that drew incorrect conclusions based on flawed spreadsheet formulas. Likewise, I’d like to share a story of how incorrect cells can wreak havoc on supply chains.
I still shudder to think that a simple, one-sheet spreadsheet completely ruined two weeks of my life.
My company uses a barcode printing program that pulls its information from a simple spreadsheet. The spreadsheet allows users to easily enter the item number they need, and the close-by thermal printer quickly spits out the barcode stickers. I had set up the spreadsheet to pull all the necessary information, including the 12-digit barcode and description, from external sources when the item number was typed in. The formulas worked great, and the spreadsheet seemed perfectly self-explanatory. Or so I thought.
Because the interface was so easy to use, more and more employees began to use it to print barcodes. This was great for me because I was not called out seven times a day to print the stickers. However, the lack of standardized training and safeguards eventually created a problem. Seeing the UPC column, someone replaced my formula that calculated the UPC with a static 12-digit UPC number for the item they needed. Having been saved with the incorrect number, the spreadsheet now printed the same, incorrect UPC barcode for every item. However, because the description was correct, no one caught the error. Worst of all, that week we had a big project that required a large number of labels.
One of our customers increased their orders significantly for an item that we had to label individually. This amounted to over 50,000 incorrect barcodes stickers that we put on, and then shipped without knowing the error. They went out the door on time, and the problem wasn’t fully realized until the product was already set in thousands of retail locations.
Was the Spreadsheet to Blame?
So who or what can we really blame for this mistake? After lots of pondering, I couldn’t blame anyone but myself – the creator of the spreadsheet. I don’t know who changed the cell, but it really wasn’t that person’s fault. Whenever I create a process, program, or spreadsheet for others, I need to make sure it’s error-proof. To fix it, I immediately locked down all the cells and rewrote the spreadsheet to be much harder to incorrectly edit (some simple poka-yoke, or in English, error-proofing). Just as important, we added a barcode-check step in which we scan the barcode and get a second pair of eyes to approve it. So, when the malevolent spreadsheet decides to strike again, we’ll be able to catch it.
While this may be a story more on the need for better processes, it also illustrates the devastating power one spreadsheet cell can conjure.
In the end, the retailer was able to change their computer system to accept the incorrect barcode, so the effect on end consumers was very minimal. The real pains were internal as we identified our process problems and worked to correct them. Of course, I lost some sleep and worked under heavy stress for a couple weeks, but as a result, I learned some important lessons – not the least of which concerns spreadsheets.
Five Tips toward Error-free Spreadsheets
Even if the world is plagued by lying spreadsheets, we can still personally become sources of accurate and correct data analyses. Here are five tips toward more accurate spreadsheets.
1. Get Someone to Proof Your Work and Make You Explain Your Formulas
If you do nothing else, please do this: Ask someone to look through your spreadsheet and test all your assumptions and formula logic. Have them examine key calculations closely and confirm everything is calculating correctly. Offer to do the same for your friend’s spreadsheets in return.
2. Trace Cell Relationships
Excel’s trace functions, found under the formula tab in Excel 2007 and 2010, are excellent ways to make sure all the cells you think are included in a calculation actually are. Trace precedents will point arrows to all the cells that feed into the cell you’ve selected. Trace dependents show all the cells depending on the selected cell. These help you catch problems of cells sneaking in or out of formulas – as well as seeing what might happen to other cells when you change the current cell’s value. Plus you feel pretty smart when there are hundreds of arrows all over your screen, “Wow, I can make some intense calculations.” Note that this step would probably have helped me catch the error in the story above.
3. Verify Links
External data, especially from other spreadsheets, can be very tricky. Excel isn’t the best database system in the world, so links sometimes break or don’t update. To avoid problems, verify and update links through the edit links button on the data tab. This tool let’s you ensure links are active and correct. I’ve had many links break for no apparent reason, so be vigilant on checking this frequently.
4. Address background error checks
Those little triangles in the top left corner cells are signaling that Excel thinks something might be wrong. Address them all, since often they are true problems. Only ignore the error if you’re sure it’s not an error, and the triangle will disappear.
5. Force Excel to Calculate
There are several reasons Excel stops calculating formulas. Usually it’s because calculations are turned off, but sometimes big files have quirks that hamper calculation – especially when VBA is involved. To recalculate all formulas and update all links, press Ctrl + Alt + Shift + F9. If you have external data, you’ll want to use the Refresh All command. If you have pivot tables that pull from External Data, you’ll want to refresh the pivot tables after you Refresh All (or Refresh All twice). Pressing Refresh All twice, then pressing Ctrl + Alt + Shift + F9 will refresh and calculate everything possible.
When You Need to be 100% Accurate, Rebuild from Scratch
Although these tips will help you catch many errors, there are still chances of problems. Several third-party vendors offer error-checking software. I’ve never tried them, but I do have a pretty strong technique to ensure high accuracy. For very important spreadsheets and decisions, I often recreate the entire project from a blank workbook to confirm everything is correct. I don’t copy and paste, but instead retype the formulas to make sure everything matches. Quite time intensive indeed, but duplicating my work usually takes a fraction of what it usually takes to create it. In fact a two-week spreadsheet took only an hour to completely rebuild from scratch because I knew exactly what to do. Having rebuilt it, and with everything matching perfectly, I feel confident that my spreadsheet is accidental-error free. Of course, whether my human assumptions are correct is an entirely other issue.
So the next time you find yourself in possession of a breakthrough calculation in Excel, I hope you’ll be a bit more leery that your spreadsheet is likely lying to you in some part. Encourage honest spreadsheets by error checking and proofreading so we can all avoid painful spreadsheet problems.