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.

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

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.

Where this comes up again

The SUM Function Spreadsheet Basics
Putting It Together Conditional Math
FILTER Dynamic Arrays
Project: Budget Against Actual Projects

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

The summary is already filled in: E2 to E4 are SUMIFs, one per region. In E5, the total of the summary: SUM over E2:E4.

To begin, type it exactly:

=SUM(E2:E4)

Row	A	B	C	D	E	F
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

The summary is already filled in: E2 to E4 are SUMIFs, one per region. In E5, the total of the summary: SUM over E2:E4. To begin, type it exactly: =SUM(E2:E4).

Hint. The three cells above.
2

A pivot table is only right if it matches its list. In C11, the total of the Sales column: SUM over C2:C10.

Hint. The nine sales.
3

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

Hint. Two equal totals.
4

In F2, the North share of the total: E2 divided by E5, with E5 locked as $E$5 so the formula can fill down.

Hint. E2 over the locked total.
5

In F3, the South share.

Hint. Same shape, one row down.
6

In F5, write a formula that adds the three shares. It must come to 1.

Hint. The three shares above.