Excel PivotTables Explained: Build, Group, Refresh

A PivotTable takes a list of rows, such as a list of sales, and turns it into a summary: totals, counts or averages, broken down by whatever fields you drag into it, without writing a single formula. Insert, PivotTable builds one from a range or a Table, and you tick the fields you want, dragging them between the Rows, Columns, Values and Filters areas.

This guide builds a pivot on one small sales list, and works through the details that quietly go wrong: an average that is not what it looks like, a calculated field versus a calculated item, month grouping that mixes different years together, and a pivot that shows old numbers until you tell it to refresh. Every result was produced by building the PivotTable in Microsoft Excel.

The sample data

All examples use this list of eight sales, which becomes the pivot’s source range:

Building a basic PivotTable

Region was dragged to Rows, Product to Columns, and Amount to Values (which defaults to Sum for a numeric field). Excel builds the whole cross-tabulation, including subtotals and a grand total, automatically:

The grand total of an average is not the average of the parts

Change the Values field to Average instead of Sum, and the grand total is not the average of the three regional averages. It is the average of every individual sale, all eight of them, regardless of which region they belong to:

East has two sales (200 and 600, averaging 400), North has three sales averaging 533.33, and South has three sales averaging 450. The grand total is not the average of those three region averages (which would be 461.11); it is the average of all eight raw amounts, 468.75.

Show Values As: percentages and more

Right-click a value in the pivot, Show Values As, and choose from options such as % of Grand Total, % of Column Total, Running Total and Rank. Here every cell is shown as a percentage of the grand total instead of a raw amount:

Calculated fields

A calculated field (PivotTable Analyze, Fields, Items and Sets, Calculated Field) adds a new value built from the source fields, computed for every underlying row and then summarised like any other value. It can only use the original field names (Region, Product, Amount), not another summary already shown in the pivot. Here Bonus = Amount * 0.1 is added next to the existing Sum of Amount:

Calculated items

A calculated item adds a new row (or column) that combines existing items, such as North plus East. This is different from a calculated field, and it has a surprising side effect: the new item is included in the grand total, which doubles the count of anything it references:

The grand total now counts North and East twice: once on their own rows, and again inside NorthPlusEast. Calculated items are useful for a one-off comparison row, but check the grand total before you trust it.

Grouping dates: months can merge different years

Right-click a date field in the Rows area and choose Group to combine daily dates into months, quarters or years. If you group by Month only, without also selecting Years, Excel merges the same month across every year in the data into a single row:

To keep years separate, hold Ctrl and select both Months and Years in the Grouping dialog, which nests them instead of merging them.

A PivotTable is stale until you refresh it

Editing the source data does not update an existing PivotTable. It keeps showing the numbers from when it was last built or refreshed, until you right-click it and choose Refresh (or PivotTable Analyze, Refresh):

If several people work on a workbook, a pivot can quietly show last week’s numbers. Refresh after any change to the source, or set it to refresh automatically when the file opens (PivotTable Options, Data, Refresh data when opening the file).

GETPIVOTDATA

Clicking a cell just outside a PivotTable and then referring to a cell inside it often inserts a GETPIVOTDATA formula instead of a plain cell reference. It looks up a value by field names rather than position, which keeps working even if you rearrange the pivot, but it fails if the item does not exist:

If you would rather have an ordinary cell reference, type it instead of clicking, or turn the behaviour off in Excel Options, Formulas, “Use GetPivotData functions for PivotTable references”.

One pivot cache, several PivotTables

Two PivotTables built from the same Insert, PivotTable action (or that you deliberately point at the same cache) share their data behind the scenes, called a pivot cache. Refreshing one refreshes both, even though only one was told to refresh:

Two PivotTables created by two separate Insert, PivotTable actions, even from the same source range, get their own caches and do not refresh together, which can needlessly slow down a large workbook. Build extra PivotTables from an existing one (copy it, or choose “Use this workbook’s Data Model” consistently) when they should share a cache.

Common mistakes

Mistake 1: forgetting to refresh after changing the source

The most common pivot complaint (“my numbers are wrong”) is usually a pivot that has not been refreshed.

Mistake 2: reading Average of Amount as the average of the row totals

It is the average of every underlying row, which is not the same thing when groups have different numbers of rows.

Mistake 3: grouping dates by Month without Year

Data spanning more than one year quietly merges the same month together. Group by both Months and Years when the data crosses a year boundary.

Mistake 4: not noticing a calculated item in the grand total

A calculated item adds to, rather than replaces, the values it is built from, so totals that include it are inflated.

Cheat sheet

Try it yourself

Use the sample sales data from the top of this guide.

1. Build a PivotTable with Region in Rows and the Sum of Amount in Values. Which region has the highest total?

Show solution

South, with 1600. Rebuild it as shown in the first grid above and check the Grand Total row for each region.

2. You group a date field by Month only, and the data spans 2023 and 2024. What happens to November of each year?

Show solution

They are combined into a single “Nov” row, adding both years’ amounts together. Tick Years as well as Months in the Group dialog to keep them apart.

3. You change a number in the source data behind a PivotTable. Does the pivot update by itself?

Show solution

No. Right-click the pivot and choose Refresh, or use PivotTable Analyze, Refresh.

4. A PivotTable shows a calculated item that adds two regions together. Why might its grand total look too big?

Show solution

A calculated item is an extra row built from existing items, and it is included in the grand total alongside the items it is made from, so those amounts are effectively counted twice.

Frequently asked questions

How do I make a PivotTable in Excel?

Select your data (or a Table), then Insert, PivotTable. Drag fields into the Rows, Columns, Values and Filters areas of the Field List.

Why doesn’t my PivotTable update when I change the data?

PivotTables do not refresh automatically. Right-click the pivot and choose Refresh, or turn on Refresh data when opening the file in PivotTable Options.

Why is the average in my PivotTable not what I expected?

The grand total of an Average field is the average of every underlying row, not the average of the row or column subtotals shown above it.

What is the difference between a calculated field and a calculated item?

A calculated field creates a new value from other fields’ totals, such as Amount divided by Count. A calculated item creates a new row or column that combines existing items, such as two regions added together, and it is included in the grand total.

How do I show percentages instead of totals in a PivotTable?

Right-click a value cell, Show Values As, and choose an option such as % of Grand Total or % of Column Total.

Why did grouping by month combine two different years?

Grouping by Months only ignores the year. Select both Months and Years in the Group dialog (Ctrl-click both) when your data spans more than one year.

What is GETPIVOTDATA and why does it appear automatically?

It is a formula that reads a value from a PivotTable by field name rather than cell position. Excel inserts it automatically when you click into a pivot cell from another formula; type the reference instead if you want a plain cell address.

Do two PivotTables from the same data always update together?

Only if they share the same pivot cache, which normally happens when the second is built from the existing pivot’s data rather than a fresh Insert, PivotTable on the same range.