No Ticket Needed

Appearance

5 min fix 5 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Format your data as an Excel Table (Ctrl+T), click the Copilot button on the Home tab, and type a prompt in the sidebar like 'Show me a chart of sales by region' or 'Highlight the top 10% of values'.

How to Use Copilot in Excel to Analyze Data ​

Estimated Time to Fix: 5 minutes

Applies to: Microsoft Excel with Copilot (Microsoft 365 Copilot license required)
Article Type: How-To
Last Updated: 2026-07-16

Summary ​

Use Microsoft Copilot in Excel to analyze data, create charts, find trends, and generate formulas using natural language prompts — no formula knowledge required. Ask questions in plain English and Copilot does the analysis.

Prerequisites ​

  • Microsoft Excel (desktop app, Microsoft 365).
  • A Microsoft 365 Copilot license (check with IT if unsure).
  • Your data must be in an Excel Table format (Copilot doesn't work with plain cell ranges).

Instructions ​

1. Prepare Your Data for Copilot ​

Copilot works best with clean, structured data in an Excel Table.

  1. Make sure your data has clear headers in the first row (for example: Name, Department, Sales, Date).
  2. Remove blank rows or columns within your data range.
  3. Convert your data to an Excel Table: select any cell in the range and press Ctrl + T, then click OK.

WARNING

Copilot requires data to be in a Table format. If your data is in a plain range, Copilot may not be able to analyze it. Press Ctrl + T to convert it first.

2. Open Copilot in Excel ​

Access the Copilot panel.

  1. Click the Copilot button in the ribbon (Home tab).
  2. The Copilot panel opens on the right side.

3. Ask Questions About Your Data ​

Use natural language to analyze your data without writing formulas.

In the Copilot panel, type questions like:

  • "What is the total sales by department?"
  • "Which region had the highest revenue last quarter?"
  • "Show me the trend of sales over time."
  • "What is the average order value?"
  • "How many orders were placed in June?"

Copilot analyzes your data and returns answers with tables, charts, or summaries. Click Insert to add any chart or table directly into your spreadsheet.

4. Generate Formulas with Copilot ​

Ask Copilot to write formulas for you in plain English.

Type requests like:

  • "Add a column that calculates profit (Revenue minus Cost)"
  • "Add a column that shows the percentage of total for each row"
  • "Calculate the average order value per customer"
  • "Add a column that flags orders over $10,000 as 'High Value'"

Copilot generates the formula and shows a preview. Click Insert column to add it to your table.

TIP

This is one of Copilot's most powerful features. Instead of Googling "SUMIF vs SUMIFS", describe what you want in plain English and let Copilot write the formula. You can then click the formula bar to see exactly what it created and learn from it.

5. Create Charts and Visualizations ​

Ask Copilot to visualize your data instantly.

Type requests like:

  • "Create a bar chart of sales by region"
  • "Show me a line chart of monthly revenue"
  • "Create a pie chart of expenses by category"
  • "Make a comparison chart of this year vs last year"

Copilot generates the chart and shows a preview. Click Insert to add it to your spreadsheet. You can resize and reposition the chart after inserting it.

6. Highlight and Sort Data ​

Use Copilot to find patterns and organize data.

Type requests like:

  • "Highlight the top 10 customers by revenue"
  • "Sort by date from newest to oldest"
  • "Highlight all rows where sales are below $1,000"
  • "Filter to show only the Marketing department"

Copilot applies conditional formatting, sorting, or filtering based on your request.

7. Verify the Results ​

Always check Copilot's work before sharing or acting on it.

  1. Review formulas Copilot generated by clicking the formula bar to see the actual formula.
  2. Spot-check a few calculations manually to verify accuracy.
  3. If a chart looks wrong, click it and adjust the data range or chart type.

WARNING

Copilot can make calculation errors, especially with complex data relationships or ambiguous column names. Always verify important numbers before including them in reports or presentations.

Troubleshooting ​

TIP

If Copilot gives an unhelpful answer, rephrase your question using the exact column header names from your table. Instead of "show me the numbers", say "show me the total of the Revenue column grouped by Region." Column-specific prompts produce much better results.

Symptom / ErrorPotential CauseSolution
Copilot button doesn't appearNo Copilot license assignedCheck with IT to confirm your Microsoft 365 Copilot license is active.
"I need your data to be in a table"Data is in a plain range, not a TableSelect your data and press Ctrl + T to convert it to a Table.
Copilot gives wrong totalsAmbiguous column names or mixed data typesRename columns to be more specific (e.g., "Q3 Revenue" instead of "Amount"). Make sure number columns don't contain text values.
"I can't analyze this data"Data has too many columns or is too complexSimplify your data by removing unnecessary columns, or ask narrower questions about specific columns.
Chart doesn't match expectationsCopilot chose the wrong chart typeClick the chart, then use the Chart Design tab to change the chart type or data range.