hen VisiCalc hit the scene on October 17, 1979 as the world’s first mass-produced spreadsheet software, it forever changed the way we thought about data input and analysis. The lesser-told story, though, is that it also marked the first day that Pandora’s box full of garbage, user-created spreadsheets opened up and circulated en masse.
The hard, consistent truth is that despite advances in spreadsheet technology, with Microsoft Office and Google Workspace containing some of the most advanced and popular spreadsheets today, user-error and/or poor spreadsheet design continues to keep businesses operationally archaic.
After 10+ years of consistent spreadsheet use both professionally and personally, I’ve had enough. No more garbage spreadsheets on my watch.
Why Does Spreadsheet Accuracy Matter?
Accuracy in every profession is paramount, especially in professions that leverage heavily off of spreadsheet data. While innocuous errors such as missing punctuation within a text string wouldn’t move the needle, bringing in data from an incorrect column and making business decisions based on that incorrect data would instantly qualify that spreadsheet as complete, objective trash. The impact of the error(s) ultimately determines whether you’re sitting on a pile of rubbish or a pile of decision-making gold. That’s easy to quantify when, for example, your company’s bid to close on a deal falls through because your company executed a wire for $100,000 instead of $1,000,000 — thanks to a spreadsheet’s hardcoded error.
The same way that we expect software to run flawlessly with no bugs is how we should view spreadsheets’ construction and use considering how critical they are to most business’ decision-making processes. However, what we often find in reality is that the spreadsheets’ keys are blindly handed to individuals with moderate to considerable industry experience but little to no in-depth spreadsheet experience. As a result, you have companies that inadvertently put money and lives in peril due to preventable spreadsheet mistakes. But don’t take my word for it. Ask an Ivy League like Dartmouth College.
You could do this with a headline or slogan (such as VW’s “Drivers Wanted” campaign), color or layout (Target’s new colorful, simple ads are a testimony to this) or illustration (such as the Red Bull characters or Zoloft’s depressed ball and his ladybug friend). All good advertising copy is comprised of the same basic elements.
Dartmouth College Said Most Spreadsheets Suck
Over a decade ago, a small study at Dartmouth College sought to identify the quantitative impacts of errors in 25 spreadsheets across 5 different companies. Errors were defined within 6 categories as follows:
1. Logic error - a formula is used incorrectly, leading to an incorrect result
2. Reference error - a formula contains one or more incorrect references to other cells
3. Hard-coding numbers in a formula - one or more numbers appear in formulas and the practice is sufficiently dangerous
4. Copy/paste error - a formula is wrong due to inaccurate use of copy/paste
5. Data input error - an incorrect data is input
6. Omission error - a formula is wrong because one or more of its input cells is blank
Their findings were summarized as follows:
Within these 25 spreadsheets we identified 381 potential errors, of which 117 (31%) were confirmed as errors by the developers of the spreadsheet. Among these confirmed errors, 47 (40%) had no quantitative impact on the results. Among the remaining 70 confirmed errors, the largest error was $100 million; however, 9 of the 25 spreadsheets tested had no errors at all.
Whether it was 25 or 2,500 companies being studied, 9 out of 25 (36%) is a poor error-free rate given how important spreadsheets are for a company. With so much at stake, it’s reasonable to expect the error-free rate to be significantly higher regardless of the sample size. My experiences in the decade since the study lead me to believe that not only was that an accurate representation of companies’ broader indifference towards spreadsheet competency, but also that not much has changed since.
Make Spreadsheets Great Again
It’s been over a decade since I was using Microsoft Excel to input an equals sign into one cell, followed up with individual clicks on 137 other cells in an effort to sum the 137 cells’ worth of data. That was poor, unscalable functionality.
I, too, was guilty of sending a spreadsheet to someone with 5,849 rows worth of data and told them that the answer they were looking for was somewhere within that spreadsheet. That was poor design.
Fast-forward to present-day, after 10+ years of trial and error in real business situations, hours of Udemy courses, countless Google searches, and in-person software development instruction — at worst I can show the average person how to stop producing low-quality spreadsheets both functionally and aesthetically. At best, I can now show the average person how to become an indispensable part of their organization.
Over time, I'll lay out the common practices I use to separate my spreadsheets from those of my peers. Instead of focusing on Microsoft Excel, my focus will be on Google Sheets (which is not the same as Microsoft Excel) due to its ever-increasing popularity among young people and young companies alike. The practices and formulas in Google Sheets are generally transferable to Microsoft Excel, though, so they’ll be useful across both products.
Ensuing topics will include but not be limited to:
- Must-have formatting and design for every spreadsheet
- Simple, essential, yet commonly neglected functions
- Different types of IF functions
- Pivot tables
- Sorting/Filtering functions in lieu of using the Filter icon
- Uses for advanced functions such as IMPORT & INDEX MATCH
- Google Apps Script and the power of custom-made functions
- And much more
Let's be great!
