PivotTables in Excel: create one and understand it

In short: click inside your list, choose Insert > PivotTable and drag fields into the areas. What you group by goes into Rows or Columns, the numbers go into Values. Excel does the sums, counts or averages for you.

Try it now

The list holds orders with region, product and revenue. Show revenue per region by choosing in the field list which field goes where.

Task Create a PivotTable from the list that shows revenue per region.

	A	B	C
1	Region	Product	Revenue $
2	North	Paper	1200
3	South	Toner	800
4	North	Binders	450
5	West	Paper	990
6	South	Binders	300
7	West	Toner	1500
8	North	Paper	700
9	South	Toner	620

When a PivotTable is the right tool

Whenever your question sounds like "how much per ...": revenue per customer, orders per sales rep, average price per category. SUMIFS can answer the same question, but you would have to type every category yourself and build one formula for each. A PivotTable finds the categories on its own and can be rearranged in seconds. It never changes your source data.

Get the data ready first

Most PivotTable trouble starts in the source list. Check three things:

• One header row with a unique name above every column. Blank or duplicate headers become useless field names.
• No blank rows or columns inside the data and no subtotal rows. One row is one record.
• Format it as a table with Ctrl+T. The range then grows when you add rows at the bottom, so the PivotTable picks them up.

Creating the PivotTable

1. Click any cell in the list.
2. Choose Insert > PivotTable. In the "PivotTable from table or range" dialog, check the range and pick New Worksheet.
3. The PivotTable Fields pane opens on the right, listing every column header above four areas.
4. Drag fields into the areas or tick their boxes. Ticked text fields go to Rows and number fields to Values by default.

The four areas

Area	What it does	In the example
Filters	Limits the whole PivotTable to certain items, such as one year	empty
Columns	Each item becomes its own column across the top	empty (Quarter in the next exercise)
Rows	Each item becomes its own row down the side	Region
Values	The numbers being summarized	Sum of Revenue $

Use Rows and Columns together and you get a cross table, which is the next exercise.

One more: revenue by sales rep and quarter

This PivotTable groups twice: sales reps down the side and quarters across the top.

Task Create a PivotTable that shows revenue per sales rep, split by quarter: sales reps down the side, quarters across the top.

	A	B	C
1	Sales rep	Quarter	Revenue $
2	Lena	Q1	1200
3	Tom	Q1	850
4	Aylin	Q2	1400
5	Lena	Q2	900
6	Tom	Q2	1100
7	Aylin	Q1	760
8	Lena	Q1	430
9	Tom	Q2	690

Sum, Count or Average

Number fields are summed by default. To change that, click the field in the Values area and choose Value Field Settings. Under "Summarize value field by" you find Sum, Count, Average, Max, Min and more. The Number Format button in the same dialog adds currency or thousands separators. Count counts entries, so it also works on text fields like order numbers.

Refresh after the data changes

A PivotTable does not recalculate when the source changes. Right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh (Alt+F5). Refresh All with Ctrl+Alt+F5 updates every PivotTable in the workbook. If your list is a plain range and you added rows below it, also widen the range with PivotTable Analyze > Change Data Source.

Group dates by month

Drop a date field into Rows and Excel often groups it into years, quarters and months automatically. If it does not, right-click a date inside the PivotTable, choose Group and select Months and Years. Leave out Years and January 2025 and January 2026 end up in the same row. Grouping only works when the column holds real dates, not text that looks like dates.

Slicers and PivotCharts

A Slicer is a filter with big buttons, great for reports that other people click through. Add one with PivotTable Analyze > Insert Slicer and pick a field such as Region; one click on "North" filters the PivotTable. A PivotChart from PivotTable Analyze > PivotChart turns the PivotTable into a chart that follows every filter and slicer.

Common mistakes

Count instead of Sum

If Values shows "Count of Revenue" when you expected a sum, some numbers are probably stored as text, or the column has blank cells. Convert the text to numbers (green triangle, "Convert to Number") and switch to Sum in Value Field Settings.

Forgetting to refresh

The totals no longer match the list because nobody clicked Refresh after the last edit. Make Ctrl+Alt+F5 a habit before you send a report.

Source range too small

New rows are missing because the PivotTable still points at the old range. Format the list as a table with Ctrl+T and it will not happen again.

PivotTables in German Excel

English	German
Insert > PivotTable	Einfügen > PivotTable
Filters, Columns, Rows, Values	Filter, Spalten, Zeilen, Werte
Value Field Settings	Wertfeldeinstellungen
Sum, Count, Average	Summe, Anzahl, Mittelwert
Refresh / Slicer	Aktualisieren / Datenschnitt

Which Excel versions have PivotTables?

All current ones, including Excel 2016 and 2019, Excel for Mac and Excel for the web. In older versions the ribbon tab is called Analyze instead of PivotTable Analyze; the commands are the same.

FAQ