Steven Searle looks at the resurgence of Microsoft Excel from glorified calculator to an application and automation giant and explains why Excel still matters for SMEs and market research in particular.
After attending Andrew Le Brueilly’s ‘Python in Excel’ ASC Labs session a few weeks ago, I started reflecting on how much Excel has transformed in recent years.
Before I start rambling, I’m quite conscious that some people reading this won’t understand what certain things are, so I’ll asterisk any techy jargon and whack a description at the bottom of the article to act as a glossary.
Like many people, my first real experience with automation came through VBA*. It felt powerful at the time – saving half an hour here and there felt like magic – but it quickly became clear that VBA was more of a workaround than a long-term solution. It was often messy, error-prone, and increasingly incompatible with the fast-moving world of cloud-first platforms and APIs. As newer tools arrived, I genuinely thought Excel’s best days were behind it.
But a lot has changed since then. A few years ago I spent a lot of time saying “get this out of Excel” trying to prioritise using software and now I’ve come full circle to seeing it as a flexible, lightweight application that’s pivotal to our business operations.
Here are some of the reasons why Excel’s evolution is worth paying attention to
1. The Power Platform
I could talk for days about how much I love Microsoft’s Power Platform* (maybe that’s another blog…).
The tight integration between Excel and all things ‘Power’ has opened up a world of new possibilities. You can create entire applications that sit either in Power Apps* or Excel and use Power Automate* to have them communicate with each other…and any other part of your Microsoft package. Excel is no longer a data silo-it’s now a living, breathing interface that can read from and write to Dataverse* tables or SharePoint.
For teams who live in spreadsheets, this is transformational. You can now build real apps and automate workflows without forcing people to abandon Excel. In fact, I’ve built new workflow management tools in Excel specifically because of its compatibility.
…and for old times’ sake, I may have chucked in a few functionality macros because I will always love VBA and it still has its uses!
2. SPILL Formulas & Dynamic Arrays
The introduction and development of SPILL* formulas like FILTER, SORT, UNIQUE, and SEQUENCE has completely changed how we build logic in Excel. These aren’t just incremental improvements, they’re a paradigm shift.
We’re no longer relying on hidden helper columns or complex nested formulas. The logic is cleaner, more scalable, and often behaves more like programming operations than traditional formulas.
Using these alongside VSTACK and HSTACK is amazing as well -you can SPILL by rows, columns, and stack variables horizontally or vertically. It’s incredibly useful for displaying dynamic content that you’d normally get in dashboards.
3. Power Query & API Integrations
With its ability to pull data from APIs, SharePoint, SQL, and dozens of other sources, Excel has become a legitimate data integration tool. It’s not just for “copy-paste and tidy up”-it’s a real ETL* solution for small-to-mid scale needs.
I quite often use Power Query* as my first port of call for testing and retrieving API data. It’s fast, intuitive, and surprisingly robust.
4. Python in Excel
And then there’s Python.
Native Python support within Excel means analysts and developers can now write proper scripts inside the spreadsheet environment. You can use pandas for data wrangling, matplotlib for plotting, and run complex logic that would have been virtually impossible to do in formulas.
And it’s still very early days with Python in Excel-there’s so much potential here.
Why Microsoft still rules for SMEs
Excel didn’t fade away-it evolved. And much of that evolution is thanks to the Power Platform.
All of the tools in the Microsoft ecosystem are an absolute must for any SME out there. In the right hands, you have the ability to significantly reduce your overheads while genuinely being creative and innovative. In the last year alone, I’ve saved several thousands of pounds ridding clunky SaaS solutions that weren’t fit for purpose.
If you’re reading this and feel like you’re missing a beat somewhere, I’d love to chat through some of the options and solutions at your disposal. Give me a call or drop me an email (steven.searle@mustard-research.com).
Glossary for non-geeks
VBA – the programming code used to create macros in Excel for automating the repeated processes at the click of a button. A largely defunct programming language these days, though, but one which still holds a fond place in my heart
Microsoft Power Platform – this comprises of the following development tools;
- Power BI – interactive dashboard building tool
- Power Apps – application building tool
- Power Pages – public websites building tool
- Power Automate – a low-code tool for getting different parts of Microsoft to talk to each other. For example, you could build a Timesheet app in Power Apps and use Power Automate to automatically send an email to everyone who hasn’t filled it in this week
Dataverse – this is the secure location where any Power Platform data is stored. Think of it as a server
SPILL formula in Excel – a formula result that veers into multiple rows or columns rather than just one
Power Query –a tool in the back-end of Excel that allows you to get and transform data
ETL –stands for Extract, Transform, Load. In other words: Get this data >> Do something to the data >> Store/load the finished data
