Can Copilot Create Pivot Tables in Excel? Yes, Here's How to Ask
The short answer: yes. Describe the PivotTable in the Copilot pane and Copilot builds a real, editable PivotTable linked to your source data. Microsoft's own example is a one-liner: Create a PivotTable with the sum of sales, with month in rows and category in columns.
The trick is to name 3 things: what goes in the rows, what goes in the columns, and what gets added up. Below: how to prepare the data, the prompts, charts from the pivot, and what to check before anyone sees the numbers.
Yes, and here's what you actually get
1A native PivotTable
Copilot updates your workbook with Excel's built-in features, so your content stays editable. Microsoft's list of what Copilot in Excel can do says it plainly: create and edit charts, PivotTables and shapes with editable links to source data. No pasted picture, no frozen numbers.
2A PivotTable as an answer
Ask Copilot a question about your data and it returns insights as charts, PivotTables, summaries, trends or outliers. Microsoft adds that the more specific the question, the more useful the answer.
3Changes by follow-up
Because Copilot can edit PivotTables too, you don't start over when the first version is wrong. You ask a follow-up in the same chat and it builds on the previous result.
My honest take: PivotTables are the Excel job I'd hand to Copilot before almost any other. You already know what you want to see. The dragging of fields is the boring part.
Before you ask: get the data ready
1Flat data, one header row
One row per record, one column per field (Month, Category, Sales). Microsoft no longer lists Excel Table formatting as a requirement, so you don't have to press Ctrl+T. It's still good practice, and it keeps the range tidy.
2No subtotal rows inside the data
This is my advice, not a Microsoft rule. A total row sitting in the middle of your source gets counted as one more record, and your numbers double. Delete it before you prompt.
3Name the columns in the prompt
Microsoft's tip: naming the columns you want analysed produces more accurate results. Write them exactly as they appear in the header row.
4The file settings
Save as .xlsx and keep Calculation Options on Automatic. Copilot editing only works when calculation is set to Automatic.
How to ask Copilot for a pivot table
Open Copilot with the icon in the lower-right corner of Excel, or press F6. It opens in edit mode, which changes the workbook directly (the 3 modes are explained in Copilot Agent Mode in Excel is now edit mode). Then say rows, columns, values, and where you want it.
Create a PivotTable with the sum of sales, with month in rows and category in columns.
Create a PivotTable on a new sheet with Region in rows, Quarter in columns and the sum of Revenue as values.
Add Product under Region in the rows and sort the regions by total Revenue, highest first.
Change the values to the average of Order Value and show them with no decimals.
Filter this PivotTable to 2026 only.
Ask a question, and Copilot may answer with a PivotTable
Sometimes you don't know the layout yet. You know the question. Ask it, name the columns, and let Copilot choose how to show the answer.
One thing to know: in chat mode Copilot analyses your data without changing the workbook. When you want the PivotTable to land in the file, ask in edit mode.
Which 3 categories grew fastest between Q1 and Q2? Use the Category, Quarter and Sales columns.
Show me the number of open tickets per Owner and per Priority, using the Owner, Priority and Status columns.
Charts from the pivot
Describe the chart you want and Copilot generates it. Like the PivotTable, it keeps editable links to the source data, so you can still change it by hand. Microsoft publishes no list of chart types Copilot does or doesn't support, so just ask for the one you want and see. Microsoft also doesn't show a chart-from-a-PivotTable example, so click the chart afterwards and check which data it reads.
Create a clustered column chart from this PivotTable, with a title and data labels, next to the table.
If you want more prompts for charts, formulas and clean-up, they're in Copilot prompts for Excel.
What to check before you share it
1The grand total
Compare it with a plain SUM of the source column. If they don't match, something in the source (a subtotal row, a number stored as text) is off. Takes a few seconds and saves an awkward meeting.
2Sum, count or average
Read the name of the value field in the PivotTable. It tells you what Copilot calculated. Make sure it's what you asked for.
3Everything that feeds a decision
Microsoft says Copilot can make mistakes or misinterpret information, and that you should avoid using it for decisions in sensitive areas such as finance, legal or medical topics. Review, edit and verify before you rely on it.
4Who else is in the file
When you save Copilot's changes, anyone with access sees them, including people co-authoring with you. Undo and version history get you back.
Questions about Copilot and pivot tables
Can Copilot create pivot tables in Excel?
Yes. Describe the PivotTable in the Copilot pane, naming the rows, columns and values, and Copilot creates a native PivotTable with editable links to your source data.
Can Copilot read pivot tables?
I can't confirm from Microsoft's pages that Copilot reads an existing PivotTable as such. Chat mode can analyse your workbook data and give insights without changing it. My advice: ask your question about the source data, name its columns, and use the pivot to check the answer.
Does my data need to be an Excel table for Copilot?
No. Microsoft no longer lists Excel Table formatting as a requirement. Clean, flat data with one header row still gives better results.
Can I edit a PivotTable that Copilot made?
Yes. Copilot builds it with Excel's own features, so you can change it by hand or ask Copilot for changes in a follow-up prompt.
Which licence do I need for Copilot pivot tables?
Copilot in Excel needs a Microsoft 365 Personal or Family subscription with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible business subscription. That list is for Copilot in Excel as a whole, and free Copilot Chat access inside Excel was removed for unlicensed users in some large organisations on 15 April 2026. If the button is missing, see Copilot not showing in Excel.
Want your finance team building pivots by asking?
I run live online Copilot workshops for groups of up to 20 people, from 1.5 to 4 hours. For Excel-heavy teams, the finance team session is the one I'd point you to.