Copilot + Excel

How to Use Microsoft Copilot for Data Analysis in Excel

Sagnik BhattacharyaBy Sagnik Bhattacharya14 Mar 2026Updated 25 Sep 20269 min read
Coding Liquids blog cover featuring Sagnik Bhattacharya for Excel Copilot for Data Analysis — A Practical Guide, with data analysis cards, charts, and insight panels.
Coding Liquids blog cover featuring Sagnik Bhattacharya for Excel Copilot for Data Analysis — A Practical Guide, with data analysis cards, charts, and insight panels.

Microsoft Copilot's data analysis capabilities in Excel go far beyond simple formula suggestions. It can identify trends, spot outliers, generate hypotheses, and create visualisations — all from conversational prompts. Here's how to use it effectively.

The Complete Excel Course with AI Integration thumbnail

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

This tutorial is written for analysts, ops managers, founders, and students who already know basic Excel (Tables, pivots, SUMIFS) and want to add Copilot as a faster first-pass analysis layer on top. You do not need a data-science background. You do need a plan that includes Copilot in Excel — Microsoft 365 Personal, Family or Premium, a Copilot Chat-eligible business plan, or a Microsoft 365 Copilot licence (not free Copilot in the web browser — that cannot read your workbook). The file can be saved to OneDrive, SharePoint or, on Windows and Mac, your own computer.

Follow me on Instagram@sagnikteaches

By the end you will have: (1) a reliable data-prep checklist Copilot expects, (2) a library of exploratory, statistical, and segmentation prompts that work reliably, (3) a 6-step end-to-end analysis workflow, (4) a Troubleshooting reference for the five most common failures (greyed-out button, wrong totals from text-typed numbers, hidden-rows inclusion, "I can't help with that" refusals, slow responses), and (5) a calibration of where Copilot is genuinely reliable versus where manual analysis still wins.

Connect on LinkedInSagnik Bhattacharya

If you want the wider Excel + AI context, start at the Excel formulas and analysis hub — it links every Copilot, ChatGPT, Gemini, Gemma, and Claude workflow on this site.

Subscribe on YouTube@sagnikteaches

Setting Up for Analysis

Before asking Copilot to analyse your data:

  1. Format your data as an Excel Table (Ctrl+T) — Copilot no longer refuses a plain range, but it reads Tables most reliably.
  2. Use descriptive column headers ("Revenue" not "Col3") — Copilot uses headers as prompt anchors, so ambiguous names ("Amount", "Value", "Col1") lead to incorrect assumptions.
  3. Save the file as a normal .xlsx workbook — OneDrive, SharePoint and (on Windows and Mac) local files all work, but a Strict Open XML file or one checked out in SharePoint shows "Unsupported file state".
  4. Ensure no merged cells, no blank rows inside the Table, and consistent data types per column — clean messy data before analysis.
  5. Convert numbers-stored-as-text back to real numbers (use VALUE() or Paste-Special Multiply by 1) — this is the single most common source of Copilot returning zero or wrong totals.

Real-world scenario. A retail ops analyst ran "Summarise weekly sales" on a 52-week sheet and got back summaries for only 40 weeks — the other 12 were numbers-stored-as-text from an imported CSV. After a single Paste-Special Multiply on the Revenue column, the same prompt returned the full year with correct totals. Copilot never flagged the issue; it silently ignored the text rows.

Beginner pitfall. Formatting the data as a Table, then naming it something generic like Table1. When Copilot's response refers to tables by name, a sheet with three Table1/Table2/Table3 becomes unnavigable. Rename each Table via the Table Design tab (e.g. tblSales, tblProducts) before you prompt — the extra 10 seconds saves minutes per session.

Analysis Prompts That Work

Exploratory Analysis

  • "What are the key trends in this data?"
  • "Summarise this data and highlight anything unusual"
  • "Which month had the highest growth rate?"
  • "Compare Q1 and Q2 performance"

Statistical Insights

  • "What's the correlation between marketing spend and revenue?"
  • "Are there any outliers in the profit column?"
  • "Show me the distribution of order values"
  • "Calculate the moving average for the last 3 months"

