Ian Schnoor is Executive Director of the Financial Modeling Institute (FMI) has been steeped in Excel throughout his career.

In October, Schnoor and associates Giles Male, Co-founder of Full Stack Modeller, and Paul Barnhurst, Founder of financial planning and analysis consultancy The FP&A Guy, teamed up to launch The Mod Squad: a webcast exploring the fast-growing overlap between financial modelling and artificial intelligence (AI).

Given Schnoor’s finger-on-the-pulse expertise in this arena, Insights enlisted him to talk us through how AI in Excel is opening up new frontiers for users, plus five areas where further technical improvements are necessary to sharpen AI agents up for the Excel setting.

The pros of AI in Excel

Schnoor points out that the AI experience is coming into Excel in two ways:

• Microsoft is building its own native tools inside the app; and
• there are third-party add-ons that users can attach to their Excel software.

The latter are derived from market-leading large language models, such as ChatGPT and Claude Opus.

For users looking to experiment with these new capabilities, Schnoor advises looking into Microsoft’s Copilot features for Excel in the first instance. “Once people can access Copilot, they should start playing around – realising it’s not going to do any work for you, but will help you bounce around ideas on how you may go about solving a problem,” he says.

Agentic AI is readily available

Copilot already comes equipped with its own agent – handily called Agent Mode – for use in Excel. “We’re already seeing users have great success with this when they give it a discrete, specific task,” Schnoor says. “In fact, the more specific the guidance you give these tools, the greater the success. For example, if you add a paragraph into your spreadsheet and say, ‘Use this language to populate cell G20 with a formula,’ Agent Mode will do that for you.”

As well as helping users to brainstorm problem-solving concepts, AI agents in Excel can assist with tasks such as planning, research and assumption validation. “These are great uses of AI tools to speed things up,” Schnoor notes. “Perhaps you could get an agent to recommend structures for a financial model. All of these are good places to start.”

Keep testing

Ultimately, Schnoor stresses, the more imagination and intrigue a user brings into the testing process, the better their experience is likely to be.

“I'm a big believer that strong Excel users demonstrate lots of creativity and curiosity, because there’s lots of different ways to do things,” he says. “So, I would encourage people to tap those qualities when experimenting with these tools – albeit without having overly high expectations that the chosen agent will instantly save you eight hours of work.”

As part of this journey, Schnoor says: “I also believe people are going to have to elevate their Excel skills. Whatever they are today, plan for them to get better. My webcast partner Paul Barnhurst likes to say that AI is a magnifier. If your Excel skills are terrible, and then you use AI, it’s going to magnify your flaws. But if your Excel skills are already very good, AI will enable you to do things faster and better than you could have foreseen.”

The cons of AI in Excel

1. They take shortcuts

“If there’s a shortcut to be taken, an AI agent will take it – and it may not be correct,” Schnoor says. “For example, the agent may not appreciate that it needed to build a formula in response to your query, so it could just end up throwing a dead number into a cell. That forces you to check what it’s done.”

2. They sometimes create formulas that are overbuilt

“There was one time on The Mod Squad webcast when we asked an agent to solve a problem in Excel and it created two, giant lines of code,” Schnoor recalls. “We’re all experts, and it took us a while to understand what the agent was doing. This is another reason why better Excel skills are important: if you ask an agent to solve a problem and it serves up something big and messy, you must be able to understand that output to untangle it.”

3. They often build formulas that are neither flexible, nor dynamic

On another occasion, Schnoor asked an agent to create a formula that would rationalise 1,000 rows of Excel data. The result left much to be desired.

“In one second,” he explains, “it built 1,000, unique formulas in a column – all of which contained dead numbers, or what we call ‘hard-coded values,’ that could no longer be used or copied. So, in building all those unique formulas, it had done in an instant what a human would have taken many hours to do. But that doesn’t make the outcome good.”

4. They will provide different answers to the same problem at different times of asking

Schnoor warns that if you present the exact same problem to an AI agent on three separate occasions, it will produce a different solution each time. One may be right, another may be wrong and the third may be right in principle – but overly complex.

“This is rather concerning,” Schnoor says, “and means you have to spend quite a bit of time iterating and evaluating. Again, professionals require elevated skills to be able to look critically at the outputs.”

5. Like teenagers, they are zealously overconfident

“Even when an AI agent is clearly wrong, it will proudly tell you that it has crushed it and come up with the goods,” Schnoor says. “If you know how to challenge it, then it will admit that it was wrong. Never assume it has nailed the assignment until you’ve triple-checked it.”