Tech

It’s not your Dad’s Excel anymore

Expert shares his insights into how generative AI is changing Excel.
article cover

Sean Gladwell/Getty Images

· 5 min read

It’s been about six months since ChatGPT took the world by storm, and finance professionals are working to understand how it will affect them and their organizations. One place that generative AI tools like ChatGPT could have an immediate impact on finance professionals is in Excel, a bedrock tool for the finance function for decades.

Microsoft in particular has announced a series of integrations with the technology underlying ChatGPT into its suite of products, including Excel. The stated goal of these integrations is to make it easier for professionals to do their work, by interacting with software using natural language, and the technology’s ability to toggle between natural language and code or formulas.

To find out how the intersection of Excel and AI may change the work of financial professionals in the months and years to come, CFO Brew spoke to Jeff Lenning, Microsoft Excel MVP and Microsoft Certified Trainer at Excel University, an online training platform.

Lenning got early previews of some of the new AI-based functionality that’s already come online for Excel users, and has been training his students with new chat-enabled methods. After working with the new features, Lenning already sees their potential, even for power users.

“It’s really just helping people to be more productive,” he said, adding, “There’s a shortage of people anyway, so to the extent we can leverage these tools to get our work done faster, I think that makes sense.”

AI technology likely isn’t going to replace any financial analysis jobs entirely in the near future, but Lenning said learning how to incorporate these tools is vital for finance professionals looking for job security.

“The people who know how to use it to leverage their productivity are going to be the people who are going to stick around and are less likely to lose their job because of the increased efficiency” that AI will bring to the field, he said.

Here are five tasks where Lenning says financial professionals can take advantage of AI to be more productive in Excel:

Formula creation: A common AI use case would be to manipulate text values in Excel. In this situation, you’re providing Excel with an example of the output you want to see, and AI generates the formula that would make that output happen.

For example, you might have a column of people’s names formatted with their last name, then a comma, then their first name, like “Smith, John.” If you want to then have another column that extracts just the first or last name, you now don’t need to know the formula to do so, according to Lenning; Excel now “reverse engineers the formula that’s required to create that result and then it fills that formula down.”

Instead of figuring out what formula to use, professionals can simply show what they want the end result to look like.

News built for finance pros

CFO Brew helps finance pros navigate their roles with insights into risk management, compliance, and strategy through our newsletter, virtual events, and digital guides.

“The AI piece looks at the sample data, looks at what you want, and then builds the formula to do that; that’s pretty remarkable,” Lenning said.

Explaining complex formulas and code: We’ve all been there: You receive a spreadsheet with a complicated calculation, or poorly-documented VBA code, and it’s going to take you a lot of time to figure out what it’s trying to do, with what data sources. With the AI tools now available, you can get a description of what the formula or code is doing, in natural language, in seconds.

Lenning suggests copying and pasting that formula into a ChatGPT prompt, saying something like, “Please explain this Excel formula.” The plain-English response “goes through each and every argument,” and is “so good at explaining formulas, functions and things you encounter in your workbook," he said.

Lenning will even ask if a certain macro is safe to run, or if it might transform data in such a way that he should make a backup copy of the original before proceeding.

Getting formulas right: Remembering the precise syntax of a rarely-used formula is a pain, and Excel users frequently find themselves searching the web for blog posts and forum exchanges about how to implement a certain solution.

But now, Lenning said, when you already know some of what you want to do, you can rely on AI to help you out: Tell the AI something like “I want to use this formula on this column to find out this information,” and ask it for the correct syntax to get the desired result.

Exploring your data: The Microsoft Copilot integration in Excel can offer insights about your data, before you spend time exploring it in detail.

As opposed to ChatGPT, which doesn’t have access to any data you don’t specifically provide to it, Copilot is embedded within Excel.

“When you’re chatting with Copilot, it does have access to your data; it has access to your workbook, and it has access to your files,” said Lenning, adding that makes Copilot, “A lot more personalized and more in tune with the context of what you’re working on than ChatGPT.”

Figuring out what analysis to perform: Sometimes you’re sitting there with data, and you don’t even know what would be useful to create. Lenning suggests going to ChatGPT or Copilot with a query like, “How do I quickly summarize a report?”

Adding in more specifics can help, but you can also start at that very basic level and follow a path to a result you like. Despite these advances, Lenning said those using AI capabilities for their work need to be cautious.

The AI “hallucinates sometimes and it’s still a little glitchy,” he noted.—SW

News built for finance pros

CFO Brew helps finance pros navigate their roles with insights into risk management, compliance, and strategy through our newsletter, virtual events, and digital guides.