But outside of the big headlines London Whale-like mistakes, what kind of errors are we talking about? Raymond Panko, from the University of Hawai’i, is the specialist. Wrong data entered in a cell, inaccurate choice of formula, shaky links between cells, etc. How much error are we talking for people like you and I? A lot.
Broadly speaking, when humans do simple mechanical tasks, such as typing, they make undetected errors in about 0.5% of all actions. When they do more complex logical activities, such as writing programs, the error rate rises to about 5%. These are not hard and fast numbers, because how finely one defines reported “action” will affect the error rate.
Why do people make so many mistakes using Excel?
- People just make mistakes. That’s the reason constraining (and often frustrating) ERPs exist. When you leave the ERP system, you’re out there on your own!
- Excel gives a false feeling of control and flexibility. But at the same time, when using a technological tool to perform calculations, you tend to forget that you are still in charge!
- Excel seems too intuitive. That a lot of users are self-taught, or at least self-starters. There is a lack of formal and serious training for Excel. Who the heck would need it?
- In data we trust. There is such a devotion for data. We simply trust tables when we see them. Show me anything that is presented in an Excel table format and your result seems to have a scientific/calculation backup, and is therefore credible.
Shall we abolish Excel?
There are alternatives: MS Access, ERP systems. But none of these options offer Excel’s flexibility. You can’t have it all. We’ll always need to have tools on both ends of the spectrum: standardized and controlled ERPs versus flexible Excel spreadsheets.
There are as many ways to look at financial statements as there are financial analysts. There are multiple ways of surveying the quantities produced by a supplier and, arguably, different metrics can be of interest for the management and executives. You need the flexibility offered by Excel.
But mere data is not information. Information is not knowledge, and knowledge isn’t wisdom. Every front end user should have an understanding of the data he’s manipulating and the tools he’s using.
What we need is programming education: not just coding, but database design, should be for everybody. It’s already part of our (professional) life, let’s just embrace it.