Lessons Lesson 97

Reading and Checking a Pivot Table

About this lesson

A pivot table is right when its total equals the total of the list it summarises. This lesson makes that check in one cell, then reads each group's share of the whole and checks that the shares add to one. Here you check it with formulas, not with Excel's PivotTable tool.

By the end you can

  • check a pivot table against the list it summarises.
  • work out each group's share of the total.
  • say what a difference of zero between two totals proves.

Practises SUM SUMIF

By Ali, Founder, Excel Lessons

The idea

Two checks catch nearly every pivot table mistake. The first is the total: the summary's grand total must equal the list's total, and their difference must be zero. The second is the shares: each group's part of the whole, and the shares must add to one.

A share is the group total divided by the grand total. The grand total is locked with dollar signs, so the one formula fills down the column. A share is a decimal here: 0.4 is 40%.

The mistake to watch for

Reading a share off a summary without checking the whole. If one row is missing from the summary, every share is too big, and each one still looks reasonable on its own. Add the shares: if they do not come to 1, the summary is short.

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

E2 to E4 hold the region totals.

In E5, type:

=SUM(E2:E4)

E5
Row A B C D E F G
1 Region Product Sales Region Sales Share
2 North Bikes 220 North 400
3 South Helmets 90 South 250
4 East Locks 130 East 350
5 North Helmets 60 Total
6 South Bikes 100
7 East Bikes 150
8 North Locks 120
9 South Locks 60
10 East Helmets 70

Every step

  1. 1

    E2 to E4 hold the region totals. In E5, type: =SUM(E2:E4).

    Hint. The three cells above.

  2. 2

    A pivot table is right when its total matches the list. In C11, total the Sales column with SUM.

    Hint. The nine sales.

  3. 3

    The check in one cell. Read =E5-C11 and say what it returns.

    Hint. Two equal totals.

  4. 4

    A share is a group's total divided by the grand total. In F2, divide E2 by $E$5. Remember: $E$5 stays locked when you fill down.

    Hint. E2 over the locked total.

  5. 5

    In F3, divide E3 by $E$5.

    Hint. Same shape, one row down.

  6. 6

    In F5, total the three shares in F2:F4. It must come to 1.

    Hint. The three shares above.