How to use Copilot in Excel with examples

What is Copilot in Excel?

Excel Copilot 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.

How to access Copilot in Excel

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:

1. Get Microsoft 365 subscription. Ensure you have a Microsoft 365 subscription that supports Copilot, such as Microsoft 365 Personal, Family, or Business.
2. Obtain Copilot subscription. Choose the Copilot plan corresponding to your Microsoft 365 subscription and purchase it. If your organization has purchased Copilot for you, ask your admin to log into the Microsoft 365 admin center and assign a Copilot license to you.
3. Install Excel 365 or log into a web app. Install Excel 365 on your desktop and sign in with your Microsoft account. Or log into the Excel web app using your Microsoft account.
4. Look for Copilot icon. Once you have purchased the required subscriptions, the Copilot icon will show up in your Excel ribbon, granting access to its features.

What Copilot in Excel can do

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:

• Data analysis. Copilot looks at your tables and brings important information into focus. You can then dig deeper by questioning Copilot.
• Modeling. When working on financial modeling, you can explore different scenarios by asking "what-if" questions and providing prompts such as "model how a change to X would affect Y"
• Visualizations. AI helps you create charts and graphs to visualize your data in a clear and understandable way.
• Writing formulas. Copilot assists in writing formulas for your calculations, making complex formula creation easier and more accessible.
• Organizing and cleaning data. Copilot can quickly clean up messy data for you. It can remove extra spaces, merge values from different columns, highlight duplicates, sort, filter, and more.
• Suggestions. Get intelligent recommendations for formulas, functions, and actions based on your context.
• Insights. Uncover interesting insights within your data, like trends, outliers, or correlations between different parts.
• Automating tasks. Copilot can even write VBA code to help you automate common tasks. This is like creating kind of shortcuts for things you do often.

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.

How to use Copilot in Excel

Getting started with Excel Copilot is really easy and intuitive. Just follow these simple steps:

1. Run Excel. Launch Excel 365 on your computer, or if you prefer, log into the Excel web app using your Microsoft account.
2. Open cloud-stored workbook. Open the workbook stored on OneDrive or SharePoint where your data resides.
3. Activate Copilot. On the Home tab of the Excel ribbon, click the Copilot button to activate it.
4. 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.
5. 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.
6. 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.

Explore more prompt suggestions

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.

Tips and notes:

• If the Copilot button appears inactive (grayed out) in your Excel, it's likely because you've opened a file stored locally on your computer. Copilot can only operate on files stored on OneDrive or SharePoint. To activate Copilot, try saving your file to the cloud or open another file directly from the cloud.
• When making custom queries or analysis requests, provide as much detail as possible. Clearly identify the columns by their names. The more specific you are, the better Copilot can help.
• If you don't have your own data to experiment with, then you can start with a sample table that Copilot has ready for you.

Excel Copilot examples

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.

Get multiple formula columns at once

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.

1. You provide Excel Copilot with a prompt, such as "Split the name column into first name and last name". Or you can be more precise and mention exactly which column you want to extract the information from, like "Extract the first and last name from column C and insert into 2 new columns".
2. Copilot then suggests the appropriate formulas, which you can copy and paste manually.
3. Alternatively, you can review the suggested formulas, and with a single click, insert them into your worksheet.

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.

Analyze with Copilot

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:

• Navigate to the Analyze category in the Copilot pane and choose from the suggested prompts.
• Alternatively, you can opt to Show data insights.

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:

• Design projects yield the highest profits.
• Research projects experienced the most significant percentage increase in revenue from Q1 to Q2.
• Profit margins for most projects fall within the 3% to 10% range.
• The most common budget range is between $56K and $71K.
• There is a strong correlation between budget and revenue.

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:

Excel Copilot benefits

Excel Copilot makes working with spreadsheets easier in many ways. Here are the key benefits you can expect:

• Natural language queries. You can ask Copilot questions using normal words, and it will give you answers in charts, tables, or formulas. Simply type your questions or command in everyday language, and Copilot will understand and respond accordingly. This makes it easy to perform tasks without needing to remember specific function syntax.
• Speed up your tasks. Copilot can help you complete routine tasks, such as data cleaning, sorting, and filtering, much faster. It can identify patterns and anomalies in your data, allowing you to focus on analysis rather than manual data manipulation.
• Accessible for all skill levels. Copilot is designed to be accessible for users of all skill levels. Whether you're new to Excel or have been mastering it for a while, Copilot is easy to use for everyone.
• Continuous learning and improvement. AI keeps getting better as it learns from how you use it, so it becomes more helpful over time. This means that the more you use Copilot, the more effective it becomes at understanding your needs and providing relevant assistance.

Limitations of Excel Copilot

While Microsoft Copilot in Excel offers many benefits and advantages, it also has some limitations to keep in mind:

• Copilot is only available for Excel files (.xlsx or .xlsm) that are stored on OneDrive or SharePoint with AutoSave turned on. When working with unsupported file types or files saved locally on your computer, the Copilot button will remain inactive.
• Currently, Copilot understands prompts in English, French, German, Italian, Japanese, Brazilian Portuguese, and Simplified Chinese. Also, Microsoft plans to add more languages in the future.
• It cannot perform certain common tasks such as comparing tables or looking up and pulling matches.
• It only works within Excel tables and is limited to handling tables of up to two million cells.
• Copilot can be slow, especially on large tables, sometimes taking 30 seconds or more, and it may even hang in some cases.

Excel Copilot FAQs

After exploring the main features of Excel Copilot, let's get into the technical details and address some common questions.

Practice workbook for download

Using Copilot in Excel - examples (.xlsx file)