It’s been almost two years since ChatGPT changed public perception of AIand yet the idea of ​​using generative AI at work can still feel a bit like cheating. While there are many potentially dubious ways to use technology to save costs and outsource work, there are also legitimate use cases for AI in your daily professional life. In fact, companies like Microsoft are now injecting AI tools directly into their business applications, in the hope that users will find ways to make the most of the technology to improve their workflows.

Excel is one of these applications: although there are independent applications, third-party tools that you can use to leverage generative AI with your spreadsheets, the easiest method is to simply use Microsoft’s own tool, Copilot. Since 2023, the company deployed Copilot capabilities in its Microsoft 365 workflow suite, meaning that as long as you have the right setup, you can use generative AI with your spreadsheets to accomplish any number of tasks.

Who can use Copilot in Excel?

You may not understand why, after launching Excel, you don’t see the option to use Copilot. It’s not you: it’s Microsoft. The business actually requires two separate subscriptions to use Copilot with its office work suite: you need Microsoft 365 to be able to access applications 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 the latest OpenAI models and Co-pilot’s voicea Copilot Pro subscription adds the AI ​​assistant to your Microsoft 365 apps. These two subscriptions together aren’t necessarily cheap: Copilot Pro costs $20 per month, while Microsoft 365 costs $6.99 per month or $69.99 per year—unless you have the quality of a free subscription.

If you don’t want to pay for both, a Copilot Pro subscription gives you access to Copilot in the the canvas versions of Excel, which Microsoft offers free to everyone. This should be enough to get an idea of ​​what you can do with Copilot in Excel, but there are a number of limits to the web application compared to what you can expect from its desktop counterpart.

Another key note: you can’t use Copilot in Excel if your file is saved locally on your computer. If so, you will see the Copilot button grayed out. You will need to move the spreadsheet to OneDrive to play with the AI ​​tools.

Be careful when using AI for important or specific work

AI can seem like a shortcut on the surface: you ask it to do something for you, and presto, the task is done. Remember, however, that generative AI is not infallible; rather it is prone to errors and mistakes, an effect called “mind-blowing“Microsoft doesn’t deny this: you’ll notice that every time you use Copilot in Excel, each result ends with the statement that “AI-generated content may be incorrect.”

This doesn’t mean you should completely avoid using Copilot in Excel. Instead, you should carefully examine its results. If your job relies on accuracy and precision, be especially careful.

With that, let’s take a look at some of the features of Copilot that I think Excel users might find useful. The spreadsheets are not my thing, so I imagine the following might offer some support, especially for those of us who may not know exactly what we’re doing when we open a page overflowing with numbers and figures.

Manage charts, pivot tables, 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 useful way to break this data down into something digestible. I know that I am certainly more likely to understand the implications of a data set if I see it as a graph rather than a sea of ​​numbers.

One of the main features announced by Microsoft for Copilot is this type of data analysis: when you click the Copilot button in Excel, you can press the “Understand” button in the sidebar to load the AI analyze your data. You can ask Copilot to display your data in charts or pivot tables. Copilot displays this output in the sidebar thread, so you can take a look for yourself to see if the visual matches expectations. If so, you can run it via the “Add to new sheet” button.

Credit: Microsoft

Ask Copilot to suggest plans

I marvel at experienced Excel users who intuitively know how to use formulas to perform calculations in their spreadsheets. I could probably populate a table with data if need be, but ask me to come up with formulas to perform calculations in my dataset? Forget it.

This would definitely be one of the uses of Copilot that I rely on the most: rather than finding a formula to calculate the total costs of my business or to find the average number of days a process can 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.

Credit: Microsoft

You can ask Copilot to do this at any time, but you can also start by clicking “Create” in the Copilot menu.

On the other hand, if you’ve tried using your own brain to come up with a formula and it just isn’t working, you can ask Copilot to fix the problem: Microsoft says the robot should be able to tell you help identify why a formula is not working correctly. The same goes for formulas you don’t understand: a formula may work fine, but if you don’t follow the process, you can ask Copilot to explain the formula to you.

Format your spreadsheets

Copilot can also help you format your spreadsheets. Rather than spending time trying to bold things yourself or figure out how to spread data across 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 the full names in your spreadsheet in one column into two (one for first names and one for last names). You can highlight specific types of data, for example if you want to see your most productive six months highlighted in green and your least productive months highlighted in red. You can also ask the wizard to bold certain metrics that change as your data changes.

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

Talk to Copilot about your data

The advantage of conversational chatbots is that they are designed to understand standard questions. In theory, you should be able to ask Copilot questions about your data set as you would an expert who knows everything about that data and receive informed answers.

You can ask Copilot to look for any trends in your data set, such as something important that you yourself may have overlooked. On the other hand, you can ask about outliers in your data, perhaps data points that don’t fit with the rest of the set, and that could be used to inform future decisions. You can ask specific questions about the data that appears when you analyze it. Something that might take you a while to find when you browse and investigate on your own might be faster when you rely on AI. (Again, always check the work produced by AI, Copilot or otherwise.)

Generate Python Code in Excel

Excel now has an “Advanced Analysis” button in the Copilot menu that can perform analysis of your data, followed by writing and running Python code to visually display that data. This is sort of an amalgamation of several tasks I cover above, and if you’re experienced with Python you can take a look at the code produced by Copilot to see what’s going on.

Use prompts to get started

If, like me, you lack the imagination to create one of these AI prompts yourself, Microsoft has a A whole bunch of inspiration available on its Copilot Lab website. You can choose to focus specifically on Excel, as well as certain tasks and tasks, or simply go through the entire set of prompts.