Making a PivotTable in Excel: how it works
A PivotTable turns a long list into a summary per group. Click in your data, choose Insert > PivotTable and drag the fields to Rows and Values.
Try the first exercises for freeIn the lesson PivotTables
What is a PivotTable?
A PivotTable summarises a long list into an overview per group: the revenue per region, the number of orders per customer, the sales per month. You don't write a single formula for it: you drag fields to the right place.
You can turn the overview around with a few clicks: regions in the rows and months in the columns, or vice versa. A PivotTable doesn't update itself: if your list changes, click Refresh.
When do you use a PivotTable?
- You have hundreds of sales rows and want to see the revenue per region.
- You want to know how many orders each customer placed: Customer in Rows, and counting in Values.
- You want the sales per month and per product in one table.
- You want to explore what's in your data quickly, before you build a fixed report with formulas such as SUMIF.
Making a PivotTable, step by step
This is how to go about it:
- Make sure there's one header row, with no empty columns and no empty rows in your list.
- Click in your data and choose Insert > PivotTable. Choose New Worksheet and click OK.
- In the pane, drag a field to Rows, such as Region.
- Drag a field with numbers to Values, such as Revenue. By default, Excel adds up the numbers.
- If you want something other than a sum, click the field under Values and choose Value Field Settings, such as Count or Average.
- If your data changes, right-click in the PivotTable and choose Refresh, or press Alt + F5.
Updating
A PivotTable doesn't update by itself. Right-click and choose Refresh, or press Alt + F5.
Sum, count or average
Excel adds up numbers by default. If you want something else, click the field under Values and choose Value Field Settings. There you can choose Count, Average, Max or Min, among others.
Grouping by month or year
If you drag a date field to Rows, Excel often groups it by itself. If it doesn't, right-click a date and choose Group, then select Months and Years.
Filtering with slicers
Under PivotTable Analyze > Insert Slicer you get buttons to filter by region, for example. That's handy when other people use your summary too.
Where it often goes wrong
- Empty column headings give an error message.
- Numbers stored as text are counted instead of added up.
Practise this in the lesson PivotTables. The first exercises are free.
Frequently asked questions
PivotTable or formulas?
A PivotTable is quicker for exploring data. Formulas are better for a fixed report that has to update straight away.
Read next
Sorting and filteringSUBTOTALMaking a chartWorking with tablesConditional formatting