It’s dubbed the accountant’s Swiss Army Knife thanks largely to the wide array of functions it offers for data management, modelling, analysis and reporting, among others. But as Microsoft Excel celebrates its 40th birthday, its appeal shows no sign of abating.

Chris Webb is a programme manager in Microsoft’s Fabric team. He’s also a self-professed Excel aficionado who recently spoke at an ICAEW event to celebrate the spreadsheet’s significant birthday. “I think there was a feeling around five years ago that Excel was a bit stale,” Webb says. “But I really feel like Excel has got its mojo back again in the last couple of years.” 

Recent functionality is bringing expert capabilities to the masses and making some of the more advanced features more accessible to even occasional users of Excel, Webb says.

Despite an explosion in the availability of data modelling and analytics tools in recent years, accountants continue to turn to Excel because its accessibility and user interface remove the friction of having to talk to IT about crunching numbers or analysing data. 

However, most people only use a small proportion of the spreadsheet’s functionality and some of the biggest drivers of efficiency are features that very few people seem to know about, Webb adds.

Power Query

Although not unique to Excel, the Power Query function was introduced natively into Excel in 2016. It is a feature for populating data into Excel, a perennial issue for spreadsheet users. “Something that people still wrestle with is how to get data from other sources – for example, another Excel spreadsheet, a database or data on a web page.” 

In practice, users waste huge amounts of time copying and pasting data into Excel and then checking the integrity of data – both data accuracy and format errors – before they can perform Excel functions, Webb explains. 

More recently, Power Query was extended to Excel Online, allowing Mac and Linux machine users to enjoy the same Power Query functionality as their PC colleagues. Excel now also connects to Microsoft’s data analytics platform Fabric. 

AI enhancement for data cleaning

Some of the questions people have with analysing data in Excel include ‘How do you lay out a table? Do these numbers look right? Are there any outliers? Has somebody put a decimal point in the wrong place? Has somebody formatted a number as text?’ At ICAEW’s Excel at 40 event, Webb illustrated how artificial intelligence (AI) enhancements in Excel are already reaping rewards for data cleaning and data preparation. 

“My demo included a spreadsheet with a list of fruit names, but the word ‘grapes’ was deliberately misspelled as ‘garps’. Excel is now able to look at that table and query whether something is correct – not just a spell check thing but using a bit more intelligence.”

CoPilot

CoPilot is an AI-powered assistant designed to boost productivity and streamline workflows. CoPilot functionality in Excel already allows you to perform conversational queries on your data. It also allows any text comments in a spreadsheet to be analysed. “For example, maybe you've got some survey data, and you want to analyse whether comments are positive or negative, you can do that with a copilot function,” Webb says.

But did you know that CoPilot can also highlight functionality to users who know what they want to achieve but perhaps don’t know how to do it, or don’t know the relevant Excel terminology to achieve their goals? “People talk about PivotTables as being one of those great cliff edges in learning Excel. If CoPilot can say, ‘I understand you want to analyse this data and here's the PivotTable I've created for you,’ you don't need to know which buttons to press to be able to do that,” Webb adds. 

Agent Mode

Microsoft recently released the new Agent Mode in Excel, which is currently only available in Excel Online. Agent Mode uses generative AI to bring expert-level capabilities of Excel including advanced modelling to the masses.

“Previously, if you asked CoPilot to summarise data in a table, it would say: here's the table. Agent Mode not only builds the PivotTable, but it will try to understand what you're doing and help you create formulas to analyse the data. This is beyond a nice demo tool; it's doing useful, interesting stuff and increasing our efficiency,” Webb says.

The LET function

Vast lines of code in an Excel formula might be a necessary evil, but the result is often impenetrable to all but a few. The LET function in Excel makes complex formulas easier to read and understand by breaking them down into easily digestible chunks.

“It's perhaps not something that the average user would use, but there are some instances where you can't avoid having a complex and long formula in a single cell. The LET function allows you to break that down into individual steps so you can read it and write it much more easily as a result.”

The LAMBDA function

The LAMBDA function allows users to create custom and reusable functions. “One problem people have with spreadsheets is that you've got multiple cells with very similar formulas in them, and if you want to make a change, it can get very complicated and it's easy to make a mistake, or for your code to be inconsistent.”

With the LAMBDA function, you can create your own custom function that can be reused across multiple cells. But it means you've only got one place to make that change. “For accountants, investing a bit of time learning this will definitely make your life better and easier,” Webb says.

Future developments

According to Webb, generative AI offers huge potential to make tools like Excel even easier to use, making complex functionality less scary and helping users to learn more quickly. “Copilots are about making it easy for you to do the job you need to do more efficiently, without being intrusive. It's not replacing you. It's about working with you.”