Segmentation

  • "Break down revenue by customer segment and region"
  • "Which product category has the best profit margin?"
  • "Show me the top 10 and bottom 10 performing stores"
  • "Split the sales by channel and show me what percentage each channel contributes"
  • "Group customers by order frequency — one-time, occasional, repeat, loyal"

Real-world scenario. A D2C founder preparing for a board meeting used the prompt sequence "Summarise this sales data → break this down by channel → for each channel, show me the top 3 SKUs by revenue → calculate month-on-month growth for the top SKU in each channel". Four prompts, under three minutes, produced an insight slide that would have taken 40 minutes of pivot-table work. She spent the saved time on interpretation instead of extraction.

Beginner pitfall. Asking one massive compound prompt ("Show me revenue by region, by channel, by product category, with growth rates and margin percentages, and highlight outliers") — Copilot tends to answer only the first or second clause and quietly drops the rest. Break compound prompts into a sequence of follow-ups. Each follow-up stays within the context of the previous response, so you are not repeating yourself.

Working with Copilot's Responses

Copilot responds with a combination of text insights, charts, and data tables. For each response:

  • Insert — Ask Copilot to "add this to a new sheet" to keep the analysis (in edit mode it builds the charts and PivotTables straight into the workbook)
  • Iterate — Ask follow-up questions to drill deeper
  • Challenge — Ask "Why?" or "What's driving this?" for root cause analysis

Building a Complete Analysis Workflow

  1. Start broad: "Summarise this sales data"
  2. Identify patterns: "Show me the monthly trend"
  3. Drill down: "Break this down by region"
  4. Find anomalies: "Are there any unusual months?"
  5. Forecast: "Based on this trend, what can we expect next quarter?"
  6. Visualise: "Create a chart showing all of this" — for design tips, see professional chart formatting

Take your analysis further by building interactive dashboards from Copilot's insights.

Limitations to Remember

  • Copilot may not understand domain-specific terminology — rephrase in simpler terms
  • Large datasets (100K+ rows) may take longer to process — for truly massive data, consider Power Pivot
  • Always verify calculations independently, especially for financial reporting
  • Copilot only brings in other workbooks, web results, or connected sources when you point it at them — check its citations before trusting imported figures

Copilot vs Manual Analysis

Copilot is excellent for exploratory analysis — quickly understanding what's in your data. For precise, repeatable analysis (monthly reports, financial models), combine Copilot's insights with traditional formulas and pivot tables. Use Copilot to discover, then build robust formulas to automate.

Real-world scenario. A finance team uses Copilot every month-end to exploratorily review the GL close — "any unusual entries this month?", "which accounts have the largest variance vs last month?" — and then rebuilds the final reviewed variances as SUMIFS-based worksheets with cell comments explaining each variance. The formulas are auditable; Copilot's output would not survive an external audit trail review.

Beginner pitfall. Trusting Copilot's totals without cross-checking. Copilot's answers look authoritative and polished, which makes it easy to miss when it quietly ignored text-typed numbers, filtered-out rows, or a column whose name it misinterpreted. Rule of thumb: before you quote a Copilot number in a meeting, check it against a =SUBTOTAL(9, column) or a pivot-table equivalent. Five-second sanity check, avoids hours of embarrassment.

Troubleshooting Common Copilot Errors

These are the five most common problems when running data analysis with Copilot in Excel, and the exact fix for each.

1. Copilot button is greyed out or missing

Three causes in order of likelihood: (a) the button has moved — Copilot now opens from a button anchored in the lower-right corner of the sheet, not the Home tab. Fix: look there first, or right-click it and choose Move to ribbon if you prefer the old spot. (b) Your plan does not include Copilot in Excel — free Copilot in the web browser does not count. Fix: File → Account, select Update Licence and restart Excel; if Copilot is still absent, contact your IT admin. (c) Excel is out of date, on the Semi-Annual Enterprise Channel, or has connected experiences switched off. Fix: update Excel, ask IT to move you to Current or Monthly Enterprise Channel, and under File → Account → Account Privacy → Manage Settings turn on "Experiences that analyse your content" and "All connected experiences".

