Analyze data workflows in Excel with Copilot

As a business professional at a growing organization, you receive a dataset with quarterly sales figures and product performance. Your manager asks you to analyze the data, create charts for a leadership presentation, and summarize key insights—all under a tight deadline. You're juggling spreadsheets and deliverables, but you have a powerful assistant at your side: Copilot in Excel.

In this unit, you'll explore how to use Copilot in Excel to analyze data, analyze text columns for themes and sentiments, generate charts, and build workbooks using Edit with Copilot. Along the way, you'll learn how to guide Copilot with effective prompts and choose the right tools to streamline your workflow.

Search and import data using Copilot

In today's fast-paced business environment, quickly accessing relevant data is essential for making informed decisions. Microsoft Copilot Chat in Excel streamlines this process by allowing you to search for, locate, and import data from various sources—all within your spreadsheet. Whether you need industry benchmarks, recent trends, or specific figures, Copilot Chat helps you efficiently gather the information you need to analyze and visualize your data, saving you valuable time and effort.

How to import data from a public web source

1. Open Excel.

2. Select the Copilot icon in the lower-right corner of Excel to open the Copilot pane.

3. Select the globe icon and choose web grounded content.

4. In the Copilot pane, ask Copilot to find the data you're looking for. You can copy and paste some of the example prompts that are displayed on the screen or create your own.

   Tip

   To include content from the web, words such as "industry benchmarks," "recent trends," or "using web sources" (if enabled by your organization) are suggested.

5. If the data that Copilot found is what you want, select Insert to new sheet to copy the data into a new sheet. To add the data into your current sheet, select the copy icon and paste the data where you want it.

Search for data with prompts like:

• Search the web for a table of countries and their exchange rates.
• Get (blank) from my file about (blank).

Use Copilot in Excel to find trends in your data

Identifying meaningful trends in large datasets can be challenging, but with Excel's Copilot feature, you can quickly uncover patterns and insights that matter. By utilizing advanced AI capabilities, Copilot streamlines the process of analyzing your data, helping you make informed decisions with less effort. Whether you're seeking summaries, visualizations, or specific trends, Copilot in Excel provides intuitive support to turn raw data into actionable information.

How to find trends using Copilot in Excel

1. Open Excel and select the Copilot icon in the lower-right corner to open the Copilot pane. Copilot is ideal for quickly understanding your data and generating summaries.

2. Ask Copilot to find the insights you're looking for. Enter your question in your own words, or modify one of the example prompts. The more specific your question, the better the answer Copilot can find. If you want your insights in a specific format, such as a PivotTable, specify that in your prompt. Copilot analyzes your data to show insights as charts, PivotTables, summaries, trends, or outliers.

3. Review the insights provided to find ones that help you the most.

4. To add a generated chart to a sheet, select Add to a new sheet. If Copilot provided you with multiple insights, you can add all insights to a grid in a new sheet.

Example prompts: How many people in this list reside in the United States? Summarize the quarterly sales by region and highlight the top three.

Analyze text with Copilot in Excel

Not all insights come from numbers. Customer feedback, survey responses, and product reviews often contain valuable information that's difficult to process manually at scale. Copilot's text analysis feature helps you uncover what's in your text columns quickly—without needing formulas or manual tagging.

When you point Copilot at a column of text, it identifies recurring themes and sentiments, cites specific data examples, and automatically inserts a new column with category labels. This makes feedback analysis and open-ended survey data far more manageable.

How to analyze text using Copilot in Excel

1. Open your workbook in Excel and make sure it's saved on OneDrive.

2. Open the Copilot Chat pane and reference your text column in a prompt:

   • Review the customer feedback column and identify the major themes.
   • Analyze the comments column and label each row by sentiment.
   • Identify the top three topics mentioned in the feedback column.

3. Copilot analyzes the text and inserts a new labeled column with categories or sentiment tags.

4. Refine with a follow-up prompt if needed:

   • Group the labeled rows by theme and show me a count.
   • Summarize the most common complaints in three bullet points.

Generate charts and visualize data

Once you've explored your data and identified key insights, the next step is to communicate those findings clearly—especially when presenting to leadership. Visuals like charts and graphs can make complex information easier to understand and more impactful. With Copilot in Excel, you can generate compelling visuals from your data using simple prompts, saving time and ensuring your message lands effectively.

How to use Copilot to generate charts and visualize data

1. Ask Copilot Chat to create a chart. You don't need to pre-select data—Copilot identifies the right table or range based on your prompt:

   • Create a bar chart showing sales by region.
   • Generate a line chart of quarterly sales trends.
   • Visualize product performance with a pie chart.

2. Refine your chart with prompts like:

   • Add labels to the axes.
   • Highlight the top three regions in the chart.
   • Change the chart type to a column chart.
   • Create a chart from this table that has vendor budget on the X axis and headcount on the Y axis.
   • Use 10,000 as the baseline. Create a line chart showing change over time. Show data labels and gridlines.
   • Show a chart comparing Position in Range to diversity statistics such as generation, ethnicity, and gender.

Use Copilot to understand and create formulas

Formulas are an essential feature in Excel, yet they can often be complicated and hard to decipher, particularly in extensive or inherited spreadsheets. Excel's Copilot can assist you in generating formulas for individual cells and by offering straightforward and concise explanations of what formulas do and how they work.

How to create formulas

Copilot streamlines creating precise calculations by generating formulas for individual cells, making tasks like percentage changes or text extraction easier and eliminating manual formula entry. There are two ways to generate a formula:

• Choose Suggest a formula directly in the Copilot pane.
• Prompt Copilot:
  • Calculate the percentage increase from cell A1 to cell B1
  • Extract the first name from a full name in cell C1

Edit with Copilot in Excel

Copilot in Excel now includes an Edit with Copilot mode where it acts as a workbook-building assistant. Instead of making changes one request at a time, you describe what you want to accomplish and Copilot handles the multi-step work—creating tables, adding formulas, generating charts, and formatting data—all within a single conversation.

This is particularly useful for building new templates from scratch, restructuring an existing workbook, or completing complex models that would otherwise require many manual steps.

How to use Edit with Copilot

1. Open Excel and open the Copilot Chat pane. Look for the Let's edit together welcome message, which indicates Edit with Copilot is active.

2. If the message isn't visible, go to the Tools menu and select Edit with Copilot.

3. Describe what you want to build or change:

   • Build an expense tracking template with formulas for monthly totals and variance.
   • Add a summary sheet that pulls totals from the Sales and Expenses sheets.
   • Create a dashboard with a chart for each product category.

4. Copilot carries out the steps and explains what it changed. Review the results and continue the conversation to refine further.