I found an excellent discussion about the way we use MS Excel spreadsheets. Excel is flexible, but can turn out to be frustrating in some cases. It has lead to monumental errors.

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.

Where do you start? Teach yourself MS Access and VBA (what office doesn’t have the MS Office?), other programming languages such as JavaScript, PHP, etc.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s