- Excel has become an absolute essential for accountants and analysts.
- Microsoft’s Excel developers have started releasing new functions in response to user feedback.
- CAs must think hard about how they want to use Excel in their organisations, so it can be developed in the most beneficial way.
We all know that life as an accountant is not all numbers. We’re extremely well rounded professionals with technical, problem-solving, relationship-building and team skills.
But it is mostly about the numbers. And whether it’s an intricate web of group accounts, or a back-of-an-envelope calculation, we entrust our numbers to Microsoft Excel.
Everyone has Excel. It’s intuitive. It mirrors the tabular structures of finance and you can do just about anything with it. As a result, it has become a common language between accountants everywhere.
The only downside to our love affair with Excel is suffering the unreasonable protests of philistine friends and family as we launch Excel into service when planning holidays, buying houses or managing party invitations, personal finances and shopping lists.
Excel is not an addiction… it’s an utter dependency
In the past 20 years, financial systems and enterprise resource planning systems (ERPs) have attempted to close the functionality gap between themselves and the end user. This “last mile” traditionally has been occupied by Excel and is the bane of auditors everywhere. But the changing demands of the end user have stretched this gap and now strings of Excel workbooks and similar tools fill a “last 10 miles” or more.
We’re more dependent on Excel than ever.
Imagine, if you will, that Excel was removed from every computer in the world overnight. The financial systems of every company and country would collapse before breakfast. I’d suggest that Excel is the most important software in the world.
“Imagine, if you will, that Excel was removed from every computer in the world overnight. The financial systems of every company and country would collapse before breakfast.”
Does Microsoft listen to its users?
You might imagine that Excel, and the spreadsheets we create in it, would be top of the agenda at every IT conference and CIO strategy meeting, but for many years accountants and financial modellers have been broadly ignored by IT departments and Microsoft.
The doors to the Excel development team were closed, much like those of Willy Wonka’s Chocolate Factory. No-one went in and no-one came out.
Every three years we received a new version of Excel, but it was generally the same as the previous one.
Please don’t think me ungrateful. I appreciate how difficult it is to change Excel, because billions of spreadsheets depend on it being exactly the same. (Excel can’t change the functionality or parameters of a single function without risking the destruction of the world as we know it.) And we have been blessed with Power Query, Power Pivot and Power BI.
Yet while the extension of spreadsheets upstream (into data sources) and downstream (into visualisation) is fantastic, this has hardly sated the needs of the humble analyst and accountant
New Excel functions: XLOOKUP and XMATCH
Then, on 29 August 2019, Microsoft’s Joe McDaid broke the internet (or one corner of it) by announcing XLOOKUP (the successor to VLOOKUP) and XMATCH (which searches for a specified item in a range of cells, and then returns the item’s relative position).
I woke to a phone already bursting with “have you heard” emails, texts and Facebook tags. Joe’s blog has had more views than all other Excel blogs together. My LinkedIn post that day had almost 30,000 views.
These two new functions are exactly what we’d been asking for. They directly address spreadsheet risks we’ve been wrestling with for years. We were finally being listened to. We mattered. With these functions, Excel-based life will be better and simpler, with substantially lower risk of error.
I predict XLOOKUP will be the most used function in Excel within three years. Universities will teach both functions. Accountants will draw diagrams to explain them to mates at a barbecue. And, of course, Google Sheets will be forced to introduce them.
When Microsoft responds to feedback
While everyone wanted it on their laptop immediately, XLOOKUP was (and still is) only available to Office Insiders, a user group that gets early access to new features and provides feedback to Microsoft developers. And a good job, too!
In early October 2019, Microsoft developers did something extremely rare. Based on user feedback, they added a sixth argument to XLOOKUP. They changed a formula that Excel users were already using, but by adding the argument to the end of the function it didn’t break any spreadsheets that had already been built.
Then on 30 October, again based on user feedback, they swapped the fourth and sixth arguments. I know, unbelievable, right? Any spreadsheet using XLOOKUP would now break if opened in the new version of Excel.
Yet they made the bold call early, while it was still in beta. Once these functions roll out to the mainstream, Microsoft developers will never have the chance again.
There can now be no doubt that Microsoft’s ears are open and accountants are being heard. I’m excited.
Do accountants deserve better?
The introduction of XLOOKUP and XMATCH is an invitation for accountants and analysts the world over to start a wish list for the future of spreadsheets.
But if we’re going to be part of Excel’s future, then we need to earn the right to continue to have a voice. As a community, we must consider carefully how we want to use Excel in our organisations, and the standards we set for those who use it to run our businesses.
Although accountants have adopted Excel and made it our own, we’re also guilty of showing it little respect.
Taking Excel to the cloud
I’ve discussed many times the amazing opportunity that cloud-based platforms create for us accountants and our spreadsheets: real-time collaboration, cell-level history and audit trail, no data size constraints, inter-organisation live data connections, discoverable models, etc. But today, both Excel Online (Excel for the web) and Google Sheets don’t cut it.
There are three things we need to tell the Excel Dev team about any future version of Excel that they want us to adopt:
1. Formula auditing and traceability. We need to be able to visualise and test formula dependents and precedents as we’re working.
2. Add-ins and macros. All add-ins have to work: both third-party add-ins such as OAK and Spreadsheet Advantage, and the add-ins that have been developed over many years within organisations, such as PwC, that power our model build and review work. And we must be able to develop new ones quickly.
3. Keyboard shortcuts. I’ve previously explained the value of keyboard shortcuts in allowing financial modellers to do their job faster. We will not proceed to the cloud with one hand tied behind our backs. Without these, financial modellers lose their superpowers.
But, more importantly, it is simply too risky to use the cloud versions of spreadsheets for financial modelling or anything but trivial spreadsheet calculations.
Not to be taken for granted
We’re all very excited about a future with data analytics, Alteryx, RPA, cloud-based modelling and dashboards, etc, but the spreadsheet will be absolutely no less important.
I love that our comfort with the humble spreadsheet grid has led to Excel being the launchpad into analytics, machine learning and visualisation, because through this lens we will change the finance function and beyond. But in pursuing this innovation we must not lose sight of the core: our best friend, Microsoft Excel.
Microsoft is listening, and now is the time for accountants to lead the debate so Excel evolves into the tool we deserve.
YOUR QUESTIONS ANSWERED
Have a financial modelling or spreadsheet question you’d like answered in Acuity? Send it to firstname.lastname@example.org with ‘Ask Ian’ in the subject line.
FROM THE CA LIBRARY
Using Excel for Business and Financial Modelling: A Practical Guide by Danielle Stein Fairhurst offers step-by-step instructions to solve common business problems using financial models.Download the e-book from CA Library
Excel: sending feedback to the Excel team
Excel guru Bill Jelen explains how to get in touch with the Excel team to make suggestions or give feedback.Download from the CA Library