In praise of Excel

Excel gets bad press. When it lost test results during the pandemic there was an outcry. People have been told to replace it with “something better” for years. I’m going to defend Excel. Not because it’s perfect but because it’s the lingua franca of data whether people like it or not — and data has never been more important. We’re drowning in data. “Data driven decision making” means making decisions on the basis of facts and figures as opposed to the whimsical approaches of the past. That doesn’t just apply to business leaders. Everyone is meant to use data.

Enter Excel. It’s been used by accountants for most of its (long) life. People also used it for their personal budgets. But gradually, over the years, its wide availability (it’s been part of Microsoft’s Office suite of software for decades) resulted in it being used by everyone. Accountants continue to use it — but so too do scientists, economists, researchers, teachers, nurses and hairdressers. Nowadays, you can assume people mean Excel when they say they’re doing anything with numbers. Even when people say they don’t use Excel they do. I asked a friend of my son, a Computer Science graduate who works in the City of London, what he uses for data analysis: “Excel pivot tables”. It’s become a guilty secret among professionals.

Why this love/hate for Excel? Part of the reason is the alternatives. If you think Excel is bad, check out SPSS. If you don’t like the graphs and charts in Excel, try producing a pie chart in Python. Excel dominates for a reason. Microsoft realised its good fortune (that the competition was worse) and made Excel easier for beginners and more powerful for experts.

Why you should use Excel

1. Excel is everywhere. Like it or not, you’re going to have to work with Excel. Excel effectively costs nothing because it’s part of the Microsoft Office suite. It’s installed on 90% of the PCs in the world. Excel is the most popular programming language in the world. You can send Excel files in the knowledge that the recipient can open your files and work on your data. Good luck sending an SPSS file or Python code.
2. Excel is easy. In spite of Microsoft’s best efforts, many people find Excel complex and, at one level, it is. It’s certainly more complex than Word or PowerPoint. So Excel is complex — until you look at the alternatives. Lots of people have tried “something else” only to come back to Excel. One of the attractions of Excel (or any other spreadsheet) is that you can “see” your data. Excel is visual. Your data and your code co-exist in the same place. The row and column interface is familiar, and you see the data change in front of your eyes.
3. Excel is powerful. Microsoft is trying to make Excel more powerful by adding new functions and tools. The file size limitations have gradually increased over time. The addition of pivot tables and Power Query provided new capabilities for working with large datasets. You can create powerful pivot tables, which summarise datasets, using drag-and-drop. Functions, such as FORECAST, provide sophisticated prediction techniques without knowing anything about regression.
4. Excel is all you need. Most people don’t work with Big Data. They work with small data. The typical dataset isn’t 10,000,000 records, sourced from 1,000 websites. It’s 200 records, sourced from your local network. People are now working with bigger datasets — but “big”, in this context, means a few hundred or a few thousand records. Excel is so ubiquitous and so familiar that even real data scientists use it. They run trial data through Excel to “see how it looks” before using more sophisticated tools.

Why you shouldn’t use Excel

Excel is fine for most people. But it’s not fine for serious data science. If you’re an astrophysicist dealing with gigabytes of real-time data from satellites, learn Python or R. If you’re an epidemiologist trying to find viral patterns across populations, use machine learning. If security is critical, don’t use a spreadsheet.

The interesting question is the middle ground between small and big. Is Excel OK for quite large datasets or quite important applications? Can you run your High School on Excel? Should you trust your business to Excel? In one sense, it’s moot — Head Teachers do run their schools on Excel; businesses do trust Excel. As data gets bigger, Excel gets better. Excel is morphing into an ETL (Extract/Transform/Load) tool. Power Query lets you source data from lots of internal and external sources and transform (clean, delete, merge, combine, etc.) the data using the query editor. Excel supports data models, which allow you to create representations of your data in much the same way as a relational database.

So, potentially, Excel can take on relatively large data analysis tasks. I say “potential” because most Excel users have never heard of ETL, data transformation or data models. Unless the Head Teacher knows something about these things, she probably shouldn’t be running her school on Excel. But that’s an education problem, not an Excel problem. Excel democratises data analysis. It puts sophisticated analysis software in the hands of ordinary people, and permits them to work on their data without incurring the costs and inconvenience of employing experts. We’re better spending our time educating people to use Excel properly than telling them to leave it to the experts.

Excel isn’t the best at anything. SPSS offers more statistical power. Python is more flexible. Power BI offers better visualisation. But Excel is on your desktop. It offers enough statistical power, flexibility and visualisation for 99% of people — all wrapped up in a familiar, easy-to-use, ubiquitous piece of software. Shouting “Don’t use Excel!” isn’t going to work. The genie is out of the bottle. Most data analysis is carried out by people using their own small datasets — for which Excel is ideal. No-one uses Excel for serious data science (beyond prototyping). Microsoft is trying to make Excel a serious “middle ground” contender. Don’t bet against it.