Excel PivotTables: Create, Refresh, Group and Limits

Build a PivotTable in 5 steps

Microsoft's page covers Excel for Microsoft 365, 2024, 2021, 2019 and 2016, with separate steps for Windows, web, Mac and iPad. The Windows steps are:

• Select the source cells. Use a single header row.
• Choose Insert > PivotTable.
• Pick an existing table or range as the source.
• Choose New Worksheet or Existing Worksheet and a location.
• Select OK, then tick fields in the PivotTable Fields pane.

Where Excel puts each field by default

When you tick a field, Excel decides the area. Non-numeric fields go to Rows, date and time hierarchies go to Columns and numeric fields go to Values. Drag a field to another area to move it.

In Values, the default calculation is SUM. If Excel reads the field as text, it uses COUNT instead, so a column of numbers stored as text shows counts, not totals. Keep each value field to 1 data type.

A worked example: 6 sales rows, Rows = Region

These are sample values. Columns are Region, Product and Sales. The rows: North, Pens, 120. North, Pens, 80. South, Pens, 50. South, Notebooks, 200. North, Notebooks, 40. South, Pens, 30.

Drag Region to Rows and Sales to Values. The PivotTable shows Sum of Sales: North 240, South 280, Grand Total 520. Drag Product to Columns and you get 4 cells: North Pens 200, North Notebooks 40, South Pens 80, South Notebooks 200.

Drag Sales into Values a second time and set it to Show Values As > % of Grand Total. North becomes 46.2% and South 53.8% (240 and 280 out of 520). Microsoft describes this two-views trick for the same field.

Prepare the source: 6 rules from Microsoft

The 6 rules below start with the layout of the data.

• Tabular layout with no blank rows or columns.
• One header row, with a unique, non-blank label for every column.
• Data organized in columns, not rows.
• No double header rows and no merged cells.
• The same data type in every column, for example no dates mixed with text.
• Format the source as an Excel table.

A PivotTable does not refresh itself

A PivotTable works on a snapshot called the cache. The source data is not altered, and the PivotTable stays at the 1 snapshot until you refresh it. For one PivotTable, right-click inside it and choose Refresh. For several, select a cell, open PivotTable Analyze, click the arrow under Refresh and choose Refresh All.

With the source formatted as an Excel table, added rows are included on refresh and new columns appear in the field list. Without a table, change the source manually or use a dynamic named range.

Group dates and numbers

Right-click a value and choose Group. In the Grouping box tick Starting at and Ending at, then choose a period under By. For a numeric field, enter the interval for each group instead, such as 10 for bands of ages. To group chosen items, hold Ctrl, select 2 or more values, right-click and choose Group. To undo, right-click any item in the group and choose Ungroup.

Excel groups date and time columns by default, with plus signs beside them. If you do not want that, File > Options > Data has the setting Disable automatic grouping of Date/Time columns in PivotTables.

Change what the numbers mean

The summary functions on Windows and Mac are Sum, Count, Average, Max, Min, Product, Count Nums, StDev, StDevp, Var and Varp. Count works like COUNTA, and Count Nums counts only numeric values. On Windows, open Value Field Settings from the arrow next to the field name.

Changing the calculation renames the field to something like Sum of Sales. Microsoft suggests not renaming fields until setup is finished, and then using Find & Replace (Ctrl+H) to remove "Sum of".

A calculated field such as = Sales * 15% adds a 15% commission. Formulas cannot use cell references or defined names, and are not available for OLAP sources.

Limits that matter

Excel allows 1,048,576 unique items per field and 1,048,576 rows per sheet. The number of PivotTable reports per sheet and of row or column fields is limited only by memory. Microsoft recommends the Data Model for very large datasets. In 32-bit Excel, a data model gets roughly 500 to 700 MB of a 2 GB address space; 64-bit Excel has no hard file size limit.

Undo is disabled for large refreshes above 300,000 rows by default, so save a copy before refreshing a big PivotTable.

Mistakes and platform notes

Microsoft's source rules ask for 1 header row, unique non-blank labels and no merged cells, so check those first when the field list looks wrong. Numbers stored as text turn Sum into Count. A PivotTable cannot take unique or duplicate conditional formatting in its Values area.

Recommended PivotTables work through Insert > Recommended PivotTable on the desktop; on the web they are for Microsoft 365 subscribers, and they use AI services under the connected experience. Excel on iPad needs version 2.82.205.0 or later. If a field list or dialog confuses you, Crade can read the screen and explain it.

To delete a PivotTable, select its whole range and press Delete. Other data around it is not affected.

Who it suits

PivotTables suit flat lists that you want to total by 1 or 2 categories, such as the 6 sales rows above. For data with nested or complex layout, Microsoft points to Power Query, for example to unpivot columns, before building the PivotTable.