How to Analyze Business Data with Copilot in Excel

by Gloria Bakerian | Sep 29, 2026 | Microsoft 365

← View All Microsoft 365 How-To Guides

Copilot in Excel can help you explore a worksheet, identify patterns, answer questions, and create charts or PivotTables. It can accelerate analysis, but important conclusions should always be checked against the underlying data.

Availability note: Copilot capabilities vary by license, Excel version, device, organization settings, and Microsoft rollout. This guide uses the Copilot pane, not the retired COPILOT worksheet function.

Before you begin

  • Use a permitted workbook stored in OneDrive or SharePoint when required by your Excel experience.
  • Make sure the data has clear column headings and no unnecessary blank rows or columns.
  • Remove or anonymize sensitive data that is not needed for the analysis.
  • Know the business question you are trying to answer.

How to prepare your data

  1. Open the workbook and confirm that each column contains one type of information, such as date, customer, category, amount, or status.

    Business data prepared for analysis in Excel
    Arrange the data in consistent columns with clear headings.

  2. Select the data and format it as an Excel table if it is not already a table. Choose Home > Format as Table, select a style, and confirm that the table has headers.

    Formatting business data as an Excel table
    Format the data as a table and confirm that it has headers.

  3. Check for inconsistent dates, numbers stored as text, duplicate headings, and incomplete rows.

How to ask Copilot to analyze the data

  1. Select the Copilot button to open the Copilot pane.

    Opening Copilot in Microsoft Excel
    Open the Copilot pane from the Excel workbook.

  2. Ask a specific question and name the columns you want Copilot to use.

  3. Review Copilot’s response, including any cited cells, formulas, tables, or charts.

    Reviewing a data analysis answer from Copilot in Excel
    Review Copilot’s answer and the worksheet data it used.

  4. Ask a follow-up question if the answer is too broad. For example, request a comparison by month, location, service category, or account owner.

Useful prompts for business analysis

  • “Summarize the monthly revenue trend using the Date and Revenue columns. Identify the three largest changes.”
  • “Compare average response time by Service Category. Show the calculation used.”
  • “Identify possible duplicates based on Company Name and Email Address. Do not delete anything.”
  • “Create a chart showing total sales by month and explain any visible outliers.”

How to create a chart with Copilot

  1. Ask Copilot to create a specific chart and identify the fields for the horizontal and vertical axes.

    Asking Copilot to create a chart in Excel
    Describe the chart and identify the fields Copilot should use.

  2. Review the proposed chart, labels, filters, and aggregation method.

  3. Insert or keep the chart, then use Excel’s normal chart controls to revise the title, labels, colors, or data range.

    Reviewing a chart created with Copilot in Excel
    Check the chart’s source data, labels, filters, and calculations.
Open Copilot, enter a specific analysis prompt, and review the response.

How to verify Copilot’s answer

  1. Confirm the correct table and columns were used.
  2. Recalculate at least one important total or average with a standard Excel formula or PivotTable.
  3. Check whether blanks, filtered rows, duplicates, or date ranges changed the result.
  4. Open the source rows behind any unusual finding.
  5. Do not make financial or operational decisions from a summary you have not verified.
Verifying a Copilot result with an Excel formula
Use a standard Excel formula or PivotTable to verify an important result.

Security reminder: Use sample or approved business data for demonstrations. Do not upload regulated, personal, payroll, medical, or confidential client data unless your organization has explicitly approved that use.

Official Microsoft guidance

Frequently asked questions

Why does Copilot say it cannot analyze my data?

Check that the workbook is saved in the required Microsoft 365 location, the data has clear headings, and the correct work account is active. Converting the range to a table may also help.

Can Copilot change my worksheet?

Some Copilot modes can create or modify workbook content. Review proposed changes and keep a clean copy of important workbooks.

Is an AI-generated chart automatically accurate?

No. Confirm the source range, calculation, grouping, filters, and labels before using the chart.

Related Microsoft 365 guides

Need help adopting Microsoft 365 Copilot?

PCC helps businesses configure licensing, permissions, security, and employee guidance for Copilot. Request a conversation with PCC.