How do I… · Excel

How do I make a pivot table in Excel?

A pivot table summarises a list — totals by category, counts by month — without writing a formula. It takes about thirty seconds once the data is shaped right.

Steps

  1. Make sure your data is a plain rectangle: one header row, no blank rows, no merged cells, one thing per column.
  2. Click any cell inside it, then Insert → PivotTable. Accept the range it guesses and choose a new worksheet.
  3. Drag the field you want to group by into Rows — the category, the region, the month.
  4. Drag the number you want summarised into Values. Excel sums numbers and counts text.
  5. To change how it summarises, click the field in Values → Value Field Settings → Sum, Average, Count.
  6. Drag a second field into Columns to cross-tabulate, and into Filters to slice the whole table.

Worth knowing

If a number column is being counted instead of summed, there is text somewhere in it — often a stray "n/a". Refreshing after the source data changes is manual: right-click the table → Refresh. Turning the source into a Table first (Insert → Table) means new rows are picked up automatically.

Related

Stuck on the next one?

This page is written by the people who make Open Portal: press Ctrl+Shift+O, box any part of your screen, and ask about it. Excel included — it works in every app, because it reads pixels rather than plugging into anything.

Try it here, no install Download free

Faster than searching: box the part of your screen you cannot work out and ask about it directly — in Excel or anywhere else. Answers arrive where you drew the box.

Try it on a screenshot Download free