How to Use Copilot in Excel for Data Analysis

Microsoft Copilot in Excel lets you analyze spreadsheet data by asking questions in plain English. You type a prompt like "show me a chart of monthly sales by region," and Copilot returns the chart directly in your sheet. No formulas, no pivot table setup, no VBA. The main prerequisite is a paid Microsoft 365 Copilot license, data formatted as an Excel Table, and a file saved to OneDrive or SharePoint.

What You Need Before You Start

Copilot in Excel is not included in every Microsoft 365 plan. As of early 2026, it is available only with a Microsoft 365 Copilot license, a paid add-on for business and enterprise subscriptions. Eligible base plans include Microsoft 365 Business Standard, Business Premium, E3, E5, and equivalent enterprise tiers.

A note on timing: Microsoft is discontinuing free Copilot Chat access inside Excel, Word, PowerPoint, and OneNote for all Microsoft 365 business users on April 15, 2026. After that date, continued access to Copilot in Excel requires the full Microsoft 365 Copilot add-on, priced at $30 per user per month. If your organization has been using Copilot without a paid add-on, confirm your licensing status before that cutoff.

You also need Excel desktop (Windows or Mac) or Excel on the web, the file saved to OneDrive or SharePoint, and AutoSave enabled. Copilot requires a cloud-connected file in most configurations; locally saved workbooks without cloud sync may not trigger the Copilot panel.

How to Format Your Data for Copilot

This is the step most users skip. Copilot in Excel only works with data formatted as an Excel Table. If your data sits in standard cell ranges without table formatting, Copilot either errors out or returns unreliable results.

To format your data as a table:

1. Click anywhere inside your data range
2. Press Ctrl+T on Windows or Cmd+T on Mac
3. Confirm the table range in the dialog and check "My table has headers"
4. Click OK

Once your data is a table, Excel adds alternating row colors and filter arrows to the header row. That structure is what Copilot reads.

Data quality rules that matter:

• Use a single header row. No merged headers, no blank rows at the top.
• Avoid merged cells anywhere in the table body.
• Keep data types consistent within each column. Do not mix dates and text in the same column.
• Remove blank rows inside the table.
• Name your columns clearly. "Q1 Revenue" works better than "Column D" because Copilot uses column names to interpret your prompts.

Data size: Copilot handles tables of hundreds to several thousand rows reliably. For very large datasets, performance degrades and Copilot may only sample the first 50 rows in some query types. According to Microsoft's community forums, users with tables above 100,000 rows report inconsistent sampling behavior. If you are analyzing data at that scale, Copilot in Excel is not the right tool.

Opening Copilot in Excel

Once your table is formatted and the file is saved:

1. Click anywhere inside the table
2. Go to the Home tab in the ribbon
3. Click the Copilot button (a sparkle icon, typically on the far right of the Home tab)
4. A panel opens on the right side of the screen

Alternatively, a small sparkle icon may appear when you click a cell inside the table. Clicking that also opens the Copilot panel.

If the Copilot button does not appear, either your license does not include Copilot or the feature has not been assigned by your admin. Contact your Microsoft 365 admin to confirm license assignment under the admin center.

Prompts That Work

The most reliable prompts are direct and specific. Vague prompts return vague output. Here are examples that produce consistent results:

Summarize and highlight:

• "Highlight the top 10 rows by revenue"
• "Show me which months had below-average sales"
• "Flag rows where the return rate is above 15%"

Create visualizations:

• "Create a bar chart showing total orders by region"
• "Build a line chart of revenue over the last 12 months"
• "Compare Q1 and Q2 performance by product category in a chart"

Calculate and aggregate:

• "What is the average deal size by sales rep?"
• "Show total revenue for each product line"
• "Which customer segment has the highest refund rate?"

Add calculated columns:

• "Add a column that calculates profit margin for each row"
• "Insert a running total column next to the revenue column"

PivotTable creation:

• "Create a PivotTable that breaks down sales by region and quarter"

What Copilot Does Well vs. Where It Struggles

Works well:

• Simple aggregations: sum, average, count, max, min by category
• Generating standard chart types from clean, structured tables
• Creating PivotTables from a single well-formatted table
• Highlighting rows that meet a condition
• Adding calculated columns for common operations

Struggles with:

• Multi-table analysis. Copilot operates on one table at a time. If your data is spread across multiple sheets or tables, consolidate it first.
• Complex nested formulas. Copilot can generate IF statements and VLOOKUP functions, but often produces errors when logic involves multiple conditions or cross-sheet references.
• Non-table data. Spreadsheets with totals rows, subtotals, or mixed header structures cause Copilot to misread the schema.
• Statistical analysis beyond descriptive stats. For regression, correlation matrices, or hypothesis testing, use Python in Excel or a dedicated analytics platform.

Accuracy: Copilot in Excel can generate plausible but incorrect calculations. A 2026 analysis by Office Watch documented multiple cases of Copilot generating syntactically valid formulas that returned wrong values due to incorrect range references. Always verify Copilot output against source data before sharing results.

The Python Integration Option

For more advanced analysis, Copilot in Excel can generate Python code that runs directly in the Excel grid through the Python in Excel feature, available in Microsoft 365 on Windows. You can prompt Copilot with "Write Python code to calculate a 30-day rolling average for the sales column" and it will generate the script. This requires no Python knowledge, but review the generated code before running it, particularly on sensitive data.

When Excel Copilot Is Not the Right Tool

Copilot in Excel works well for single-file, single-table analysis on clean, well-structured data. It is not suited for:

• Files with more than a few hundred thousand rows
• Analysis that requires combining data from multiple sources or formats
• Workflows where you need to repeat the same analysis across different datasets without reformatting
• Teams that need audit trails or reproducible analysis notebooks

If your data is messy or lives outside of Excel, restructuring it to meet Copilot's table requirements can take longer than the analysis itself. VSLZ handles file uploads from CSV, Excel, or database connections and runs analysis from a plain English prompt without requiring any upfront table restructuring.

Checklist Before Your First Prompt

• File saved to OneDrive or SharePoint
• AutoSave enabled
• Data formatted as an Excel Table using Ctrl+T
• No merged cells, blank rows, or mixed column types
• Microsoft 365 Copilot license assigned to your account
• Copilot button visible in the Home tab ribbon

Work through this list if Copilot is not responding or producing errors. The table formatting and license steps account for the majority of issues users report.