PivotTables have been Excel’s default answer to data analysis for decades, and I’ve used them enough to know the routine well. They work, but the setup has always felt disproportionate to the questions I’m actually asking. Copilot’s natural language queries skip that by allowing you to type a plain English question into Copilot and get an answer.
It’s not a function or a hidden feature; it’s just a conversation with your data. For most quick analysis tasks, this is how I prefer to use Copilot in Excel now, and PivotTables have become something I reach for far less often.
Related
My Excel formulas used to break all the time until I found this function
AGGREGATE was built to handle messy data from the start.
Copilot lets you ask questions instead of building tables
A plain English prompt replaces most of the clicking
The traditional PivotTable workflow for a sales spreadsheet goes something like this: you select the data range, click Insert, choose PivotTable, decide where to place it, then drag Region into Rows, Product Category into Columns, and Sales Amount into Values. If you want averages instead of sums, you need to right-click and change the aggregation. That’s a lot of clicking for a single question.
With Copilot, I type:
Which region had the highest total sales?
That’s it. Copilot reads the table — in my case, 32 rows of sales data spanning North, South, East, and West across four product categories — and returns the answer directly.
The advantage shows up when you need follow-up answers. With a PivotTable, a different question often means reconfiguring the layout or building a second one. In Copilot, I just type the next prompt. After getting the top region, I asked:
Show me a breakdown of sales by product category for the North region.
Copilot responded with a clean summary table listing Electronics, Clothing, Home & Garden, and Sports, each with its respective total.
I did the same for individual performance. Typing “Which salesperson had the highest total sales amount?” gave me a ranked answer across all eight salespeople in the spreadsheet, with David Chen and Emma Davis tied at the top and Sarah Johnson at the bottom. That kind of query would normally require grouping by Salesperson in a PivotTable and sorting the result manually.
This conversational flow makes it practical since you’re not planning your analysis in advance or deciding which fields to include — you just ask what you want to know, one question at a time. However, if a prompt doesn’t return exactly what you need, refining it takes seconds.
It handles charting and summarization in one step
You don’t need to build a chart separately after analyzing data
Making a chart in Excel the usual way requires selecting the data range, picking a chart type from the ribbon, then adjusting axis labels, titles, and colors until the chart communicates what you intended. If you built a PivotTable first, you’d then create a PivotChart on top of it — yet another layer of setup for what should be a straightforward visual.
Copilot collapses that into a single prompt. I typed the following prompt, and it generated a chart with Electronics, Clothing, Home & Garden, and Sports plotted side-by-side.
Create a bar chart comparing the total Sales Amount by Product Category.
The labels were already in place, and it didn’t require selecting ranges or fiddling with the Charts section under Insert. You can get more specific, too. Using the following prompt, Copilot pulled commission data for all eight people in my spreadsheet and plotted it without me pointing to specific cells. Copilot inferred the relevant columns on its own.
Create a bar chart showing total commissions earned by each salesperson.
This is where the time savings compound. One chart isn’t a big deal. But if you’re pulling together a quick report and need three or four visuals across different angles of the same dataset, the traditional method stacks up fast. With Copilot, each chart is just another prompt. Microsoft is also actively evolving how Copilot works in Excel, with Agent Mode offering deeper analysis options beyond basic chat prompts.
Copilot isn’t perfect, and PivotTables still have a place
Know when to type a prompt and when to drag a field
Copilot needs a Microsoft 365 Copilot license, and it sometimes stumbles when your data has merged cells, empty rows, or inconsistent headers. For live dashboards with slicers or calculated fields that you’ll revisit weekly, PivotTables are still the better fit. But for the quick questions, such as what sold best last quarter, who earned the most commission, and how the East compares to the West, typing a sentence beats configuring a table every time. Try both on the same dataset and see which one you reach for first.
