by Alexander Frolov , updated on May 24, 2024
This article will take you on a fascinating journey through Excel Copilot, examining its various features, revealing its untapped potential, and showing how to use it effectively.
In the world of spreadsheet software, Excel has long reigned as the go-to tool for crunching numbers and analyzing data. But what of you had a smart assistant helping you navigate Excel's vast capabilities, giving you expert advice and quick insights. That's the idea behind Copilot - the newest and most innovative feature of Excel. Like a reliable wingman, Copilot uses artificial intelligence (AI) to change the way we interact with spreadsheets.
is an innovative feature that utilizes artificial intelligence (AI) and machine learning algorithms to enhance the user experience in Excel. By integrating these advanced technologies, Copilot provides users with intelligent suggestions, formulas, and insights as they work on their spreadsheets. By analyzing patterns, context, and user behavior, Copilot anticipates the user's needs and offers relevant assistance in real-time.
In simple words, Excel Copilot is like having a smart helper right inside your spreadsheet. It uses artificial intelligence, which is a type of technology that makes computers think and learn like humans. When you're working on your Excel sheets, AI watches what you're doing and suggests helpful things to make your work easier.
For example, if you need to do a calculation or make a chart, you can just ask Copilot using regular words, and it will figure out what you need and help you do it. It's like having a friend who knows a lot about Excel and gives you tips to get your work done faster.
Overall, Copilot doesn't extend beyond what you could accomplish yourself by using various Excel features and functions. The advantage is that you don't have to master Excel's language. Now, you have a single entry point to communicate your intentions to Excel in your own language. This saves you time and makes using Excel easier, especially if you're not an expert at it.
To get access to Copilot features in Excel, all you need to do is to purchase the Microsoft 365 and Copilot subscriptions and install the Excel app:
Note. For the Copilot icon to appear in desktop Excel, you may need to restart the application and sync your Microsoft 365 settings. If you're using Excel on the web, simply refresh the page.
Copilot in Excel is a powerful tool that can help you visualize, analyze and transform data in your spreadsheets in many different ways. Here's what it can do for you:
In summary, Copilot in Excel is a handy helper that makes your work easier and helps you get more done without all the hard work.
Getting started with Excel Copilot is really easy and intuitive. Just follow these simple steps:
Turn your data into a table. Copilot can only work with Excel tables, so if your data isn't already in table format, convert it. You can do this yourself by pressing Ctrl + T or let Copilot convert it for you.
Use Copilot. In the Copilot pane, either follow the suggested options or type your request directly into the text box. You can also explore additional prompts provided above the chat box.
Review and apply results. As Copilot fulfills your request, it may directly implement the solution into your sheet for tasks like highlighting, sorting, and filtering. For actions such as adding formula columns and creating visuals, Copilot will display the results in the pane. Here, you can click on Explain formula for detailed information or hover over action buttons like Insert column to preview the results. If everything looks good, just click the Insert button to add the result to your worksheet.
If you encounter unexpected results, don't worry! Copilot has the handy Undo button for quick corrections.
To get more prompt ideas, click the View Prompts icon at the bottom of Copilot's pane. From there, you can explore the suggested categories such as Create, Understand, Edit and Ask. By delving into a category that aligns with your data's nature, Copilot will provide you with a variety of prompts that you can use directly or modify to suit your needs.
Now, let's explore some practical examples of how Copilot can be used in Excel. By looking at the suggested categories, you can gain insight into the various functionalities and tasks that Copilot can accomplish.
You need to calculate something in your dataset but unsure how to write a formula? Ask AI to build a formula for you:
Note. As with any AI-generated content, it's essential to review and verify anything Copilot creates for you.
Let's begin by exploring the suggested prompts:
Copilot has added this formula, which compares the revenues of Q1 or Q2:
=IF([@[Q1 Revenue]]>[@[Q2 Revenue]], "Q1", "Q2")
Let's ask for a more complex calculation. For instance, you can tell Copilot to create a column that shows the percent difference between Q1 and Q2 revenues.
Nice! Copilot has generated an accurate percent change formula and automatically applied the Percent format to the newly added column:
A custom formula written by Copilot on request. " width="718" height="701" />
Furthermore, when tasked with projecting revenues for Q3, Copilot also performed nicely:
Here are a few more formula requests that Copilot successfully executed for our sample dataset:
With the latest April 2024 update, Excel Copilot becomes even more efficient at creating formulas. Now, you can have multiple formula columns created with a single prompt. Just take a look at how Copilot solves a common problem of splitting full names into first and last names.
In our case, the formulas suggested by Copilot align perfectly with those you'd write manually: Excel formulas to split names.
This feature not only saves time but also reduces the likelihood of errors, making your data management more reliable.
With Copilot, you can quickly make important information in your sheets stand out using colors. Here's how to do it:
For the most part, Copilot highlights values with conditional formatting, so all the changes made to your worksheets are dynamic in nature. That is, when your data changes, the formatting will update automatically to reflect those changes.
For example, you can ask Copilot to highlight the top n values in a key column, and it will do so for you.
Here are a few other prompts you can try:
Note. At this time, Copilot can only color cells based on simple conditional formatting. It is unable to highlight values with two or more conditions. For example, Copilot is unable to fulfill a request like this: "highlight rows that have "Development" in the Project type column and % Change lower than 3%".
Sorting and filtering data in Excel tables is usually simple, thanks to the built-in filter buttons in each column. Copilot can offer additional assistance, especially when filtering across multiple columns. Here's how to get started:
For instance, you can instruct Copilot to filter "Design" projects where the percentage change is negative. In a moment, you'll observe that only two such projects exist in your dataset:
And now, we're diving into the most captivating aspect: analyzing with Copilot. To provide Copilot with more data to work with, I've expanded our sample table by adding a few extra columns as follows:
To start your analysis with Excel Copilot, you have a couple of options:
The main way that Copilot conducts its analysis is through pivot tables and pivot charts. When tasked to show data insights, it generated 2 pivot tables and 6 visuals that vividly illustrate various aspects of our data. Here's what we can glean from these graphs:
If you wish to delve deeper into the analysis, feel free to do so. For example, you can request a breakdown for specific data categories:
Additionally, you can explore various What-If scenarios. Just remember to be accurate and specific with your queries. Copilot thrives on clarity and precision and may struggle to respond to vague or ambiguous questions, as illustrated in the example below:
In case the visuals suggested by Copilot for your data analysis don't quite match your expectations, fear not. You can directly instruct Copilot to create the desired graph by specifying the chart type and the data categories to include.
For instance, you might ask Copilot to generate a bar graph showing Q1 – Q3 revenues by project for the project type "Development":
Copilot can help automate your daily routines by generating VBA code. Not only can Copilot write the code for you, but it also provides brief instructions on how to integrate it into your workbook and execute the macro.
For example, Copilot has written perfect VBA code to hide all worksheets in the current workbook except the active one.
Excel Copilot makes working with spreadsheets easier in many ways. Here are the key benefits you can expect:
While Microsoft Copilot in Excel offers many benefits and advantages, it also has some limitations to keep in mind:
After exploring the main features of Excel Copilot, let's get into the technical details and address some common questions.
Copilot is only available in Excel for Microsoft 365 (for Windows and Mac) and in Excel on the web.
No. To have Copilot features integrated in Excel, you'll need to buy a subscription.
At present, there are two subscription options to choose from:
To make it clear, Copilot cannot be purchased only for Excel. With either subscription, you'll get it integrated with all your Microsoft 365 apps such as Excel, Outlook, Word, and PowerPoint.
Currently, Copilot in Excel cannot be customized. Simply, there are no options available for customization within Excel or in the Microsoft 365 admin center.
If you're wondering why Copilot isn't showing up in your Excel ribbon when you expect it to, here are important things to check:
Additionally, to use Copilot for Microsoft 365, you need to meet the requirements for app version, license, network, and privacy settings: Requirements for Microsoft 365 Copilot.
If you find that the Copilot button is grayed out in Excel, it's likely because you're working on a workbook stored locally on your computer. To activate Copilot, simply save your file to OneDrive or SharePoint and make sure the AutoSave option is enabled.
While Copilot in Excel does provide explanations for formulas and insights in a grammatically correct manner, the generated content may sometimes be inaccurate. This can happen due to misunderstandings or misinterpretations of your requests. Copilot openly notifies users with a transparent warning stating that "AI-generated content may be incorrect". Therefore, it's important to double-check the generated formulas and visuals before inserting them into your worksheets.
As we finish up our journey through Copilot in Excel, it's clear that this tool can change the way we work with spreadsheets. It is designed to help us work more intelligently, not more strenuously. Whether it lives up to Microsoft's goals is something you can decide based on your own experience with it.
