Dr Liam Bastick, Director at financial modelling specialists SumProduct, has a war story. A few years ago, Bastick appeared at a conference in Brisbane, in his secondary role as an Excel Most Valuable Professional (MVP): a Microsoft-designated guru who regularly tests out new Excel functions. 

Outside the auditorium, Bastick sat with a panel of other MVPs on a special helpdesk for answering delegates’ queries. A financial controller approached him. Every Friday for two-and-a-half years, her board had handed her a huge mass of company data that they had wanted fully reported and interpreted by Monday morning. 

She had been doing this task on Saturdays, with her husband taking the children out for the day to give her space. Her laptop was creaking under the weight of the data. At the helpdesk, Bastick found that the Excel package on her computer included Power Query, which she knew nothing about. On the spot, he showed her how to execute her weekly task in eight seconds flat. “Have you ever seen elation and despair on someone’s face at the same time?”

According to Bastick, even today lots of accountants don’t know that Power Query exists on Excel. Or they say they have heard about it, but don’t have time to look into it. However, he stresses: “They are exactly the people who should look.”

Power Query is a game changer

In Bastick’s view, a facility with Excel’s most powerful functions must be backed with critical insights on the software’s pros and cons, based on a detailed working knowledge. He says: “A trick question I like to ask accountants is, ‘What do you think of the VLOOKUP function?’ If they simply say, ‘I like it,’ I’m not interested. While it’s the third most popular function on Excel, there are lots of things wrong with it.”

Bastick explains that VLOOKUP is fine for basic tasks, but if you’re looking to purchase a multi-million-dollar pharma company that has 75 products – of which 30% are successful – you’ll need to go beyond VLOOKUP to, say, find the probability of making a certain profit margin or get an idea of what the dividends would be.

Fellow MVP David Benaim, Founder of data services company Xlconsulting and prominent member of ICAEW’s Excel Community, corroborates Bastick’s view on accountants’ grasp of Power Query, calling it “something almost no one knows about”. He hails the function as a “game changer”, not just for providing a means to transform different datasets from one format to another, but for assisting the design and understanding of spreadsheets in general. “It should be marketed to users as super-important,” he says.

In Benaim’s assessment, there are people who “think Excel”, and those who don’t. “The former are proficient with lookup functions, conditional formulae and PivotTable-style analytics,” he says. “Modern users will use multi-table setups linked via XLOOKUP, or Power Query. The latter, meanwhile, will do things like merging cells and not thinking about the output, putting inconsistent formulae in columns or typing over formulae. The results will be neither successful nor robust.”

Best modern Excel functions

Some of the software’s newer capabilities “are not aimed at advanced users”, highlighting checkboxes, in-cell pictures, the Navigation Pane and the Translate function. “I’m using that one for a bilingual project in English and French,” says Beniam. Accountants should also consider using:

• Dynamic Arrays, which enable users to return multiple cells based on a single formula;
• TEXTSPLIT and REGEXEXTRACT, which can extract certain text from cells; and 
• VSTACK, which combines multiple tables into one. 

“Users should also get familiar with how to use AI tools, such as the ChatGPT or Microsoft Copilots, to write complex formulae,” Benaim says.

That said, plunging into Excel with a functions fever is not always the best approach, according to John Tennent, EMEA Regional Director at business acumen learning specialists Corporate Edge. In his view, Excel is a means to an end, rather than an end in itself. 

“Users should not be daunted by being off the pace of the latest functions,” he says. “There are often numerous ways to create an Excel solution. Many of the new functions provide development speed (eg, array formulae) or simpler formulae (eg, =TEXTSPLIT), yet if your solution has produced the required answer, it has served its purpose.”

You may sometimes realise that a solution will involve things you’ve never done before, he explains. He suggests starting with a pencil and a piece of paper, and thinking about what you need your model to be able to do. Define the outputs, then the steps required to generate those outputs. Finally, identify the data that will be fed into the calculations. 

Once you have a plan, then go to the spreadsheet. “The ability to learn is often stimulated by a need to do so. But most people just start in Cell A1 and type away without a clear plan of what they ultimately need to achieve.”

Tennent highlights ICAEW’s Excel Community as a particularly useful resource for SMEs with just one or two finance people who have no one else to talk to. “Their capability horizon will be restricted to the sum of their knowledge,” he says. “Unless they talk to people outside, they will never broaden that horizon. So it’s vital to encourage them to engage with other users with similar goals, so they can understand the various choices and functions they should start to learn and use to be able to get better.”

Further reading

• 100 new Excel features from the first half of the 2020s
• For up-to-date hints and pointers on a host of different functions and features, visit ICAEW’s Excel Tips & Tricks Index.