Build your first pivot table in Excel

A pivot table summarises a long list without any formulas. If you have ever copied figures into a calculator to total them up by month or by customer, this replaces that job entirely.

1. 1

   Tidy the source data

   You need one heading row and then nothing but data — no blank rows, no subtotals, no merged cells, and one thing per column. Dates in a date column, amounts in a number column.

2. 2

   Turn it into a Table first

   Click anywhere in the data and press Ctrl+T. This means that when you add new rows later, the pivot table will include them after a refresh.

   Tip: Give the table a sensible name on the Table Design tab, such as SalesData.

3. 3

   Insert the pivot table

   With the cursor still in the data, click Insert, PivotTable, and choose New Worksheet. You get an empty grid and a field list on the right.

4. 4

   Drag fields into the four boxes

   Drag Customer into Rows, Month into Columns and Amount into Values. That is a complete sales-by-customer-by-month report. Anything you drag into Filters becomes a dropdown at the top.

5. 5

   Fix "Count of Amount"

   If Excel counts instead of adding up, one of your amounts is text. Click the field in the Values box, choose Value Field Settings and pick Sum — then go back and check the source column for stray text.

6. 6

   Refresh when the data changes

   Pivot tables do not update on their own. Right-click the pivot and choose Refresh, or press Alt+F5.