How to Create PivotTables with Copilot in Excel

Copilot can build PivotTables from plain-English descriptions — you tell it what you want to summarize and how, and it handles the configuration. This works especially well for people who know what insight they need but don't want to drag fields around the PivotTable builder manually.

What you need first

Your data needs to be in a proper Excel Table format (select your data, then Insert > Table) with clear column headers. Copilot uses those headers to understand what fields to use — vague headers like "Column C" will produce vague PivotTables. The file must be saved to OneDrive or SharePoint with AutoSave on.

How to ask Copilot to build a PivotTable

Open the Copilot pane from the Home tab ribbon, then describe the PivotTable you want. The key is to specify what you want as rows, what you want as values, and optionally what you want as columns or filters. You don't need to use those exact words — natural descriptions work fine.

Basic PivotTable prompt examples

• Create a PivotTable showing total revenue by region
• Summarize sales by product category
• Show me total orders by salesperson
• Create a PivotTable with months as rows and total sales as values
• Summarize expenses by department and category

More specific prompts for two-dimensional summaries

• Create a PivotTable showing total revenue by region and product category
• Show sales by salesperson broken down by quarter
• Create a PivotTable with product as rows, region as columns, and revenue as values
• Summarize total sales by month and show year-over-year comparison

Refining a PivotTable with follow-up prompts

After Copilot creates an initial PivotTable, you can keep refining it in the same conversation:

• Add Quarter as a column slicer
• Filter this to show only the top 5 products
• Sort by revenue descending
• Add a slicer for Region
• Show averages instead of totals
• Add a grand total row

Adding conditional formatting to a PivotTable

Once the PivotTable exists, ask Copilot to highlight what matters:

• Apply conditional formatting to highlight the top 10% values in green
• Highlight any values below the average in red
• Add a data bar to the Revenue column

Where Copilot places the PivotTable

Copilot places PivotTables on a new worksheet by default. Once inserted, you can move it, resize it, or continue editing it manually using the standard PivotTable field list — Copilot's output is fully editable, not locked in any way.

When Copilot's PivotTable isn't quite right

If the PivotTable uses the wrong fields or is structured differently than you expected:

• Ask Copilot to adjust it: "Move Region to columns instead of rows"
• Or manually adjust it using the PivotTable Fields pane — drag fields between Rows, Columns, Values, and Filters as you normally would
• If the whole structure is wrong, it's often faster to describe what you want more specifically and ask Copilot to try again rather than editing field by field

Using Agent Mode for complex multi-pivot dashboards

If you need multiple PivotTables and charts assembled into a dashboard, Agent Mode handles this better than the standard Copilot pane. Enable Agent Mode by clicking the tools icon below the Copilot chat box, then describe the whole dashboard in one prompt:

Create a dashboard with three PivotTables: one showing total revenue by region, one showing top 10 products by sales, and one showing monthly trends. Add a chart for each.

Agent Mode executes the steps sequentially, builds the layout, and lets you review before finalizing.

The bottom line

Describing a PivotTable in plain English — "total revenue by region and product, with quarter as a slicer" — is genuinely faster than building it manually for most two-dimensional summaries. Where it takes a little practice is learning to be specific enough in your prompt that Copilot picks the right fields. When the first result isn't quite right, a follow-up refinement prompt is usually faster than starting over.