Copilot is the brand name for Microsoft’s artificial intelligence tools. While they offer some free Copilot features, their two main offerings are the $20 per month Copilot Pro—designed for home and student consumers—and the $360 per year Copilot 365, designed for enterprise customers.
In mid-September 2024, Microsoft announced that Copilot 365 had moved from a preview to general availability. Copilot features are in many products, such as Teams, Word, PowerPoint, Outlook, and OneDrive. This article will focus on the features for Microsoft Excel.
To use Copilot in Excel, you need a subscription to Microsoft 365. Owners of the perpetual Office 2019 or Office 2024 will not have Copilot. In addition to Microsoft 365, you’ll need someone in your IT department to use the Microsoft 365 Admin panel to assign a Copilot license to you.
To use Copilot in Excel, your data has to be stored in OneDrive or SharePoint Online. In addition, the AutoSave feature must be enabled. In the past, your data had to be stored in tables created with Ctrl+T or Format as Table or Insert Table. This requirement has been lifted.
The Copilot icon is found near the right side of the Home tab. The Copilot task pane offers five types of tasks:
- Add new columns to your data by writing a formula
- Summarize your data using pivot tables or charts
- Apply conditional formatting
- Ask Copilot how to do something in Excel
- Advanced analysis using Python. This feature is new as of September 2024 and is in preview for Office Insiders.
Figure 1
The following examples use the data set shown in Figure 2.
Figure 2
If you ask Copilot to calculate a bonus amount, it can write a fairly complex formula making use of a percentile function, as shown in Figure 3.
Figure 3
Sorting, Filtering, and Conditional Formatting are all easy for Copilot. In Figure 4, I asked for the tricky conditional formatting of highlighting the entire row when something is true. Copilot had no problem with this.
Figure 4
In the next example, I asked “Create a pivot table showing the top 2 customers based on Revenue for each Sector.” Copilot offered to insert a new sheet with the pivot table shown in Figure 5.
Figure 5
All these features have been in preview for almost a year. During this time, there would be many times you’d ask for something a bit more complicated and Copilot would say that it could not do things in the same request. With the new Copilot with Excel with Python feature, Excel is now able to perform multi-step analyses using Copilot.
For example, consider the prompt: “Summarize Revenue by Customer and Year. Find all of the customers who have more revenue in 2024 than in 2023 and chart them.”
Copilot tells you that it will use Python to solve the problem and ask you to click Start Analysis. Once it begins, Copilot writes several sections of Python code, first to set up a data frame from your original data. Then, it writes more code to summarize the data by customer and year. Finally, it writes a third block of code to create the chart.
Figure 6 shows the results. At the very top is the Python formula bar, showing a portion of the code generated by Python. Rows 8-19 show a preview of the data frame. Rows 21-35 show the chart of all customers with more revenue this year than last year.
Figure 6
Copilot Beyond Excel
There are many other products at Microsoft that offer Copilot. One that amazes me is the ability for Microsoft Teams to summarize what was said at a meeting that I missed. In the past, if I missed an hour-long status meeting, I might have to go back and watch the recording. Now, I have a one-page summary of the meeting and I don’t have to go back.
Here is one more very cool use of Copilot in OneDrive to analyze financial statements. For this example, I copied an Income Statement, Balance Sheet, and Cash Flows to Sheet1 of a workbook. This data came from the SEC EDGAR database.
I saved the Excel workbook to OneDrive.
In OneDrive, when I hover over the workbook, a Copilot icon appears. Copilot offers to summarize the file or let me ask a question. If you ask for Key Financial Ratios, Copilot in OneDrive is able to understand the Excel file, find the correct numbers for the most recent year, and perform the calculation.
Figure 7 shows the Copilot icon that appears after you hover.
Figure 7
Figure 8 shows a portion of the results of the query.
Figure 8
The speed of improvement in Copilot since it debuted a year ago is amazing. The new Python features in Copilot with Excel allow you to solve problems that are two to three times as complex as what Copilot could do just one year ago.
