Decision Mechanics

Insight. Applied.

And if thine data offend Excel, change it

By

genetic code

I’ve written numerous articles on issues with spreadsheets and why they shouldn’t play any part in a data analytics pipeline. However, trying to persuade people to stop defaulting to Excel is a thankless task.

So, it was no surprise (but very depressing) to read that geneticists changed their naming conventions to prevent Excel mangling their data.

My dismay was alleviated, partially, by a great quote in the article that originally alerted me to this travesty.

[W]e won’t be putting anything into production that relies on data supplied to us as spreadsheets.

The anti-spreadsheet manifesto in a single sentence.

Spreadsheet error delays opening of children’s hospital

By

An audit report has blamed a spreadsheet "copy and paste" error for cost overruns and delays at an Edinburgh children’s hospital.

A local politician called it

[…] one of the most expensive typos in history.

How many more high-profile spreadsheet failures is it going to take before we deem the inappropriate use of them to be professional malpractice?

16,000 coronavirus cases missed by Excel

By

When the lead story in the Daily Mail complains about Excel misuse in its headline you know things have gone too far.

15,841 coronavirus cases were excluded from UK government figures as a result of

…an Excel spreadsheet reaching its maximum file size, which stopped new names being added in an automated process.

Apparently these details were not supplied to the "track and trace" programme, meaning that people exposed to the virus were not alerted—potentially leading to unnecessary infections.

I’ve been complaining about spreadsheet misuse for a decade, and this is one of the most shambolic examples I’ve come across. Blatant abuse of Excel as a ramshackle database.

This also highlights the dangers of casual automation. Without appropriate checks and balances, automated processes can fail catastrophically, and silently.

There really is no excuse for this level of incompetency. And, the worst thing is the "solution".

The technical issue has now been resolved by splitting the Excel files into batches.

Add more sticking plaster to the homespun system and press on. I can only weep.

Some additional technical details have been revealed since the story broke.

Data organization in spreadsheets

By

Karl Broman and Kara Woo offer some good advice on organizing data in spreadsheets.

They advocate confining the use of spreadsheets to data entry and storage—moving calculations and visualizations to other tools. This certainly avoids some of the biggest problems with using spreadsheets.

However, spreadsheets don’t enforce any discipline. It’s up to the user to be vigilant and do the right thing. People are bad at maintaining consistency. Computers are excellent at it. Why make it hard for ourselves?

Spreadsheets. Just say no.