Six Ways You Can Use AI With Excel

Credit: Ian Moore

It's been nearly two years since ChatGPT changed the public's perception of AI, and yet the idea of using generative AI at work can still feel a bit like, well, cheating. While there are many potentially suspect ways to utilize the technology to cut corners and outsource work, there are also legitimate use cases for AI in your professional day to day. In fact, companies like Microsoft are now injecting AI tools directly into their work apps, in the hopes that users will find ways to make the most of the technology to enhance their workflows.

Excel is one such app: While there are independent, third-party tools you can use to tap into generative AI with your spreadsheets, the most straightforward method is to simply use Microsoft's own tool, Copilot. Since 2023, the company has been rolling out Copilot capabilities in its Microsoft 365 work suite, which means as long as you have the right setup, you can use generative AI with your spreadsheets to accomplish a number of tasks.

Who can use Copilot in Excel?

You might be confused why, after firing up Excel, you don't see the option to use Copilot. It isn't you: It's Microsoft. The company actually requires two separate subscriptions to use Copilot with its desktop work suite: You need Microsoft 365 so you can access apps like Word, Excel, and PowerPoint, but you also need Copilot Pro, the company's "professional" subscription, to use its suite of AI features.

Among features like priority access to OpenAI's latest models and Copilot Voice, a Copilot Pro subscription adds the AI assistant to your Microsoft 365 apps. Those two subscriptions put together aren't necessarily cheap: Copilot Pro is $20 per month, while Microsoft 365 costs $6.99 per month, or $69.99 per year—unless you quality for a free subscription.

If you don't want to pay for both, a Copilot Pro subscription does give you access to Copilot in the web versions of Excel, which Microsoft offers for free for everyone. That should be enough to get a sense for what you can do with Copilot in Excel, but there are a number of limitations to the web app versus what you can expect from its desktop counterpart.

Another key note: You cannot use Copilot in Excel if your file is saved locally to your computer. If it is, you'll see the Copilot button grayed out. You'll have to move the spreadsheet to OneDrive to play around with AI tools.

Be cautious when using AI for important or precise work

AI can seem like a shortcut on the surface: You ask it to do something for you, and presto, task complete. However, remember that generative AI is not infallible; rather, it is prone to error and mistakes, an effect called "hallucinating." Microsoft doesn't deny this: You'll notice whenever you use Copilot in Excel, each result ends with the statement that "AI-generated content may be incorrect."

That isn't to say you need to avoid using Copilot in Excel altogether. Instead, you should review its outputs carefully. If your work relies on accuracy and precision, take extra caution.

With that, let's take a look at some of the Copilot features I think might be of use to Excel users. Spreadsheets are not my thing, so I imagine the following could offer support, especially for those of us that might not know exactly what we're doing when we open up an overflowing page of numbers and figures.

Manage Charts, PivotTables, trends, and more

So, you have a lot of data in your spreadsheet, and you're not sure what you're looking at. Of course, a visual is often a helpful way of breaking down that data into something digestible. I know I'm definitely likelier to understand the implications of a dataset if I see it as a graph rather than a sea of digits.

One of Microsoft's key advertised Copilot features is this type of data analysis: When you click the Copilot button in Excel, you can hit the "Understand" button in the sidebar to task the AI with analyzing your data. You'll be able to ask Copilot to display your data in charts or PivotTables. Copilot displays this output within the sidebar chat feed, so you can take a look for yourself to see if the visual matches up with expectations. If it does, you can run with it via the "Add to a new sheet" button.

Ask Copilot to come up with formulas

I am amazed by Excel power users who intuitively know how to use formulas to perform calculations in their spreadsheets. I could probably fill up a table with data if need be, but ask me to come up with formulas to perform calculations in my dataset? Forget about it.

This would definitely be one of the Copilot uses I'd rely on most: Rather than figure out a formula for calculating my company's total costs or for finding the average number of days a process may take, you can ask Copilot to create these formulas for you. The AI bot can then create a new column or row that displays these calculations.

You can prompt Copilot at any time for this, but you can also get started by clicking "Create" under the Copilot menu.

On the other hand, if you tried to use your own brain power to come up with a formula, and it simply isn't working, you can ask Copilot to troubleshoot: Microsoft says the bot should be able to help you identify why a formula isn't functioning correctly. The same applies for formulas you don't understand: A formula might be working fine, but if you aren't following the process, you can ask Copilot to explain the formula to you.

Format your spreadsheets

Copilot can also help format your spreadsheets for you. Rather than spend time trying to bold items yourself, or figure out how to spread data through multiple columns, see if a Copilot prompt can achieve the same result.

For example, you can ask Copilot to split your data across multiple columns by any metric you specify, perhaps if you want to split full names in your spreadsheet from one column into two (one for first names and one for last names). You can highlight specific data types, like if you want to see your top six most productive months highlighted in green, and your lowest productive months highlighted in red. You could also ask the assistant to bold certain metrics that change as your data changes.

Here's another example: As YouTuber Chandoo highlights, you can ask Copilot to take dates formatted by day/month/year, extract the day of the week from each, and print the results as a new column at the end of the spreadsheet. Now, in addition to these calendar dates, you also have their corresponding day of the week (e.g. Sunday, Monday, Tuesday, etc.).

Talk to Copilot about your data

The benefit of conversational chatbots is they're designed to understand standard questions. In theory, you should be able to ask Copilot questions about your dataset as you would ask an expert who know all about that data, and receive informed answers back.

You could ask Copilot to look for any trends in your dataset, like something substantial that you may have overlooked yourself. On the flip side, you could ask about any outliers in your data, perhaps data points that don't fit within the rest of the set, that could be used to inform decisions going forward. You could ask specific questions about the data that pop up when analyzing it. Something that might take you a while to find when combing through and investigating for yourself could be faster when relying on AI. (Again, always double-check the work AI produces, Copilot or otherwise.)

Generate Python code within Excel

Excel now has an "Advanced Analysis" button in the Copilot menu that can put together an analysis of your data, followed by writing and running Python code to display that data visually. It's sort of an amalgamation of multiple tasks I cover above, and if you're experienced with Python, you can take a look at the code Copilot produces to see what's going on.

Use prompts to get you started

If, like me, you lack the imagination to come up with any of these AI prompts yourself, Microsoft has a whole bunch of inspiration available on its Copilot Lab site. You can choose to focus on Excel specifically, as well as certain tasks and jobs, or just browse the entire collection of prompts.