For quick, one-off questions about a spreadsheet, speaking in plain English to Copilot in Excel can be faster and less fiddly than building a PivotTable — and for many routine analysis tasks it gives the same answers without the clicks.
Background
PivotTables have been Excel’s go-to interactive summarization tool for decades. They let you group, aggregate, filter, slice, and drill into tabular data with minimal formula writing, and they remain indispensable for repeatable dashboards and complex, multi-table models. Microsoft documents PivotTables as the standard way to “analyze data in multiple tables” and to expose configurable fields for ongoing exploration.
In parallel, Microsoft has been rolling generative features into Excel under the Copilot umbrella: chat-style question answering, the in-sheet =COPILOT function (in some programs), and a more capable
Agent Mode that can produce or edit workbook artifacts (charts, formulas, even PivotTables) based on natural-language instructions. These additions move Excel toward conversational data exploration that can create results directly in the workbook. Microsoft’s documentation and release notes explain these capabilities and the licensing contours needed to access them.
At the same time, Excel’s formula toolkit is evolving: functions such as
GROUPBY and
PIVOTBY give users formula-driven aggregation alternatives to PivotTables, while Power Query and Power Pivot remain the right choices for repeatable ETL and relational modeling. The spreadsheet landscape now offers multiple ways to summarize data — interactive PivotTables, formula-based grouping, query-driven pipelines, and conversational Copilot prompts — and each has a distinct set of trade-offs.
What Copilot in Excel actually does (and how it compares to PivotTables)
Natural-language summarization and ad-hoc questions
Copilot lets you type plain-English questions about a selected table or the workbook’s data and returns summaries, rankings, and breakdowns without requiring you to insert a PivotTable and drag fields around. In real-world demos, simple prompts like “Which region had the highest total sales?” or “Show a breakdown of sales by product category for the North region” yield answers and compact result tables immediately. That conversational flow eliminates much of the setup overhead of a PivotTable when your questions are exploratory or transient.
- Strength: fast answers for one-off questions; ideal for iterative exploration.
- Weakness: not yet designed as a long-term, refreshable dashboard layer.
Charting and visualization in one step
Copilot can generate charts from the same prompt language — for example, “Create a bar chart comparing the total Sales Amount by Product Category” — and it often places labels and axes correctly without manual range selection. That collapses the separate steps (create summary, select range, insert chart, format) into a single conversational request. For short reports requiring several visuals, this significantly reduces friction.
Agent Mode: building workbook artifacts
Agent Mode (and related Copilot experiences like Analyst and Copilot chat) goes beyond chat replies. Agents can create native workbook artifacts — including PivotTables, charts, new columns with formulas, and Power Query flows — so you don’t lose reproducibility entirely when you ask for a richer result. Microsoft notes that Agent Mode can plan, build, and validate multi-step workbook solutions. Access to Agent Mode and certain agent capabilities requires specific Microsoft 365 subscriptions or a Copilot license.
Where PivotTables still win
PivotTables remain superior when you need:
- A stable, refreshable dashboard that will be revisited weekly.
- Slicers and interactive filtering built into the workbook UI.
- Complex calculated fields, multiple related tables (Power Pivot), or reproducible aggregation with explicit field placement.
- Maximum control over grouping, sorting, and layout for formal reports or audits.
Microsoft’s guidance still positions PivotTables as the authoritative tool for multi-table analysis and field-driven exploration. For anything that will be reused, audited, or refreshed frequently, PivotTables (or Power Pivot models) remain the safer, predictable choice.
Why Copilot feels faster — and why that matters
The cognitive overhead of traditional workflows
Creating a PivotTable is simple when you know the steps, but the process still demands upfront design: choose the data range, decide row/column/value fields, pick aggregation types, and format the output. For a single question (“what sold best last quarter?”), that feels like too many clicks.
Copilot reduces that overhead by letting you ask the question directly. You don’t need to decide the layout in advance or anticipate the next follow-up; you can iterate in chat. That lowers the bar for non-expert users and shortens the time to insight for power users, too. MakeUseOf’s hands‑on piece describes this shift: the author prefers typing sentences into Copilot for quick analysis and reaches for PivotTables less often.
Iteration without rework
When a follow-up question arrives — “show me the same breakdown but for the East region” — Copilot answers with a new summary or chart without requiring you to build a second PivotTable or to reconfigure the first. This fluidity makes exploratory analytics feel like a conversation rather than a mechanical construction project.
Practical strengths: what Copilot accelerates
- Rapid answers to direct questions (rankings, top/bottom performers, totals).
- Automatic chart generation from plain-language prompts, with labels and axes inferred.
- Iterative, follow-up queries that don’t require reconfiguring a report.
- Ability to generate native workbook objects through Agent Mode — so you can convert an ad hoc insight into a reproducible artifact if required.
Real-world limits and risks you must plan for
Copilot’s convenience comes with real caveats. When you adopt conversational AI for data analysis, you must treat the output differently than you treat a deterministic PivotTable or a rigorously tested formula.
Licensing and access barriers
Not every Microsoft 365 user has the full Copilot experience. Agent Mode and some advanced Copilot features require either a Copilot add-on or specific Microsoft 365 SKUs (Microsoft 365 Premium, Microsoft 365 Copilot commercial subscriptions, or plans that include AI crediting). That means organizations must budget for licenses if they want the most capable experiences. Microsoft’s documentation lists these license requirements for Agent Mode and other Copilot features.
Accuracy, hallucinations, and auditability
Generative models are probabilistic; they can misinterpret data or produce incorrect aggregations, especially in imperfect tables. Microsoft itself and independent reviewers have cautioned that AI features in Excel are not guaranteed accurate for tasks that require exact reproducibility, and Microsoft has flagged the need for caution in high‑stakes scenarios. Reports and hands-on tests have shown examples of inconsistent outputs or unexpected cells being referenced in Copilot-generated results. Treat Copilot outputs as
assistance that must be verified before you treat them as authoritative.
Data hygiene and structural fragility
Copilot’s parsing of your sheet depends on reasonably structured tables: consistent headers, no merged cells, no blank rows, and clear data types. Numerous troubleshooting reports and practical guides note that merged headers, irregular rows, or stray formatting can confuse Copilot and cause it to return incomplete or truncated answers. This fragility is a real operational cost: if your sheets are messy, Copilot can stumble; cleaning still matters.
Context truncation and agent memory
When agents convert workbook contents to text for model context, very large files may be truncated or summarized, which can lead to missing rows or partial analyses. Community reports and Microsoft guidance indicate that Copilot agents sometimes only process parts of a workbook or only the active sheet, so you must validate that the agent saw the data you intended. For heavy or enterprise-scale datasets, traditional BI tools (Power BI, SQL-based analysis) remain more reliable.
Governance, privacy, and compliance
Using an AI that sends content to cloud models raises governance questions. Microsoft describes data handling and enterprise controls for Copilot, but organizations must confirm that their configuration (tenant policies, DLP, and data residency settings) satisfies compliance needs before using Copilot for sensitive financial, legal, or regulated data. The messaging from Microsoft and third‑party analysts is consistent: verify governance controls before entrusting Copilot with protected information.
When to type a prompt and when to drag a field: a decision rubric
- If you need a quick, one-off answer or chart for an immediate conversation: use Copilot. It’s faster and more conversational.
- If you need a repeatable report, scheduled refresh, or slicer-driven dashboard: build a PivotTable (or Power Pivot model) and connect it to your data source. PivotTables provide explicit structure and refreshability.
- If you want formula-level reproducibility and prefer to keep everything on-sheet: consider PIVOTBY/GROUPBY functions. They give formula-based aggregation without the PivotTable UI, and are useful when you know exactly how to express the transformation.
- If you’re cleansing messy data or combining multiple data sources: use Power Query to build a repeatable ETL pipeline before summarizing. Power Query produces a stable source that Copilot and PivotTables both benefit from.
Practical checklist: preparing Excel so Copilot behaves
- Standardize headers: ensure a single header row with plain text column names. Copilot parses headers best when they are consistent.
- Remove merged cells and blank rows within the dataset; convert ranges to proper Excel Tables (Insert > Table). That reduces parsing errors.
- Reduce extraneous formatting and hidden rows; Copilot ingests the visible, structured table more reliably.
- If the analysis must be repeatable, ask Copilot to create native workbook objects via Agent Mode (PivotTable, chart, or Power Query steps) rather than only getting a chat reply. Native objects are inspectable and can be refreshed.
- Save the prompt history or copy Copilot-generated formulas into a version-controlled worksheet for auditability. Keep a record of prompts and outputs if the results influence decisions.
A short walkthrough (the MakeUseOf scenario, re-examined)
MakeUseOf’s writer described a short spreadsheet — 32 rows of sales data across four regions and several product categories — and contrasted two workflows:
- PivotTable route: Insert > PivotTable, drag Region into Rows, Product Category into Columns, Sales Amount into Values, possibly change aggregation to Average or Sum, then create a chart.
- Copilot route: Type a plain-English question in Copilot: “Which region had the highest total sales?” Copilot reads the table and returns the answer; follow up with “Show me a breakdown of sales by product category for the North region” and Copilot returns a clean summary table, or “Create a bar chart comparing the total Sales Amount by Product Category” and Copilot produces the visual without manually selecting ranges. This flow is faster for the quick, exploratory tasks in MakeUseOf’s example.
That example neatly illustrates the productivity difference for small, well-structured datasets. It also underscores the caveats above: the dataset was small and clean; Copilot succeeded because there was little ambiguity. In a messier workbook or a regulated reporting environment, the balance shifts back toward PivotTables and Power BI.
Governance, reproducibility, and the cost of convenience
Adopting Copilot widely inside an organization requires a governance plan:
- Inventory: decide which workbooks and teams may use Copilot; not all data belongs in model calls.
- Licensing: budget for Copilot licenses or Microsoft 365 SKUs that include Agent Mode if you want creation of reusable artifacts.
- DLP and auditing: configure data loss prevention and tenant-level policies so that Copilot operations comply with privacy and residency requirements.
- Verification regime: require human verification of automated outputs for anything that affects finance, compliance, or legal outcomes. Microsoft and independent reviewers emphasize that AI outputs must be checked for accuracy.
Without these guardrails, organizations risk adopting a tool that speeds work but leaves reproducibility and accountability gaps.
Where Copilot is likely to improve — and where I’d still keep PivotTables
Copilot will continue to improve its table parsing, multi-sheet context, and artifact-generation reliability. Microsoft’s release notes and product updates show active investment in agent workflows and better workbook integration, which means the tool will get steadily more dependable for production use.
But until those integration points are as trustworthy and auditable as a conventional PivotTable pipeline — and until enterprises are comfortable with the compliance model at scale — PivotTables will remain the best choice for repeatable reporting, multi-table analysis, and any workflow that demands explicit, inspectable aggregation logic. For analysts who value repeatable, refreshable outputs and precise control, PivotTables (and Power Pivot / Power Query) remain essential.
Recommendations for Excel users and teams
- Individual analysts and small teams: experiment with Copilot for quick, conversational queries and for rapidly building visuals during meetings. Use Copilot to accelerate exploratory analysis, then lock down the final process with a PivotTable or Power Query pipeline if it will be reused.
- Power users and data professionals: learn the new formula primitives (GROUPBY, PIVOTBY) alongside Copilot. Use Power Query for messy data and Power Pivot for multi-table models. These tools combine the best of both worlds: reproducibility and agility.
- IT and governance owners: define policy and licensing plans before broad Copilot rollout. Create guardrails for sensitive data, require verification for financial outputs, and document when agents produce artifacts that enter the audit trail.
Final analysis: complementary, not categorical replacement
Copilot represents a genuine productivity shift for many everyday spreadsheet tasks. For quick questions, one-off visuals, and iterative exploration, typing a plain-English prompt can be faster and more natural than building a PivotTable. The MakeUseOf hands-on experience captures that gap: Copilot’s conversational interface removes much of the routine clicking and reconfiguration that makes PivotTables feel heavyweight for ad hoc analysis.
However, Copilot is not a categorical replacement.
PivotTables, Power Query, Power Pivot, and the new PIVOTBY/GROUPBY functions still solve distinct problems that Copilot does not replace reliably today: reproducibility, scheduled refresh, auditable calculation logic, and robust handling of very large or relational datasets. When the situation demands precision, governance, or repeatability, the traditional toolkit remains essential. Use Copilot to accelerate insight and then convert the best outputs into durable workbook artifacts when stakes demand it.
The pragmatic path for most teams is hybrid: let Copilot speed exploration and storytelling; keep PivotTables and query-based pipelines for production reporting and compliance. That way you get the best of conversational speed without surrendering the accuracy and control that spreadsheets have always needed.
Source: MakeUseOf
I found a better way than PivotTables in Excel, and it's not a function