Analyze and transform data with Microsoft 365 Copilot in Excel

  • 3 minutes

Microsoft 365 Copilot in Excel is your data analysis companion, making complex datasets easy to understand. You no longer need to master complicated formulas; instead, you simply converse with Copilot like you would with a colleague. Copilot can help you find patterns, explore 'what-if' scenarios, or get new formula suggestions. Explore your data without changing it, identify trends, create visuals, or get recommendations.

Analyze your data

With Copilot in Excel, analyzing business data becomes a seamless experience. As soon as you input your datasets into Excel, Copilot stands ready to transform these complex figures into comprehensible insights.

There are multiple ways to start and use Copilot in Excel:

  • Select one of the starter prompts or enter your own prompt in the Start with Copilot window that appears in the middle of the Excel spreadsheet. The window appears by default when you open a blank spreadsheet.

    Screenshot of the Start with Copilot window that appears on a new spreadsheet.

  • Select the Copilot icon that appears in the spreadsheet to display a Copilot menu with various options.

    Screenshot of Copilot in Excel menu that appears when you select the Copilot icon.

  • Select the Copilot option that appears in the menu bar to open the Copilot pane. Then select one of the starter prompts or enter your own prompt.

    Screenshot of the Copilot pane in Excel that provides various starter prompts.

Beyond standard data analysis, Copilot in Excel can craft a coherent narrative from the patterns it identifies, ensuring a smooth transition from raw data to meaningful understanding. This process allows your key findings to become powerful drivers in making informed business decisions.

For example, you may prompt Copilot with data analysis requests such as:

  • "Are there any outliers in my data?"
  • "Find and plot the correlation between marketing spend and sales revenue."
  • "Help me forecast next quarter's sales based on current trends."

Moreover, Copilot enables natural language interaction, allowing users to explore data without needing to write complex formulas or scripts. You can ask follow-up questions like “What caused the drop in Q2 revenue?” or “Which product category had the highest growth rate year over year?” and Copilot will sift through your data to deliver answers in plain language, often accompanied by relevant charts or summaries. This capability makes data exploration more intuitive, empowering users at all skill levels to derive actionable insights and support strategic planning with confidence.

Transform your data

With Copilot in Excel, taking control of your data visualization and management is straightforward and intuitive. Copilot empowers you to not only clean and organize your data but also to enhance it through various stylistic and structural modifications. From applying styles, fonts, and colors to extend your data's capabilities through pivot tables and graphs, Copilot helps in every step. You can easily command Copilot to create new columns, define conditions for data representation, and formulate graphs for a comprehensive view.

Explore various data transformation options as you try out different commands, such as:

  • "Sort data by launch date."
  • "Change the table style to light gray."
  • "Create a pivot table where the row is category, the column is supplier, and the values represent the sum of the minimum reorder quantity."
  • "Add data bars to the 'List Price' column to visualize data range and distribution."
  • "Plot 'Inventory Cost' and 'Proposed Inventory Cost' as bars for each category."

The following image shows how Copilot can simplify adding a new column for 'Engagement Rate.' This example exemplifies the simplicity of transforming your data with Copilot in Excel. Use Copilot to craft data representations that are both clear and precise, facilitating informed decision-making.

Screenshot of Copilot in Excel inserting a column for engagement rate.