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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 is a short summary of a long list.

In E2, type:

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

Region is the test, D2 the match, Sales the sum.

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 is a short summary of a long list. In E2, type: =SUMIF(A2:A10,D2,C2:C10). Region is the test, D2 the match, Sales the sum.

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

  2. 2

    In E3, add the South sales with SUMIF. Shape: =SUMIF(range,cell,range).

    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, add E2 and E3 with SUM.

    Hint. The two cells above.

  5. 5

    COUNTIF counts rows that match one test. In F2, count the North sales with COUNTIF. Shape: =COUNTIF(range,cell).

    Hint. COUNTIF with the Region column and D2.

  6. 6

    In F3, count the South sales with COUNTIF.

    Hint. Same as F2, with D3.