PivotTables

Intermediate

Introduction

Suppose a list holds 20,000 sales, one per row, and your manager asks for sales by region and month. Formulas could do it, but a PivotTable does it in seconds, and lets you rearrange the summary just by dragging: by product instead of region, by quarter instead of month, as a count instead of a sum. PivotTables are the most powerful analysis tool most Excel users ever need.

How it works

Start with a clean list: one header row, no blank rows or columns, one kind of value per column. Ideally make it an Excel Table first, so the PivotTable picks up new rows. Click a cell in it and choose Insert, PivotTable, then a new or existing sheet.

The PivotTable Fields pane lists the columns of the source as fields, with four areas below:

  • Rows: the categories down the left side, such as Region.
  • Columns: categories across the top, such as Year.
  • Values: the numbers to summarise, such as Amount.
  • Filters: a field that filters the whole report, such as Channel.

Drag fields into the areas and the table rebuilds at once. A numeric field in Values is summed by default; a text field is counted. To change that, open Value Field Settings (right-click a value, or the field's arrow in the Values area): Summarise By offers Sum, Count, Average, Max, Min and more, and Show Values As offers % of Grand Total, % of Column Total, Running Total and Difference From. Set a number format there too.

Dates can be grouped: right-click a date in the rows and choose Group, then Months, Quarters and Years. Recent versions group dates automatically. Numbers can be grouped into bands, such as ages in groups of 10.

Slicers (PivotTable Analyze, Insert Slicer) are button panels for filtering, clearer than the filter drop-downs and able to control several PivotTables at once. Timelines do the same for dates.

A PivotChart (PivotTable Analyze, PivotChart) charts the PivotTable and follows its layout and filters.

A PivotTable is a snapshot: when the source data changes, choose Refresh (right-click, Refresh, or Alt+F5 on Windows), or Refresh All on the Data tab for every PivotTable and query in the workbook. If the source is a range rather than a Table, new rows outside the range are left out until you change the data source.

Example

A source Table named Sales has the columns Date, Region, Product and Amount, with hundreds of rows for 2026.

ABCD
1DateRegionProductAmount
205/01/2026NorthPens120
318/02/2026SouthBags900
4............

Line by line

  1. Click inside the Sales table and choose Insert, PivotTable, New Worksheet.
  2. Drag Region to Rows, Date to Columns and Amount to Values.
  3. If the dates are not grouped, right-click one and Group by Quarters.
  4. Drag Amount to Values a second time and set Show Values As to % of Grand Total.
  5. Insert a slicer for Product and click Pens.

Expected result

The PivotTable shows one row per region and one column per quarter, with summed amounts and grand totals, plus a second set of columns showing each region's share of the grand total as a percentage that adds up to 100%. Clicking Pens in the slicer recalculates everything for pens only. After new rows are added to the Sales table, Refresh includes them.

In the real world

Sales teams, accountants, HR and operations use PivotTables to answer ad hoc questions in meetings: which product grew fastest, which branch had the most returns, what share each channel brings. Many monthly reports are a PivotTable on a Table that is refreshed each month after new data is pasted in.

Common mistakes

  • Blank cells or text in a number column, which makes the field count instead of sum.
  • Forgetting to refresh, so the report shows last month's numbers.
  • Building from a fixed range, so new rows are not included; use a Table.
  • Typing over a PivotTable or putting formulas inside it, which breaks when it is refreshed.

Best practices

  • Prepare the source as a tidy Table with one header row and consistent types.
  • Rename value fields to clear names such as Total sales instead of Sum of Amount.
  • Use slicers for reports other people will use, and Refresh All before sharing.

Keyboard shortcuts

ActionWindowsMac
Insert a PivotTable by key tipsAlt, N, V—
Refresh the selected PivotTableAlt+F5—
Refresh all PivotTables and queriesCtrl+Alt+F5—
Group selected itemsAlt+Shift+Right ArrowCommand+Shift+K
Ungroup selected itemsAlt+Shift+Left ArrowCommand+Shift+J

Summary

A PivotTable summarises a list by dragging fields into Rows, Columns, Values and Filters. Value Field Settings choose how values are summarised and shown, dates and numbers can be grouped, slicers and timelines filter, and PivotCharts visualise the result. Build it from a Table and refresh it when the data changes.

Quick check

1. How is a numeric field summarised by default in Values?
2. Where would you put Region to list one row per region?
3. The source data changed. What must you do?
4. What is a slicer?

Key takeaways

  • A PivotTable summarises a list without formulas: rows and columns group, values aggregate.
  • Numbers are summed by default and text is counted; change it in Value Field Settings.
  • PivotTables do not update by themselves: refresh after the data changes.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.