PivotdrillCreate a pivot table
Click any cell in your data, go to Insert, PivotTable, confirm the range, choose New Worksheet and click OK. An empty pivot appears with a field list on the right: drag a text field to Rows and a number field to Values to get your first summary.
Syntax
Insert > PivotTable > Select a table or range > New Worksheet > OK| Part | What it is |
|---|---|
Table/Range | The source block, including a single header row with no blank header cells |
New Worksheet | Puts the pivot on its own sheet (the usual choice) |
Existing Worksheet | Puts the pivot at a cell you pick on a sheet you already have |
Add this data to the Data Model | Optional; needed for Distinct Count and for joining several tables |
Examples
Source: Date, Region, Product, Units, Revenue (200 rows)
Rows: Region
Values: Sum of RevenueResult: One row per region (East, North, South, West) with total revenue next to each and a Grand Total at the bottom.
Dragging a number field to Values defaults to Sum.
Rows: Product
Values: Count of Order IDResult: Each product with the number of orders it appears in.
Dragging a text or ID field to Values defaults to Count.
Select data > Ctrl+T (make it a Table) > Insert > PivotTableResult: The pivot is built on a Table, so rows added later are picked up on Refresh without changing the source range.
Common mistakes
- The PivotTable button is grayed out or the pivot has fields named Column1: the header row has a blank or merged cell, so give every column a unique one-line header.
- Totals come out as counts instead of sums: the number column contains text or blanks, so clean it and refresh.
- The pivot ignores new rows: the source was a fixed range, so convert the data to a Table (Ctrl+T) or use Change Data Source.
- Subtotal rows from the source get added in again: remove manual total rows from the data before building the pivot.
Practice Create a pivot table
These drills check your answer on live data, free, no account needed.
- Revenue by regionBuild a pivot that shows total Revenue for each Region.
- How many orders per productShow how many orders there are for each Product.
Questions
What data do I need for a pivot table?
A flat list: one header row, one record per row, one type of value per column, no blank columns and no merged cells. Totals and subtotals should not be in the source.
Is there a keyboard shortcut to insert a pivot table?
Press Alt, N, V, T in Microsoft 365 (Alt, N, V in older versions). The legacy wizard still opens with Alt, D, P.
Where did the field list go?
Click inside the pivot and it comes back. If it does not, go to PivotTable Analyze, Show, Field List.
Related
- Rows, Columns, Values and FiltersThe four pivot table areas: Rows make the row labels, Columns spread categories across the top, Values hold the numbers, Filters restrict the whole table.
- Sum, Count and Average in a pivot tableChange a pivot table value from Count to Sum or Average in Value Field Settings, show total, count and average side by side, and fix a column that counts.
- Change data sourceUpdate the range a pivot table reads from with PivotTable Analyze, Change Data Source, or convert the source to an Excel Table so it grows by itself.
- Pivot table in Google SheetsCreate a pivot table in Google Sheets with Insert, Pivot table, use the editor to add Rows, Columns, Values and Filters, change SUM to COUNTA, group dates.