A story caught my eye in the news this week: Warehouse Group staffer hacks system, steals $95k vouchers to 'mess with bosses'. The "hack" involved nothing more than manipulating a spreadsheet used for generating gift vouchers - a perfect example of how Excel gets misused in critical business processes.
Key Takeaways
The damage from spreadsheet errors ranges from ancient to deadly:
- Excel and spreadsheet mistakes have plagued humanity for 4,000 years (we have archaeological proof!)
- Common culprits include copy-paste errors, misaligned data, outdated file formats, and using Excel for critical systems it wasn't designed for
- Real-world consequences documented here include: $95k theft, wrongly rejected job candidates, damaged police-community trust, exposed identities of 10,000+ police officers, and over 1,500 COVID-related deaths
- The root cause isn't human error - it's using spreadsheets where purpose-built systems should be employed
- If your data is mission-critical, affects public safety, or involves multiple users, Excel probably isn't the right tool
The Warehouse Group Hack
What’s interesting about this are the details of the “hack”:
For a voucher to be redeemed, a staff member accessed a pre-formatted Microsoft Excel spreadsheet, and then modified the data with the quantity of coupons, coupon codes, the dollar value per coupon, and the redemption period.When the spreadsheet was finalised, the user saved the document as a copy.When coupons were ready to be redeemed by a customer, a market employee with the required permissions imported the spreadsheet into the administrative control centre and emailed each customer the relevant coupon code.On the morning of December 10, 2022, Jamieson accessed The Market’s network system and modified the pre-formatted Excel spreadsheet for bulk gift card uploads, and created three separate spreadsheets.He created four coupons worth $20,000 in his first modification, nine gift coupons worth $65,000 the second time, and one more worth $10,000.
Now, you might argue this is a process problem and not a tooling problem, but there’s no doubt that the ubiquity and versatility of Microsoft Excel means it’s used in all sorts of situations where it really shouldn’t.
In my recent blog post I talked about the ways in which I think you should use spreadsheets effectively. In this post I’m going to share some stories of how spreadsheets were used badly, and in some cases even caused harm.
Get ready to hide behind the sofa, because here are some the spreadsheet horror stories. The list below goes from bad to worse…
#5: The oldest spreadsheet error in the world
We begin with a rather benign case. Did you know that the world’s oldest piece of penmanship is a spreadsheet?
A ruled papyrus from around 4000 BCE details the observations of an Egyptian official visiting the pharaoh’s naval installation at the outpost of Wadi al-Jarf. That pharaoh was Khufu, famous for the Great Pyramid of Giza, and apparently the spreadsheet format was quite common in Ancient Egypt - examples of it are preserved in fragments spanning hundreds of years.
But we all know that where you find spreadsheets you find spreadsheet errors, and we have an example of this from a tablet discovered in the Iraqi desert dating from around 1800 BCE. Four errors in fact, meticulously chronicled by a mathematician from the University of British Columbia, and it will come as a surprise to no-one that one such error was due to a faulty copy and paste.
So the next time your boss pulls you up for your Excel snafu, take solace in the fact that at least it won’t be trawled over by distinguished mathematicians thousands of years later for all the world to see.
#4: Computer says no. Then yes. Oops, yes again.
This one is close to home for me: trainee anaesthetists in my home country of Wales were told that they were all unappointable because of, you guessed it, a spreadsheet error.
Here’s what happened:
"The interview scores are stored in an Excel spreadsheet. Each of the seven [UK] recruitment regions creates a separate spreadsheet, but these have no standardised template, naming convention or structure. After being manually amended, all of the various scores are entered into a Master spreadsheet. This is carried out row-by-row and takes several days, likely to be subject to interruptions," the report said.In the process, a ranking column in the Wales Region Spreadsheet had been wrongly transferred to the Master National Spreadsheet, erroneously appearing as an interview score. After their interviews, candidates were ranked 1 to 24 – with 24 actually being the total number of candidates interviewed in the region. But even the highest possible "interview" score of 24 was much lower than candidates' true scores, and because the candidates had been ranked in order of performance, the best candidates were deemed weakest and vice versa."As a consequence of this all the candidates from the Wales Region did not score highly enough when all candidate scores were ranked nationally and all candidates from the Wales Region were 'unappointable'," the report said.
Of course, where there’s one poorly-implemented process there’s bound to be another. And so it was:
In attempting to tell candidates about the problems with the scoring system, ANRO [the Anaesthetic National Recruitment Office] then found a bug in the messaging system of Oriel, the recruitment platform from vendor HiCom.ANRO decided to honor the 10 job offers it had made by mistake and used Oriel to tell the candidates. Unfortunately, a system error in Oriel meant it then erroneously sent that communication to an additional 16 candidates. ANRO decided to honor these 16 additional offers too, and find the candidates posts.
If only there was something to numb the pain.
#3: Misstating racial disparities in crime statistics because of misaligned spreadsheet
Did you know that black residents in Schenectady, NY are 74 times more likely to be arrested for a marijuana-related offense than white people, despite making up just 12 percent of the population? Me neither, and that’s because this isn’t true.
The truth, in fact, is that black Schenectady County residents are 10 times more likely to be arrested in the county than their white neighbors. Here’s what happened, and you can bet that there was a spreadsheet involved:
The error, which placed Schenectady atop a list of 10 counties with the worst racial gaps in cannabis arrests, was a result of a "misaligned spreadsheet" which improperly merged Saratoga County U.S. Census data with Schenectady arrest numbers from the state Division of Criminal Justice Services, researchers said.
So far we’ve looked at examples of spreadsheet horror stories that are either benign or where the error was caught and amended. But in this case lasting harm was done, as Schenectady police Chief Eric Clifford explained:
"The premature release of these figures and the reporting of this has hurt that trust that we work every day to build," Clifford said. "Not everyone who looks at that statistic is going to read the follow-up article."
The New York Civil Liberties Union, who were responsible for the error, are a non-partisan civil rights organisation and there was certainly no malicious intent involved here, but even genuine mistakes like this can cause lasting damage. In this case the damage was reputational, but the harm gets more serious as we continue.
#2: PSNI apologises to officers and civilian staff after major security breach
It was hard to decide between this example and the next one as the worst, because honestly does it get much worse than accidentally publishing the names, ranks, locations and other personal data of every serving police officer and civilian employee in the Police Service of Northern Ireland (PSNI)? Oh, and that includes almost 40 PSNI staff based with MI5.
That’s what happened last year when the PSNI received a Freedom of Information request asking for standard statistical information on the strength of the PSNI, with details of how many officers it has at each rank. This was compiled into, you guessed it, a spreadsheet and published online. Here’s what happened next:
However, a second tab in the spreadsheet contained multiple entries in relation to more than 10,000 individuals. For each individual, there are 32 pieces of data meaning that in total, there are about 345,000 pieces of data in the file.The spreadsheet, which has been seen by the Belfast Telegraph after we were alerted to it by a relative of a serving officer, includes each officer’s service number, their status, their gender, their contract type, their last name and initials, details of how much of the week they work, and their rank.It also includes the location where they are based (but not their home address), their duty type (from chief constable to detective, intelligence officer and so on), details of their unit (such as the anti-corruption unit or the vetting department), their branch and department, and other technical information about their employment.There are 10,799 entries in the database.
Anyone who knows anything about the recent history of Northern Ireland will appreciate the particular gravity of this situation - it literally puts lives at risk. And just when you thought this was bad enough, here’s the kicker:
There are a tiny number of individuals whose unit is given as “secret”. But although that does not disclose precisely what they do, it marks them out as operating in an acutely sensitive area – and then gives their name.
So what could be worse than this? The next example is truly the worst of them all, and it didn’t just put lives at risk - lives were lost as a result of it.
#1: At least 1,500 people died as a result of this spreadsheet error
Remember contact tracing? Between September 25 and October 2, 2020, the UK was scrambling to get a clear picture of how COVID-19 was spreading in the community as a second wave was getting underway, resulting in a sustained lockdown over the Christmas period that year and beyond.
Between those two dates I’ve quoted, a total of 15,841 COVID-19 cases in England (around 15 to 20% of all cases) were not immediately referred to the contact tracing system.
You know the reason why, don’t you? Spreadsheet error.
On October 4, 2020, the PHE [Public Health England] released a public statement on a “technical issue” discovered in the night of October 2 to October 3. An internal investigation had revealed that 15,841 positive cases had accidentally been missed in the data reported to Test and Trace. The maximum file size of 65,536 rows of the document type used for official data processing purposes (Excel Binary File Format, XLS) had been exceeded, leading to a failure to transfer case information in excess of that limit. PHE reported that the original reporting dates for these cases would have been between September 25 and October 2. While the data glitch did not affect people receiving their individual COVID-19 test results, an anticipated 48,000 close, recent contacts of COVID-19 patients had not been traced in a timely manner and had therefore not been encouraged to isolate.
From a technical standpoint, this case highlights the variety of things that can go wrong when we use spreadsheets carelessly: anything from mistaken copy and paste, misalignment, through to just plain old outdated software can trip us up.
But what’s particularly horrifying about this example is that lives were lost as a result of it:
Conservative estimates suggest that the failure of timely contact tracing due to the data glitch is associated with more than 125,000 additional infections and over 1,500 additional COVID-19-related deaths.
That’s right, at least 1,500 people died because of this spreadsheet error. As with all of our examples I have no doubt that there was nothing deliberate about this, but this all proves what W. Edwards Deming reminded us of over and over again: it's not the people who are to blame, it's the system. Learn how to lead system improvements without formal authority.
Fix the system. Use spreadsheets with caution, if at all. In rare cases it can literally cost lives if you get it wrong. The most effective leaders are often those without formal titles who notice these problems first.
Postscript
I ended with truly the most serious spreadsheet error imaginable, but if you’d like to peruse through (mostly) less serious examples then I refer you to the EuSPRIG Horror Stories page from where I’ve chosen all of these examples. And if you have any spreadsheet horror stories of your own let me know in the comments below!