PivotTables
Summarise a list with a PivotTable, choose row, column and value fields, group dates and numbers, show values as shares, running totals and ranks, and refresh.
A PivotTable summarises a list: total sales for each region, the average order by month, a count of rows for each product across each quarter. You choose which columns go down the side, which go across the top, and which are summarised.
Create a PivotTable
- Select the list, including its header row. If you select a single cell, all the data on the sheet is used. The list can be a table.
- Choose Insert › Tables › PivotTable › PivotTable….
- The PivotTable is placed two columns to the right of the list, and the Pivot table fields dialog opens so you can arrange it.
To start, the PivotTable groups by the first text column and sums the first numeric column. Change the arrangement in the field list, then click Apply.
| Message | What to do |
|---|---|
| Select the data to summarise first — it needs a header row. | Select a list with a header row and at least one row of data. |
| Nothing in that selection is a number, so there is nothing to total. | Include a column of numbers, or use Recommended PivotTables to count rows instead. |
The result is written into the sheet’s cells, with bold headings and a bold grand total row, so it prints and exports like any other cells.
A PivotTable built on a table follows the table: rows you add to the table are included the next time the PivotTable refreshes.
Recommended PivotTables
- Select the list.
- Choose Insert › Tables › PivotTable › Recommended PivotTables….
- In Recommended pivot tables, look through the arrangements, best fit first. Each one explains itself — for example Amount totalled for each of the 4 Region values. — and shows the first rows it would produce.
- Click an arrangement to insert it, or click Cancel.
Suggestions are based on what each column holds, not on its heading. A column with a handful of different values is offered for grouping; a column of dates is offered grouped by month; averages and counts are offered alongside totals; and two label columns are offered crossed against each other.
The field list
To open the field list for an existing PivotTable:
- Click a cell inside the PivotTable.
- Choose Insert › Tables › PivotTable › Field list….
If the current cell isn’t in a PivotTable, the status bar says Select a pivot table first.
The Pivot table fields dialog shows the range being summarised (Summarising followed by the address) and three areas:
| Area | What it does |
|---|---|
| Rows | Each different value of these fields gets a row |
| Columns | Each different value of these fields gets a column |
| Values | These fields are summarised where the rows and columns meet |
Under ADD A FIELD, every column of the source is listed with three buttons: Rows, Cols and Values. Click one to add the field to that area. The same field can be added more than once. To remove a field from an area, click its × (Remove).
At the bottom:
| Option | What it does |
|---|---|
| Grand total row | Adds a total row at the bottom |
| Grand total column | Adds a total column at the right, when there are column fields |
Click Apply to rebuild the PivotTable, or Cancel to leave it as it was.
Summarise values
Each field in Values has two menus.
The first chooses how the values are summarised: Sum, Count, Average, Max, Min, Product, Count numbers, StdDev or Var. The column heading names it, for example Sum of Amount.
The second chooses what the numbers are shown as:
| Show values as | What each number becomes |
|---|---|
| No calculation | The summary itself |
| % of grand total | Its share of the grand total |
| % of row total | Its share of its row’s total |
| % of column total | Its share of its column’s total |
| Running total | The total so far, down the rows |
| Difference from previous | The change from the row above |
| % difference from previous | The percentage change from the row above |
| Rank, largest first | Its rank in the column, largest as 1 |
| Rank, smallest first | Its rank in the column, smallest as 1 |
The calculation is added to the column heading, such as Sum of Amount (% of grand total), so the numbers can’t be mistaken for amounts. Percentages are formatted as percentages. For running totals, differences and ranks the grand total row is left empty.
Group dates and numbers
Each field in Rows and Columns has a grouping menu, offered according to what the column holds:
| Column holds | Grouping choices |
|---|---|
| Dates — numbers with a date format | No grouping, Years, Quarters, Months, Days, Quarter of the year, Month of the year |
| Other numbers | No grouping, Number ranges |
| Text | None; the field says Nothing to group by |
Years, Quarters, Months and Days keep the year, so March 2025 and March 2026 are separate rows. Quarter of the year and Month of the year combine the years, for comparing seasons.
For Number ranges, the By box sets the width of each range, such as 100 for 0–99, 100–199 and so on. It starts at a round width that suits the spread of the values.
Refresh a PivotTable
A PivotTable doesn’t change on its own when its source data changes. To bring it up to date:
- Choose Insert › Tables › PivotTable › Refresh to refresh every PivotTable on the current sheet. The status bar says Refreshed followed by the number of pivot tables, or This sheet has no pivot tables.
- Or click the refresh button in Data › Get & transform (Refresh all — re-run every query and pivot).
Refreshing clears the cells the PivotTable wrote before and writes the new result, so a result that got smaller leaves nothing behind.
Delete a PivotTable
- Click a cell inside the PivotTable.
- Choose Insert › Tables › PivotTable › Delete.
The PivotTable and its cells are removed. The source data is untouched.
PivotCharts
Choose Insert › Charts › PivotChart while the current cell is inside a PivotTable to add a chart that follows it. See Charts and sparklines.
Something unclear or out of date on this page? Tell us.