You need to summarize a large dataset in Excel, but building a Pivot Table manually takes time and requires knowing which fields to drag into Rows, Columns, and Values. Copilot in Excel can create a Pivot Table from your data with a single natural-language request. This article explains the prerequisites, walks through the exact steps to generate a Pivot Table using Copilot, and covers common mistakes to avoid.
Key Takeaways: Using Copilot to Create Pivot Tables in Excel
- Copilot button in Excel Home tab: Opens the Copilot pane where you can type requests in plain English.
- Data must be in an Excel table (Ctrl+T): Copilot can only analyze data formatted as a table with headers.
- Prompt format: “Create a Pivot Table showing [value] by [row] and [column]”: The most reliable way to get a correct Pivot Table on the first try.
How Copilot Generates Pivot Tables in Excel
Copilot in Excel uses large language models combined with the Excel calculation engine to interpret your natural-language request and build a Pivot Table based on your data. It does not simply suggest a layout; it generates the actual Pivot Table object in a new worksheet or in the existing sheet. Copilot can handle multiple row fields, column fields, value aggregations, and filters. The feature is available to users with a Copilot for Microsoft 365 license or a Copilot Pro subscription.
Before you start, your data must meet two conditions. First, the data must be formatted as an Excel table. Second, each column must have a unique header row with no blank cells. Copilot uses the column headers to understand the fields you can reference in your prompt.
Steps to Generate a Pivot Table Automatically with Copilot
Follow these steps to create a Pivot Table using Copilot in Excel for Windows or Mac. The same steps apply to Excel for the web with minor interface differences.
- Convert your data into an Excel table
Select any cell inside your dataset. Press Ctrl+T on your keyboard. In the Create Table dialog, confirm the range and check the box “My table has headers.” Click OK. Your data now has filter arrows in each header cell. - Open the Copilot pane
Go to the Home tab on the Excel ribbon. Click the Copilot button on the far right side of the ribbon. The Copilot pane opens on the right side of the Excel window. - Type your Pivot Table request
In the Copilot text box, type a clear prompt. For example: “Create a Pivot Table showing total sales by region and product category.” Copilot will analyze your table headers and respond with a proposed Pivot Table layout. It may show a preview or ask for clarification if the request is ambiguous. - Review and insert the Pivot Table
If Copilot displays a preview of the Pivot Table, review the row fields, column fields, and value field. Click the Insert button or the Add to new sheet button. Copilot creates the Pivot Table on a new worksheet by default. You can also choose to add it to the existing sheet. - Refine the Pivot Table with additional prompts
After the Pivot Table is inserted, you can ask Copilot to modify it. For example, type “Add a filter for year” or “Change the value aggregation to average.” Copilot updates the existing Pivot Table without you having to rebuild it.
Using More Complex Prompts
Copilot can handle multiple value fields and nested row labels. For instance, “Create a Pivot Table with sum of revenue and count of orders, grouped by region and then by salesperson.” Copilot will build the Pivot Table with two value fields and two row fields. If your prompt contains a field name that does not exist in your table, Copilot will suggest the closest match or ask you to rephrase.
Common Mistakes and Limitations When Using Copilot for Pivot Tables
Copilot says it cannot analyze the data
This error usually means your data is not formatted as an Excel table. Select the entire range and press Ctrl+T to create a table. Also ensure there are no merged cells or blank rows inside the dataset. Copilot requires a clean rectangular table with headers in the first row.
Copilot creates a Pivot Table with wrong aggregations
Copilot defaults to summing numeric fields and counting text fields. If you need a different aggregation, include it in your prompt. For example, “Create a Pivot Table showing average order value by month.” If the Pivot Table is already created, you can ask Copilot to change the aggregation: “Change the value field to average.”
Copilot inserts the Pivot Table but the layout is not what you expected
Copilot tries to guess the best layout based on your prompt. If the result has the wrong fields in rows versus columns, ask Copilot directly: “Move product to columns and region to rows.” You can also drag fields manually in the PivotTable Fields pane — Copilot will not override manual changes.
Copilot does not appear in the ribbon
The Copilot button is only visible if you have a qualifying license. Check that you are signed into Excel with a work or school account that has a Copilot for Microsoft 365 license. If you are using a personal Microsoft 365 account, you need a Copilot Pro subscription. After activation, restart Excel to refresh the ribbon.
Copilot-Generated Pivot Table vs Manually Created Pivot Table
| Item | Copilot-Generated Pivot Table | Manually Created Pivot Table |
|---|---|---|
| Setup time | Instant after one prompt | Requires dragging fields and configuring settings |
| Field selection | Based on natural-language interpretation | Fully controlled by the user |
| Aggregation control | Specified in prompt or changed via follow-up | Changed in Value Field Settings dialog |
| Filter and slicer support | Filters added via prompt; slicers added manually | Filters and slicers added manually |
| License requirement | Copilot for Microsoft 365 or Copilot Pro | Any Excel version with Pivot Table support |
You can now use Copilot in Excel to generate a Pivot Table from a plain English request. Start by converting your data into an Excel table using Ctrl+T, then open the Copilot pane from the Home tab. For the most accurate results, include the row field, column field, and aggregation type in your prompt. To save time on repetitive reports, try saving your prompts as a text file and reusing them on updated datasets.