Everything that is wrong with Excel (and why we still use it)
Although Excel is commonly cited as the ‘most used programming language’ with over 750 million users worldwide, for many it is also the beginning and end of all what is wrong in this world. Googling for ‘Excel mistakes’ takes you on an endless journey along with online articles and blogs with titles as ‘The 7 biggest mistakes of all time’ and ‘The Excel mistake that changed the world – and why you don’t want to be the person who done it’. The latter is about two Harvard economists who erroneously used Excel to prove that monetary austerity is justified – which shows that just a simple coding error (selecting the wrong cells to calculate an average) can have a global impact. To be fair, this could have happened with many other tools and programming languages as well, but it does reveal a few things.
First, Excel is the go-to tool, in any industry at any time, and especially in finance. Excel is the archetypical End-User Computing (EUC) tool – it is readily available and relatively easy to use and by combining data transformation capabilities and transparent user interface, it gives end users the ‘experience’ of being in the driver’s seat. Data insights are easily obtained and shared with others – the broad adoption of Excel in any professional setting is largely driven by habit.
Second, for a lot of simple objectives, Excel is the optimal solution. Especially, when there is a need for rapid analysis and straightforward data transformations like sorting, filtering, and creating tables and charts. Excel will just give you a quick answer. There are some conditions however that limit the appropriate usage of Excel. For example, the number of concurrent users is limited, as is the size of the data and integration with other data sources.
“You can have my Excel, after you ripped it from my cold, dead hands”
Steven on Twitter*
Small fault tolerance
Third, in many other situations, Excel is probably not the right tool. For example, when the fault tolerance is small or stated differently, the financial costs of errors is high, or when there are business-critical processes or key data involved. Even from a compliance or regulatory point of view you might want to address these unappropriated usages of Excel.
Many of clients struggle with this challenge. How can they preserve the advantages of Excel and End User Computing but overcome the disadvantages? It helps to understand where these disadvantages originate: ‘data ingestion’ and ‘development process’.
What you see is what you get
The strength of Excel or spreadsheets, in general, is what you see is what you get (Wysiwyg). All the data you see in your spreadsheet is the data you can actually use. But sometimes ‘Wysiwyg’ is not what you want. You either want more, for example, larger datasets, more complex data, integration with multiple other sources – or you want less, for instance, you want to hide sensitive input data while at the same time being able to use the data in calculations. In these cases, proper data architecture is required. This means building a data lake or warehouse, that is scalable and secure, and above all, of reliable quality. So, no more manual copying and pasting data.
Under the hood
The other strength of Excel is the ease of use and the absence of a steep learning curve. If you want to calculate the average range of cells, you can do that with a few mouse clicks. What happens under the hood remains hidden and frankly, you’re not interested in that either. However, when the complexity grows and tolerance for errors gets smaller, this becomes a concern. In that case, you want to apply all modern development standards, like testing, versioning of your code, code reviews, logging, and so forth. This means building a CI/CD** pipeline with clear agreements on the responsibilities and accountabilities of persons and teams.
Of course, you do not want to throw away the champagne with the cork. The trick here is to keep the process, pipelines, and tooling attractive for end-users to apply. We have advised and helped many of our clients with the transition from Excel to a controlled End-User Computing environment. We are keen to learn how you keep control of Excel in your organization and where you are in the transition. If you would like to share your thoughts or want to know more about how we handle this, feel free to contact firstname.lastname@example.org