What is the difference between Copilot data analysis and the Analyse Data button in Excel?

The older Analyse Data feature (previously Ideas) uses pre-built statistical templates to suggest pivots and charts based on data shape — it is rule-based and free with any Excel 365 licence. Copilot is a large language model: you ask free-form questions in natural language, it can drill into specific slices, and it can iterate on follow-ups. Analyse Data is better when you want a quick menu of pre-canned summaries; Copilot is better when you already have a specific question and want a conversational back-and-forth.

Copilot + Excel

How to Use Microsoft Copilot for Data Analysis in Excel

Q: Can Copilot analyse data in Excel?

Yes, Copilot can analyse data directly in Excel. Ask questions like 'What are the top 5 products by revenue' or 'Show me the trend in monthly sales'. Copilot reads your table, performs the analysis, and presents results as text summaries, charts, or new calculated columns. It is particularly effective for quick insights without writing formulas. You need a Microsoft 365 Copilot licence and the data must live in an Excel Table in a OneDrive or SharePoint-saved workbook.

Q: How do I use Copilot for data analysis in Excel?

Format your data as an Excel Table (Ctrl+T), save to OneDrive, open the Copilot panel from the Home tab, and ask questions in natural language. Effective prompts include: 'What are the key trends in this data', 'Which category has the highest growth rate', 'Create a chart showing monthly revenue by region', or 'Highlight outliers in the sales column'. Copilot works best with clean, well-structured data — no merged cells, no blank rows, and one row per record.

Q: Why is Copilot greyed out in my Excel?

The three most common causes are: (1) the file is saved locally instead of OneDrive or SharePoint — Copilot requires cloud storage for document-level access; (2) the data is not formatted as an Excel Table, so Copilot cannot see a structured range; (3) your Microsoft 365 account does not have a Copilot licence attached. Check via File → Account and look for 'Copilot' in the subscription list. Also confirm your organisation admin has enabled Copilot for Excel.

Q: Is Copilot accurate for financial reporting?

Copilot is useful for exploratory analysis and initial pattern-spotting, but it is not a substitute for auditable formulas in financial reporting. Always verify key figures independently using SUMIFS, pivot tables, or Power Query. Copilot occasionally miscounts when columns have mixed data types (numbers stored as text), ignores rows hidden by filters unless told otherwise, and cannot explain its calculation methodology the way a formula's audit trail can. Use Copilot to discover insights, then rebuild them as formulas for production reporting.

By Sagnik Bhattacharya14 Mar 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.

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.

I teach Flutter and Excel with AI — explore my courses if you want structured learning.

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 Microsoft 365 Copilot licence (not free Copilot in the web browser — that cannot read your workbook), and your file must be saved to OneDrive or SharePoint for Copilot to access the data.

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.

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.

Setting Up for Analysis

Before asking Copilot to analyse your data:

Format your data as an Excel Table (Ctrl+T) — Copilot refuses to analyse a plain range.
Use descriptive column headers ("Revenue" not "Col3") — Copilot uses headers as prompt anchors, so ambiguous names ("Amount", "Value", "Col1") lead to incorrect assumptions.
Save the file to OneDrive or SharePoint — a local-drive file will show the Copilot button greyed out.
Ensure no merged cells, no blank rows inside the Table, and consistent data types per column — clean messy data before analysis.
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 — Click "Add to a new sheet" to keep the analysis
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

Start broad: "Summarise this sales data"
Identify patterns: "Show me the monthly trend"
Drill down: "Break this down by region"
Find anomalies: "Are there any unusual months?"
Forecast: "Based on this trend, what can we expect next quarter?"
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 can't access data from other workbooks or external sources

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 file is saved locally, not in OneDrive or SharePoint — Copilot requires cloud storage to access the document. Fix: File → Save As → OneDrive, then reopen. (b) You do not have a Microsoft 365 Copilot licence — free Copilot in the web browser does not count. Fix: File → Account, look for "Copilot" in the subscription list; if absent, contact your IT admin. (c) Autosave is off and the file is in a state the cloud copy has not synced. Fix: enable AutoSave (top-left toggle), confirm the sync icon in the title bar shows "Saved".

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, multiple Tables in a single sheet with ambiguous references, or data that is not inside a recognised Table object. Fix: unmerge all cells (Home → Merge & Center → Unmerge), remove blank header rows, ensure one Table per sheet, and confirm the data is inside an actual Excel Table (Ctrl+T). 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 data is in an Excel Table, saved to OneDrive or SharePoint, and you have a Microsoft 365 Copilot licence. 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, cloud-saved), open the Copilot pane from the Home tab, 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 file is saved locally instead of OneDrive/SharePoint, because the data is not formatted as an Excel Table, or because the account does not have a Copilot licence. Check File → Account for "Copilot" in the subscription list; save-as to OneDrive; convert the range to a Table with Ctrl+T.

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.