IntermediateLesson 18 of 305 min read

PivotTables: summarising data in seconds

Summarise any list with a PivotTable: prepare data, drag fields, group dates, change calculations, refresh and troubleshoot.

In this lesson you will

  • Prepare data for a PivotTable
  • Build a table by dragging fields
  • Change calculations and add slicers
  • Refresh and troubleshoot

About 16 minutes

Ten thousand rows, one clear table

Gloria exports a year of orders from her shop, 9,000 rows. Her partner asks, ‘Show me sales by region and month.’ With SUMIFS she would need a formula for every region and month, set up by hand. A PivotTable answers the question by dragging four boxes. It is the feature that most surprises people who have used Excel for years without it, and it is the standard tool for summarising data.

What a PivotTable is

A PivotTable is a live summary of a list. You choose which field goes down the side (Rows), which goes across the top (Columns), what to add up (Values) and what to narrow down (Filters). Excel does the grouping and the sums. It never changes your original data, and you can rearrange it in seconds.

Prepare the data

  • One header row, with a unique name in every column.
  • One record per row, no blank rows or columns, no merged cells.
  • No subtotals inside the data.
  • Real dates and real numbers, not text (lesson 2).
  • Make the list an Excel Table with Ctrl+T so the PivotTable can grow with it.

Build one

  1. Click any cell in the data.
  2. Choose Insert, PivotTable, and accept New Worksheet.
  3. In the field list, drag Region to Rows.
  4. Drag Date to Columns. Excel groups dates into months automatically in modern versions; right-click a date and choose Group if it does not.
  5. Drag Revenue to Values. It becomes Sum of Revenue.
  6. Click inside the table and use PivotTable Analyze to refresh, format and filter.

What the table would show

The sandbox cannot create a real PivotTable, so the result below was produced another way: SUMIFS formulas that give the same answer a PivotTable would, calculated for real and checked against an independent Python grouping. It is the sum of revenue by region and month for the twelve sample sales:

Region Jan Feb Mar
North 680 0 660
South 300 300 240
East 0 580 540
West 0 360 500
Total 980 1240 1940

The grand total is 4160, and splitting it by product gives Pen 1640 and Notebook 2520, which add back to the total. In a real PivotTable you would see these same figures, with a Grand Total row and column added for you. If your PivotTable disagrees with a total you can check another way, find out why before you trust either.

Changing what you see

Action Result
Drag Product into Columns A region by product table
Click Sum of Revenue, Value Field Settings, Average Average sale instead of total
Value Field Settings, Show Values As, % of Grand Total Shares of the whole
Add Region to Filters A drop-down to show one region
Insert, Slicer Clickable buttons that filter

Refresh and layout

  • A PivotTable does not update when the data changes until you right-click it and choose Refresh (or set it to refresh on open).
  • Choose Design, Report Layout, Show in Tabular Form for a flat, readable look.
  • Double-click any number to see the rows behind it on a new sheet.
  • Add a PivotChart from PivotTable Analyze to chart it. It always matches the table.

Troubleshooting

Problem Likely cause
Values show Count instead of Sum Some cells in the column are text or blank
Dates are not grouped They are stored as text, or the column has blanks
New rows are missing Data was not a Table; refresh and change the source
Names appear twice Spelling or spacing differences in the data

Practice

Take a list of at least twenty sales rows with date, region, product and amount. Build a PivotTable with Region down the side and Month across the top. Add a slicer for Product. Then compare one cell with a SUMIFS formula to check the PivotTable is right.

Practice answer

The PivotTable cell and the matching SUMIFS should agree to the last digit. If they differ, the likely cause is hidden spaces or different spellings, which a PivotTable treats as separate items, or text amounts that were counted instead of added.

Common mistake

Forgetting to refresh. You change the data, look at the PivotTable and believe the old numbers. Make Refresh a habit, or tick Refresh Data when Opening the File.

Grouping and calculating inside a PivotTable

Right-click any date in the Rows or Columns area and choose Group to roll dates into months, quarters or years. Right-click numbers and choose Group to make bands such as 0-99, 100-199. For percentages, use Show Values As: ‘% of Column Total’ shows each month’s share within a region, and ‘% of Grand Total’ shows each cell’s share of everything. These are the figures managers ask for most, and they need no formulas. If you do need your own calculation, Calculated Field under PivotTable Analyze lets you define one, such as revenue divided by units.

When a PivotTable is the wrong tool

A PivotTable shines on long lists with repeated categories. It is awkward for a small fixed report with a special layout, where SUMIFS gives you full control, and for data that is not a clean list. Pick the tool that matches the job, and when in doubt, build both and compare one number.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Which area of a PivotTable holds the numbers that are added up?

  1. Filters
  2. Rows
  3. Columns
  4. Values

Revenue goes in Values.

Does a PivotTable update automatically when the data changes?

  1. Yes always
  2. No, you must refresh it
  3. Only on Fridays
  4. Only when printed

Right-click and choose Refresh.

Why prepare data with one header row and no blanks?

  1. Excel forbids blanks
  2. It sorts faster
  3. It adds colour
  4. PivotTables need a clean list

Messy data gives messy tables.

What does double-clicking a number in a PivotTable do?

  1. Shows the rows behind it
  2. Deletes it
  3. Sorts it
  4. Merges cells

It is a quick way to check a total.

Values show Count instead of Sum. What is a likely cause?

  1. The table is too small
  2. Dates are grouped
  3. The column contains text or blanks
  4. The file is saved

Clean the column and refresh.

Summary

  • PivotTables summarise lists by dragging fields into Rows, Columns, Values and Filters.
  • Clean data first: headers, no blanks, real dates and numbers.
  • Refresh after the data changes.
  • Check a PivotTable cell against a formula.

Next lesson

Next: working with several sheets, names and cleaning messy data.

Questions

Is a PivotTable better than SUMIFS?

For quick, flexible summaries, yes. SUMIFS is better when you need a fixed layout that feeds other formulas.

Can I use a PivotTable on data from another file?

Yes, using Data, Get Data to connect, but start with data in the same workbook while you learn.

Do PivotTables exist in Google Sheets?

Yes. Use Insert, Pivot table. The idea is the same, with a slightly different editor.

Progress is saved in this browser.