Six Ways to Use AI in Excel
It’s been almost two years since ChatGPT changed the public perception of AI , but the idea of putting generative AI to work can still seem a bit like a scam. While there are many potentially shady ways to use this technology to cut costs and outsource work, there are also legitimate uses for AI in everyday professional activities . In fact, companies like Microsoft are now embedding artificial intelligence tools directly into their work applications in hopes that users will find ways to make the most of technology to improve their work processes.
Excel is one such application: While there are independent third-party tools you can use to leverage generative AI in your spreadsheets, the easiest method is to simply use Microsoft’s own tool, Copilot. The company is rolling out Copilot capabilities into its Microsoft 365 work suite starting in 2023. This means that, with the right setup, you can use generative AI in your spreadsheets to perform a range of tasks.
Who can use Copilot in Excel?
You may be puzzled as to why you don’t see the option to use Copilot after you launch Excel. It’s not you: it’s Microsoft. In fact, the company 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 “pro” subscription to use it. set of artificial intelligence functions.
In addition to features like priority access to the latest OpenAI and Copilot Voice models, a Copilot Pro subscription adds an AI assistant to your Microsoft 365 apps. These two subscriptions combined 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 sign up for a free subscription .
If you don’t want to pay for both, a subscription to Copilot Pro gives you access to Copilot in the web versions of Excel, which Microsoft offers free for everyone. This should be enough to get an idea of what you can do with Copilot in Excel, but the web app has a number of limitations compared to what you can expect from its desktop counterpart.
Another key note: you cannot use Copilot in Excel if your file is saved locally on your computer. If so, you will see that the Co-Pilot button is greyed out. You’ll have to move the spreadsheet to OneDrive to play with the AI tools.
Be careful when using AI for important or precise work.
At first glance, AI may seem like a shortcut: you ask it to do something for you, and voila, task completed. However, remember that generative AI is not infallible; rather, he is prone to errors and mistakes, an effect called ” hallucinations “. Microsoft doesn’t deny this: You’ll notice that whenever you use Copilot in Excel, every result ends with a statement that “AI-generated content may be incorrect.”
This doesn’t mean you need to avoid using Copilot in Excel altogether. Instead, you should carefully review its results. If your job depends on precision and accuracy, be especially careful.
So let’s take a look at some Copilot features that I think might be useful to Excel users. Spreadsheets aren’t my thing, so I think the following may offer support, especially for those of us who may not know exactly what we’re doing when we open up a crowded page of numbers and numbers.
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, visuals are often a useful way to break down this data into something digestible. I know that I’m definitely more likely to understand the meaning of a data set if I see it as a graph rather than a sea of numbers.
One of the key touted features of Microsoft’s Copilot is this type of data analysis: When you click the Copilot button in Excel, you can click the “Understand” button in the sidebar to instruct the AI to analyze your data. You’ll be able to ask Copilot to display your data in charts or pivot tables. Copilot displays this output in the chat feed in the sidebar, so you can check for yourself whether the visual is as expected. If yes, then you can launch it through the “Add to new sheet” button.
Ask the co-pilot to come up with formulas
I am amazed by 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 needed, but ask me to come up with formulas to perform calculations on my data set? Forget it.
This is definitely one of the uses of Copilot that I would rely on most: rather than having to come up with a formula to calculate my company’s total costs or to determine the average number of days a process might take, you can ask Copilot to create these formulas for you. The AI bot can then create a new column or row displaying these calculations.
You can request this from Copilot at any time, but you can also get started by clicking Create in the Copilot menu.
On the other hand, if you tried to use your brain power to come up with a formula and it just doesn’t work, you can ask Copilot to troubleshoot: Microsoft says the bot should be able to help you determine why the formula isn’t working correctly. The same applies to formulas that you don’t understand: the formula may work fine, but if you don’t follow the process, you can ask the co-pilot to explain the formula to you.
Format your tables
Copilot can also help you format your tables. Instead of spending time trying to bold elements yourself or figuring out how to spread data across multiple columns, see if the 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 from one column into two (one for first names and one for last names). You can highlight certain types of data, for example if you want the six most productive months to be highlighted in green and the lowest productive months to be highlighted in red. You can also ask the assistant to bold certain metrics that change as your data changes.
Here’s another example: As YouTuber Chandoo points out , 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 table. Now, in addition to these calendar dates, you also have a corresponding day of the week (for example, Sunday, Monday, Tuesday, etc.).
Talk to the co-pilot 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 the same way you would ask an expert who knows everything about that data, and get informed answers in return.
You can ask Copilot to look for any trends in your data set, such as anything significant that you may have overlooked. On the other hand, you can ask about any outliers in your data, perhaps data points that don’t fit with the rest of the set, that can be used to make decisions in the future. You can ask specific questions about the data that emerges as you analyze it. What might take you a while to find by combing and investigating on your own may be faster if you rely on the AI. (Again, always double check the performance of the AI, co-pilot, or anyone else.)
Generating Python Code in Excel
Excel now has an Advanced Analysis button in the Copilot menu that lets you analyze your data and then write and run Python code to visually display that data. This is sort of a combination of several of the tasks I covered above, and if you have experience with Python, you can take a look at the code that Copilot produces to understand what’s going on.
Use tips to get started
If you’re like me and lack the imagination to come up with any of these AI clues yourself, Microsoft has a whole bunch of ideas for inspiration on the Copilot Lab website . You can focus specifically on Excel and specific tasks and assignments, or just browse the entire collection of tips.