Lesson 9: Pivot Tables, Made Gentle

Summarize a long list in a few clicks, with no formulas

Listen to this lesson (3.8 MB, about 4 minutes)Download the audio · Short pieces: 1 2 3 4
Your list A B 1 Item Amount 2 Notebook $7.00 3 Markers $29.97 4 Notebook $10.50 5 Stapler $12.50 6 Markers $9.99 The pivot table A B 1 Row Labels Sum of Amount 2 Markers $39.96 3 Notebook $17.50 4 Stapler $12.50 5 Grand Total $69.96 PivotTable Fields Filters Columns RowsItem ValuesSum of Amount 1 2
A pivot table adds up each item for you. 1 Drag Item to Rows. 2 Drag Amount to Values.

A pivot table sounds scary, but it is just a fast summary. Say you have a long list of sales, one row per sale. You want to know: how much did each item bring in? How much each month? A pivot table answers in a few clicks, with no formulas.

Before you start

Your list needs headings in row 1 (like Date, Item, Customer, Amount), and no empty rows in the middle.

Make a pivot table

  1. Click any one cell in your list.
  2. Click the Insert tab.
  3. Click PivotTable. (In some versions, then click From Table/Range.)
  4. A small window opens. Excel already selected your list. Leave New Worksheet checked.
  5. Click OK.

A new sheet opens. On the right side is a panel called PivotTable Fields. It lists your headings.

Fill it in: drag the fields

The panel has four boxes at the bottom: Filters, Columns, Rows, and Values.

  1. Drag Item into the Rows box. Each item now shows once, on the left.
  2. Drag Amount into the Values box. Next to each item you now see its total.

That's it. You made a pivot table.

Tip: You can also just check the box next to a field. Words go to Rows, numbers go to Values.

Try other questions

Count instead of add

  1. In the Values box, click the arrow on Sum of Amount.
  2. Click Value Field Settings.
  3. Pick Count and click OK.

Now it shows how many sales, not how much money.

When your list changes

A pivot table does not update by itself. After you add or change rows in the list:

  1. Click inside the pivot table.
  2. Right-click and choose Refresh.
Tip: If your list is a table (Ctrl+T from Lesson 6), new rows are included when you refresh. That is another reason to use tables.
Tip: If the PivotTable Fields panel disappears, click inside the pivot table and it comes back.
Web: Excel on the web can make pivot tables too: Insert, then PivotTable.

Try it

Go to the sheet 9 Sales Log. Click one cell in it, then Insert, PivotTable, OK. Drag Item to Rows and Amount to Values. Which item brought in the most money? Then drag Month to Columns. Last, change Sum of Amount to Count, to see how many sales each item had. You can check your answers on the Answers sheet.

Download the practice workbook

Quiz

1. What is a pivot table for?

Show the answer

To quickly summarize a long list, like totals per item, with no formulas.

2. Where is the PivotTable button?

Show the answer

On the Insert tab.

3. To see the total for each item, where do you drag Item and Amount?

Show the answer

Item to Rows, Amount to Values.

4. You added new sales to the list. How do you update the pivot table?

Show the answer

Right-click inside it and click Refresh.

Excel Made Easy. Free course. Written for easy reading.