Excel Practice
Lessons Lesson 95

What a Pivot Table Does

About this lesson

A pivot table is a short summary of a long list: one row per group, with a total or a count for each. In Excel it is a built-in tool. Here you build the same summary with SUMIF and COUNTIF, so you can see exactly what the tool does.

By the end you can

  • say what a pivot table is: a short summary of a long list.
  • build one row of a pivot table with SUMIF.
  • check a summary against the list it came from.

Practises SUMIF COUNTIF SUM

The idea

Every pivot table asks the same two questions of a list: which groups are there, and what is the total for each. The Region column has two groups, North and South, so the summary has two rows. SUMIF fills each row. COUNTIF counts instead of adding.

Excel's pivot table tool builds this in a few clicks and refreshes it when the list changes. Knowing the formula underneath is what lets you check its numbers and explain them to somebody else.

The mistake to watch for

The summary is built and then the list keeps growing. A sale typed in row 11 is outside the ranges the formulas test, so no SUMIF sees it and the summary is quietly short. Make the ranges longer than the list, or check the summary total against the list total every time.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

A pivot table turns a long list into a short summary. Columns A to C are the list: one sale per row. D1:F4 is the summary: one row per region. In E2, add up the North sales with SUMIF: the Region column to test, D2 to match, the Sales column to add.

To begin, type it exactly:

=SUMIF(A2:A10,D2,C2:C10)

E2
Row A B C D E F G
1 Region Product Sales Region Sales Count
2 North Bikes 400 North
3 South Helmets 50 South
4 North Locks 30 Total
5 South Bikes 500
6 North Helmets 70
7 South Locks 40
8 North Bikes 300
9 South Helmets 100
10 South Locks 10

Every step

  1. 1

    A pivot table turns a long list into a short summary. Columns A to C are the list: one sale per row. D1:F4 is the summary: one row per region. In E2, add up the North sales with SUMIF: the Region column to test, D2 to match, the Sales column to add. To begin, type it exactly: =SUMIF(A2:A10,D2,C2:C10).

    Hint. SUMIF with the Region column, then D2, then the Sales column.

  2. 2

    In E3, the South total. Same shape: the Region column, D3, the Sales column.

    Hint. D3 instead of D2.

  3. 3

    The summary must add up to the list. Read =SUM(C2:C10) and say what it returns.

    Hint. Nine sales, one total.

  4. 4

    In E4, the total of the summary: add E2 and E3 with SUM.

    Hint. The two cells above.

  5. 5

    A pivot table can count as well as add. In F2, count the North sales with COUNTIF: the Region column, then D2.

    Hint. COUNTIF with the Region column and D2.

  6. 6

    In F3, write a formula using COUNTIF that counts the South sales.

    Hint. Same as F2, with D3.