How to Create a Pivot Table in Excel

You have a long list of transactions and want totals by category, month, or region without writing formulas. A pivot table groups and sums the rows for you.

Prepare the data

Make sure the list has exactly one header row, no blank rows or columns, and one kind of value per column. Click inside the list and press Ctrl + T (Command + T on a Mac) to turn it into a Table. A Table grows automatically when rows are added, so the pivot table picks up new data on refresh.

Insert the pivot table

Click any cell in the data, then go to Insert, PivotTable, From Table/Range. Check that the range or Table name is correct, choose New Worksheet, and click OK. On a Mac, Insert, PivotTable opens the same dialog. A blank pivot table appears on a new sheet with the PivotTable Fields pane on the right.

Drag fields into the areas

The pane lists every column header. Drag each one into an area at the bottom:

• Rows takes the category to group by, such as Category or Customer.
• Values takes the number to total, such as Amount. It defaults to Sum.
• Columns splits the totals sideways, for example by Month.
• Filters adds a dropdown above the table, for example by Region.

With Category in Rows and Amount in Values, the sheet shows one row per category with its total and a grand total at the bottom.

Change Sum to Count or Average

Click the arrow next to Sum of Amount in the Values area and choose Value Field Settings. Pick Count, Average, Max, or another summary and click OK. The Number Format button in the same dialog sets currency or decimal places for the whole column.

Refresh after the data changes

A pivot table does not update on its own. Click inside it and go to PivotTable Analyze, Refresh, or right-click and choose Refresh. If the source was a plain range rather than a Table and rows were added below it, use PivotTable Analyze, Change Data Source to extend the range.

If the pivot table shows one row for every value. A number field was dropped into Rows instead of Values. Drag it out of Rows and into Values.

If the source data has merged cells or blank headers. Excel refuses to build the pivot table or labels a column as Column1. Unmerge, give every column a header, and remove any subtotal rows inside the list before inserting.