Getting started with pivot tables

Who this is for

It is for people who use spreadsheets at work and already know basic sums and filters, but have never built a pivot table or have always found them confusing.

The plan at a glance

1. 1.Make sure your data is a tidy table.
2. 2.Insert a pivot table from that table.
3. 3.Drag fields into Rows, Columns, Values and Filters.
4. 4.Read the result, and check one total by hand.

Start with a tidy table

A pivot table works on a list: one header row, one record per row, no blank rows or columns, and one kind of thing in each column. Dates should be real dates and amounts real numbers. If your numbers are stored as text, fix that first; our guide to cleaning up a messy spreadsheet shows how: clean up a messy spreadsheet.

The four areas, in plain words

• Rows: the categories listed down the left side, such as month.
• Columns: categories across the top, such as region. Optional.
• Values: the number to calculate for each cell, usually a sum of sales. It can also be a count or an average.
• Filters: a drop-down that limits the whole table, such as only one product.

Worked example: a sales summary by month

Here is a tiny, made-up sales table with eight rows. Imagine it as the first rows of a much longer list.

1. 1. Insert the pivot table

   Click any cell in the table, choose to insert a pivot table, and place it in a new sheet.

2. 2. Put Month in Rows

   The table now lists Jan, Feb and Mar down the side. Months typed as text may sort alphabetically; use real dates if the order looks wrong.

3. 3. Put Sales in Values

   Make sure it says Sum of Sales. You should see Jan 250, Feb 250, Mar 180.

4. 4. Put Region in Columns

   Now each month splits into North and South.

The result should look like this:

Now check it. January is 120 + 80 + 50 = 250. The grand total is 250 + 250 + 180 = 680. A plain sum of the original Sales column gives 120 + 80 + 50 + 100 + 60 + 90 + 70 + 110 = 680, which matches. If the two disagree, the pivot is missing rows, usually because the range stopped early or a blank row split the data.

To use a filter, drag Product into Filters and choose Widgets. The grand total becomes 120 + 100 + 60 + 110 = 390. Gadgets alone is 50 + 80 + 90 + 70 = 290, and 390 + 290 = 680.

Reading a pivot table for a decision

A summary only helps if you ask it a question. Here you might ask which month was weakest. March at 180 is clearly lower than January and February at 250 each. Then ask why: the North region fell from 170 to 100 to 70, while the South rose from 80 to 150 to 110.

The table does not tell you the cause. It tells you where to look. Maybe fewer North orders came in, or a product was out of stock. Take the pointer from the pivot and go back to the detailed rows or to the people who know.

If something goes wrong

• Sales show as a count, not a sum. The column probably holds text. Convert to numbers and refresh.
• New rows do not appear. Pivot tables do not always update on their own. Use the refresh command, and check the source range includes the new rows.
• Months are in the wrong order. Use real dates, or sort the field manually.
• The total is off. Compare with a plain sum, as above, and look for blank rows or rows with typos in the category name, such as 'North ' with a trailing space.

Keep it going

Try swapping Rows and Columns, changing Sum to Average, or putting Product in Rows below Month. Each change teaches you a pattern you can reuse. If a pivot table on your screen confuses you, DuckyHelper can look at it and explain what each field is doing out loud while you keep working.