Getting started with Microsoft Copilot in Excel

Microsoft Copilot in Excel transforms the way you work with data management by integrating artificial intelligence (AI). This introduction to Copilot in Microsoft Excel takes you through the extensive features and capabilities that help streamline data analysis, management, and visualization. Copilot combines the power of technology with ease of use, assisting you in creating complex formulas, generating insights, and automating tasks. Discover how to use Copilot for Excel as your personal AI assistant for data management.

Python

Without any programming knowledge, you can perform advanced analyses such as making predictions, conducting risk analyses, and identifying trends. Simply ask what you want to know, for example: “What are the revenue trends for the past 5 years?” Copilot will immediately generate the analysis, complete with charts and tables in a separate folder to ensure the data isn’t lost. You can also make changes to the Python code. This makes Copilot in Excel a powerful tool for professionals who want to quickly gain insights from their data without spending hours entering formulas or setting up complex datasets.

Features of Microsoft Copilot in Excel

With Microsoft Copilot in Excel, you have access to various features, including:

• Data Analysis: Copilot helps you analyze data from your Excel spreadsheets, detecting trends and outliers.
• Generate Insights: Generate insights from all data or specific columns or rows and visualize the insights in charts or pivot tables.
• Focus on the Essentials: Use Copilot to filter and sort data.
• Create Formulas: Let Copilot help you create calculations and formulas.
• Create Charts and Visualizations: Visualize your data in charts or pivot tables to analyze insights visually.
• Highlight Data: Copilot uses conditional formatting to highlight data in your table.
• Clean and Validate Data: Keep your data clean with the help of Copilot.

How Microsoft Copilot in Excel works

Copilot in Excel is located in the ribbon under the ‘Home’ tab. It’s important to note that you can only use the Copilot chatbox if your document is stored in the cloud on OneDrive or SharePoint. Additionally, the data needs to be formatted in a table for Copilot to work.

Note: Copilot in Excel is sometimes limited to tables with a maximum of 2 million cells. Some prompts, such as generating formulas, highlighting, sorting, or filtering data, do not have limits, but may take longer for Copilot to execute.

Some prompts to try:

• “Create a line chart showing the revenue of all products in 2024.”
• “Add a new column that calculates the average monthly sales per salesperson.”

Copilot Olympics: the Excel Battle! 🏆

For the English version, please set the subtitles to English.

Generating insights

Open the Excel document with the data you want to analyze. Ensure that the data is stored in a table. Open the Copilot chat window by clicking the Copilot icon located in the ribbon under the ‘Home‘ tab. Then, click “Analyze” and choose one of the suggested prompts or describe what you want to analyze in a prompt.

Copilot will analyze the data and present the insights in a chart, pivot table, summary, trends, or outliers. Select “add to new sheet” to add the insights to your Excel file.

Use the refresh icon to get new suggested prompts based on the data in your Excel table.

Some prompts to try:

• “Visualize sales per product category.”
• “Show total sales for each product.”
• “Show total ad sales for the ‘East Flanders’ region in the first quarter of 2024.”

Generate All Insights

Get all insights from your table with the ‘Show data insights’ prompt. Open your Excel document, format the data in a table, and open Copilot in Excel in the ribbon. Select the suggested prompt or type “Show data insights” and then click or type “Add all insights to grid.” Copilot will generate a new tab with charts and pivot tables to extract all insights from your data table.

Highlight, sort, and filter

Copilot in Excel can highlight data in tables (using conditional formatting), sort, or filter data to easily focus on key insights. Open your Excel document, format the data in a table, and open Copilot in Excel in the ribbon. Describe in your prompt what you want to highlight, sort, or filter.

Some prompts to try:

• “Bold the top 10 values in the ‘Total’ column.”
• “Highlight the highest values in the ‘Unit price’ column.”
• “Sort the ‘Sales’ column alphabetically from A to Z.”
• “Filter sales for the month of April 2024.”

Generate formula columns

Add columns to your table with calculations based on the data from already existing columns. With a formula column, you don’t need to perform each calculation individually for each row. Make your data a table and open the Copilot in Excel chat window from the ribbon. Describe in your prompt which column you want to add based on which data or choose from one of the suggestions. Then, select “Insert column” to add the column.

Some prompts to try:

• Calculate the percentage share of each sale compared to the total in the ’total’ column.
• Add a column that calculates the total profit for each marketing campaign in 2024.
• Add a column that calculates the number of days between each new sale.

Try these prompts too

Copilot in Excel is currently in a preview phase, which means the features are still under development and improvement. Below are various prompts you can test in Copilot for Excel. These may not work optimally yet.

Calculations:

• “Find the [average] of cells [cell-range].”
• “Calculate the total profit for products with a quantity greater than 50.”
• “Find the standard deviation of the dataset in [column H].”
• “Use the SUMIFS function to calculate the total revenue for [specific criteria].”

Data cleaning and validation:

• “Remove duplicates from [column A].”
• “Convert text to uppercase in [column C].”
• “Extract the year from data in [column D].”
• “Combine first and last names from [columns E and F].”
• “Split text in [column G] using delimiter [,].”

Formatting and data validation rules:

• “Create a rule to highlight cells with values greater than 100.”
• “Apply color scales to visualize data trends in the [table].”
• “Set a data validation rule to only allow whole numbers in column E.”
• “Limit input to specific values ‘High,’ ‘Medium,’ ‘Low’ in column F.”

Tabels and charts: 

• “Create a pivot table to show total revenue per product.”
• “Summarize customer feedback scores using a pivot table.”
• “Create a line chart to show monthly revenue growth.”
• “Compare website traffic from the past six months using a line chart.”
• “Show me a breakdown of [Product X] sales this quarter.”