2. Copilot returns wrong totals ("zero" or impossibly low numbers)

Cause: numbers stored as text. Imported CSVs, data pasted from web, and files from legacy systems frequently save numeric columns as strings. Copilot sums only the recognised numbers, silently drops the text ones, and presents the partial total as if complete. Fix: select the numeric column, Paste Special → Values → Multiply by 1 in an empty cell; or wrap the column in a helper column with =VALUE(TRIM(A2)). A small green-triangle warning in each cell's top-left corner is the telltale sign.

3. Copilot ignores filtered rows when you expected it to

Cause: Copilot defaults to the entire Table regardless of active filters. If you filter to Q4 and ask "what was total revenue", it will return the full-year revenue, not Q4. Fix: name the scope in the prompt explicitly — "What was total revenue where Quarter equals Q4?" — or convert the filtered view into a new Table first.

4. "I can't help with that" or "I'm not able to analyse this data"

Cause: usually data shape. Copilot refuses when it sees merged cells, blank header rows, or multiple Tables in a single sheet with ambiguous references. Fix: unmerge all cells (Home → Merge & Center → Unmerge), remove blank header rows, ensure one Table per sheet, and convert loose ranges into an actual Excel Table (Ctrl+T) so the boundaries are unambiguous. If the refusal persists, paste a small 10-row sample into a new sheet and ask the same question to confirm it's the data, not the prompt.

5. Copilot takes 60+ seconds to respond or times out

Cause: datasets above ~100,000 rows, workbooks with many large pivot caches, or files with heavy volatile-function usage (OFFSET, INDIRECT, NOW) that recalculate on every Copilot read. Fix: for large datasets, either pre-aggregate with Power Query before asking Copilot (e.g. weekly rollups instead of raw transaction lines), or use Power Pivot for the full dataset and Copilot only on smaller summary tables. Also close unused workbooks — Copilot scans all open workbooks for context, which slows large sessions.

Frequently Asked Questions

Can Copilot analyse data in Excel?

Yes. Copilot can summarise, segment, calculate statistics, spot outliers, and create charts from natural-language prompts — provided your plan includes Copilot in Excel (Microsoft 365 Personal, Family or Premium, a Copilot Chat-eligible business plan, or a Microsoft 365 Copilot licence). It works most reliably when your data is in an Excel Table. It is strongest on exploratory analysis ("what's interesting here?") and weaker on precise reporting requiring audit trails.

How do I use Copilot for data analysis in Excel?

Prepare the data (Table, clean headers, saved as .xlsx), open the Copilot pane from the button in the lower-right corner of the sheet, and ask questions conversationally. Start broad ("summarise this data"), then drill into specifics ("break this down by region", "show the top 10"), then ask why ("what's driving the drop in August?"). Iterate — each follow-up keeps the previous context.

Why is Copilot greyed out in my Excel?

Most commonly because the button has moved to the lower-right corner of the sheet, because the account's plan does not include Copilot, or because Excel is out of date or has connected experiences switched off. Check File → Account and select Update Licence; update Excel; and turn on "All connected experiences" under Account Privacy → Manage Settings.

Is Copilot accurate for financial reporting?

For production financial reporting, no — use Copilot for exploratory scanning and rebuild final numbers with SUMIFS / pivot tables / Power Query so you have an audit trail. Copilot can miscount when columns contain mixed data types, can ignore filtered rows, and cannot explain its calculation methodology the way a formula can.

What is the difference between Copilot and the older "Analyse Data" button?

Analyse Data (formerly Ideas) is a rule-based feature that suggests pre-canned pivots and chart templates based on data shape — free with any Excel 365 licence. Copilot is a large language model: you ask free-form questions, iterate conversationally, and it generates bespoke responses. Use Analyse Data when you want a menu of standard summaries; use Copilot when you already have a specific question in mind.

Related tutorials

Sources