Customize data integration, analysis, and visualization with Copilot in Excel

  • 5 minutes

Note

The prompts discussed in this unit are available with both Copilot Pro and Microsoft 365 Copilot licenses unless otherwise noted.

In addition to highlighting and sorting data based on key points, Copilot in Excel allows you to create visual representations of your data. This unit discusses how to prompt Copilot to generate charts and automate formulas, and discusses how you can create PivotCharts using Copilot in Excel.

Retrieve data insights

Copilot in Excel can help you uncover valuable data insights, which are pieces of information extracted from your data to guide better decision-making. For example, it can identify top-performing products, analyze sales trends over time, and summarize customer feedback to highlight common themes. These insights enable you to make informed decisions, optimize your strategies, and improve overall productivity in Excel. When you're prompting, be as specific as you can so that Copilot returns the most accurate and appropriate response.

How to work with data insights

Open Copilot and enter a specific prompt based on your data. For instance, if your spreadsheet contains data about product sales, you could enter Which product sold the most in March? or What are the top three states for total sales in March?.

Screenshot of how Copilot can present analyzed data in charts, graphs, and tables.

Other prompts that return insights on your data include:

  • Which product sold the most in California?
  • How many widgets did we sell in March?
  • What were the top three states for total sales in the first quarter?

Summarize text insights

Text insights in Excel is an advanced Copilot​​​​​​​ feature designed to streamline and enhance data interpretation. Using this feature, you can swiftly summarize rows of textual data, extracting valuable insights and themes with ease to gain a better understanding of your data and to identify key themes.

How to make text insights work for you

  1. In your workbook, open the sheet containing the text you wish to analyze.

  2. Select Copilot from the Home ribbon.

  3. Type your request in the prompt box. ​​​​​​​​​​​​​​​​​​​​​

    Prompt examples:

    • Review the customer feedback on this sheet and identify the major themes.
    • Summarize the comments.
    • Can you tell me about the bug descriptions in a professional tone with two bullet points?
    • Summarize the URLs into a set of bullet points, where each bullet point corresponds to a website category.

  4. Submit your prompt to begin analyzing the text.

  5. Once the analysis is complete, review the summary provided and submit any more prompts to further distill the data to your specifications.

  6. After Copilot analyzes your data, review the insights to find ones that help you make the most of your data.

    • To add a chart to a sheet, select Add to a new sheet.
    • If multiple insights are available, you can add all insights to a grid in a new sheet.
    • To undo this, select Undo.

Generate formulas using Copilot in Excel

Microsoft Excel is a powerful app that allows you to manipulate data in so many ways. Remembering how to apply formulas can be daunting. Copilot in Excel helps you work with your data by generating formula column suggestions, showing insights in charts and PivotTables, and highlighting interesting data. It can assist with writing complex formulas, including those that work with text, and can handle more complicated formulas, including nested ones.

How to generate formulas using Copilot in Excel

  1. Navigate to the Home ribbon and select Copilot to open the Copilot pane on the right-hand side.

  2. In the Copilot pane, enter prompts to describe what you want to achieve. For example, you can type:

    • Create a bar graph showing the sales growth between Q2 and Q3.
    • Bold the top three values in Annual Sales.
    • Add a new column showing the percentage difference between column A and column C.

  3. To generate formula columns, you can use prompts like:

    • Suggest a formula column.
    • Show suggestions for formula columns.
    • Add a column that calculates the number of days after the product launch event.
    • Add a row that sums up the total sales for each category.

  4. Copilot provides formula suggestions with an explanation of how each formula works. You can view the explanation by selecting Show explanation.

  5. Once you review the suggested formula, select Insert column to add the formula column to your table.

Create custom functions

Custom functions in Copilot in Excel allow you to create reusable formulas tailored to your specific needs. These functions can simplify complex calculations and automate repetitive tasks, making your data analysis more efficient. Imagine you're a financial analyst and need to create a custom function to calculate the internal rate of return (IRR) for multiple investment projects. Copilot helps you by generating these custom functions based on your natural language prompts.

How to insert a custom function using Copilot in Excel

  1. Open the workbook you want to insert a custom function into, navigate to the Home ribbon and select the Copilot icon to open the Copilot pane.

  2. Describe the custom function you want to create in the Copilot pane. For example, you can type:

    • Create a custom function to calculate IRR for these projects.
    • Create a custom function to calculate the average sales per quarter.
    • Generate a custom function to convert temperatures from Celsius to Fahrenheit.

  3. Review the generated function. Once you're satisfied, Insert function to add it to your workbook.

  4. Use the custom function in your Excel worksheet to ensure it works as expected. You can do this by entering the function in a cell and providing the necessary inputs.

  5. If needed, you can edit the custom function to refine its behavior. You can do this by modifying the function directly in the formula bar or by providing reiterating your prompt with Copilot.

Create PivotTables using Copilot in Excel

A PivotTable allows you to summarize, analyze, explore, and present large amounts of data. It helps you see comparisons, patterns, and trends in your data by organizing and rearranging the data dynamically. You can use PivotTables to perform various tasks such as sorting, filtering, grouping, and calculating data without needing complex formulas. Imagine you're a sales manager and need to analyze quarterly sales data to identify top-performing products and regions. Copilot can assist you in creating a PivotTable with ease.

How to create a PivotTable

  1. Open Excel and ensure your workbook is stored on OneDrive or SharePoint.

  2. Navigate to the Home ribbon and select the Copilot button to open the Copilot pane on the right-hand side.

  3. Enter prompts to describe what you want to achieve in the Copilot pane. For example, you can type:

    • Create a PivotTable.
    • Create a PivotTable to summarize sales by product and region.
    • Create a PivotTable with the sum of sales with Month in rows and Category in columns.
    • Summarize with PivotTable.
    • Can you make a pivot table from this data and show a summary by Report Date?

  4. Copilot generates a PivotTable based on your prompt. If you don't provide specific details, you see a generic PivotTable, and you can ask Copilot to show you another with more specific data as necessary.

You can then customize the PivotTable by dragging and dropping fields into the Rows, Columns, Values, and Filters areas in the PivotTable Fields pane.

Copilot in Excel boosts productivity by providing valuable data insights, text insights, formulas, custom functions, and PivotTables. These features help you uncover patterns, summarize feedback, generate complex formulas, and organize data efficiently. By using these capabilities, you can make informed decisions and optimize your strategies, leading to better results in Excel.