Microsoft Copilot in Excel: A hands-on look at key features
By now, you’ve probably at least heard of Microsoft’s AI Assistant, Copilot. It’s been integrated into many of Microsoft’s business and office applications, bringing features designed to streamline your work and boost productivity. In Excel, Copilot boasts several game-changing features that aim to simplify the workload of the user, including:
Formula generation and suggestions
Prompt Gallery
Data Insights
Data Import
Charts and Pivot Tables
Conditional Formatting
We have tested these features to give you a breakdown of which ones are most effective at saving time or reducing your workload, and which ones still need some refinement before they reach their full potential.
It is worth noting that Copilot is still in its earlier stages of development, so it is still evolving, even as we speak.
Licensing & File Storage
Before you begin using Copilot in Excel, note that you must have the required licensing to utilize these features:
A Microsoft 365 License, such as Business Standard or E3
An add-on license for Copilot for Microsoft 365, or
the standalone Copilot Pro license
Additionally, your Excel file must be saved to OneDrive or SharePoint with auto-save turned on. Copilot will not open in Excel if these conditions are not met.
Who can benefit from Copilot in Excel?
For users who are new to Excel and still learning its features, Copilot is an excellent tool to guide them through the fundamentals. It offers interactive tutorials, step-by-step instructions, and helps minimize errors by suggesting improvements or corrections. Experienced Excel users may not find Copilot quite as useful. However, Copilot can still assist them with automating data analysis and generating valuable insights to support decision-making.
Generate formulas using plain language
One of Copilot’s most practical features in Excel is its ability to generate formulas using plain language. Instead of hunting down the right formula on the web or within Excel, or typing it out yourself, you can simply ask Copilot to do it for you.
For example, when we asked Copilot to calculate the discounted price in cell D5 in the clip below, it correctly inserted a formula and gave us the option to add the new row, with little manual input required. This not only saves time, but also helps cut down on formula errors, which is especially useful for newer Excel users. If you prefer speaking over typing, you can use voice prompts too!
Demo of using plain language to generate a simple formula
Another exciting finding was that Copilot can effectively perform an XLOOKUP, and it can do so by being prompted using plain language, as demonstrated below.
Copilot successfully performs an XLOOKUP when prompted using plain language.
Some hiccups we observed while testing this feature include inconsistent performance when clusters of cells are selected in your file. We also noted that you may need to occasionally reword your prompts if you are not getting the result you’re looking for—and sometimes, entering the same prompt twice can yield different outcomes.
Prompt Gallery
Copilot in Excel includes a gallery of prewritten prompts, which is useful if you’re not sure how to phrase what you need help with or what you are trying to accomplish. Access the gallery by clicking the icon as shown below.
Here is a brief tour of the Prompt Gallery, where you can search prompts by task, job type, or saved prompts.
If there are prompts in the gallery that you use frequently, you can bookmark them and save them under “Your Prompts” for easy access—no need to sift through the entire gallery each time.
Bookmark and save regularly used prompts for easy locating.
We found the Prompt Gallery useful, particularly because the built-in prompts don’t require any rewording. Copilot tends to respond faster and more accurately to these prewritten prompts than custom ones.
If you’re looking for guidance on writing effective Copilot prompts, refer to our blog on the subject—>
Data Insights
Copilot’s data insights feature in Excel is intended to help users quickly understand and analyze their data. It can often provide written summaries or point you toward the right calculation, which is genuinely helpful. However, it doesn’t always allow you to insert those insights directly into your spreadsheet, which limits its usefulness in some scenarios.
Copilot can also suggest formulas based on your data and prompts. Still, this functionality can be inconsistent—it often requires carefully worded questions to get the intended results, so you may need to try rephrasing your prompt a few times before Copilot can process your query. Two key limitations are Copilot’s inability to analyze data across multiple sheets and its tendency to struggle with more complex queries. Some trial and error may be necessary to get the most out of this feature.
Data Import
Copilot also allows importing data directly from the web or files saved in SharePoint or OneDrive, such as PDFs, Word documents, Excel files, or PowerPoint files. While this is a promising feature in theory, it still needs some refinement before we deem it efficient or helpful because, although Copilot can perform data imports, we found it to be noticeably slow. It often takes longer than it would for you to manually search for and import the data on your own. Thankfully, if you attempt to import data through Copilot and change your mind due to the lag, you can press the “Stop Generating” button.
Charts/Pivot tables
Another functionality of Copilot in Excel is creating visual representations of your data in charts, graphs, and pivot tables. Copilot can generate pivot tables based on natural language prompts, which can definitely save users some time, especially those who aren’t experienced or comfortable creating them from scratch. Copilot can also produce simple charts and graphs rather quickly.
That said, formatting the legend and adding a title to your graph or chart requires a small amount of manual effort. This feature offers a helpful starting point but isn’t fully hands-off.
Copilot can generate a visual graph or chart when prompted.
Conditional Formatting
Copilot can apply conditional formatting considerably well for straightforward tasks. In the example below, you can see that when asked to highlight totals that exceed a certain amount, Copilot will effectively apply that formatting. This feature is useful for quickly spotting trends or outliers in your data without manually navigating conditional formatting menus. This feature is a reliable time saver for basic data visualization.
Copilot can assist with applying basic conditional formatting to your data
Overall, Copilot has great features and even greater potential in Excel. At this early stage, a seasoned Excel user may not get much out of Copilot that they can’t already achieve just as quickly or even faster without it. Still, if you’re new to the application or haven’t spent much time using it, Copilot is a great assistant and instructor who can answer questions about the application, familiarize you with Excel’s features, and even perform some simple tasks to assist with your workload. Remember, Copilot is still in its infancy development-wise, so there is much room for it to grow, and we’re excited to see how it continues to evolve.
If you’re curious about Copilot features in other applications, check out our other Copilot blogs here—>
View Microsoft documentation to dive deeper into Copilot features in Excel—>
Don’t hesitate to reach out to us if you’d like to learn more about how Copilot can streamline your processes.