Your first pivot table

Bisi wants revenue broken down by branch and by category at the same time. Seven categories, three branches.

With SUMIFS you'd write 21 formulas, plus 10 more for the totals. Each one is a chance to mistype a range, and if Bisi then asks for it by month instead, you write 21 more.

With a pivot table it takes about thirty seconds, the totals cannot disagree with the cells, and changing "category" to "month" is one drag.

Pivot tables are the single biggest jump in speed you will make in this course. They are also where careless analysts produce confident, beautiful, wrong tables, so this lesson teaches the check as well as the tool.

Words in this lesson

What a pivot table actually does

It takes a long list and answers: "for each combination of these fields, what is the total of that field?"

Your 938 rows go in. A 3 × 7 summary comes out. Nothing is lost: the pivot is a view of the data, not a change to it. Your clean table is untouched.

Four places to drop a field:

Build one: Google Sheets

According to Google's help centre:

1. Select the cells containing your source data. Each column needs a header — which your Module 2 table already has.
2. In the menu at the top, click Insert → Pivot table.
3. Click the pivot table sheet if it isn't already open.
4. In the side panel, next to Rows or Columns, click Add, then choose a field.
5. Next to Values, click Add, then choose the field you want calculated.

For Bisi's crosstab: Rows = category, Columns = branch, Values = amount_ngn.

About those suggestions

Google notes that you'll sometimes see recommended pivot tables based on the data you choose, under "Suggested", and that high confidence suggestions are automatically inserted when the pivot table is created.

Treat a suggestion as a starting point, never as an answer. It doesn't know what question you're asking. To turn them off, Google says: at the top click Tools → Suggestion controls, then turn off Enable Pivot table suggestions.

Build one: Excel

According to Microsoft's support page:

1. Select the cells you want to create a PivotTable from. Microsoft notes your data should be organised in columns with a single header row.
2. Select Insert → PivotTable.
3. Choose where it goes: New Worksheet, or Existing Worksheet and pick a location.
4. Select OK.
5. In the PivotTable Fields pane, tick the field names.

Microsoft describes the default placement: non-numeric fields go to Rows, date and time hierarchies to Columns, and numeric fields to Values. Drag a field to a different area to move it.

The result

Twenty-one numbers and ten totals, from four clicks.

To show those totals, Google's help says: under Rows or Columns, check Show totals.

The check that makes it trustworthy

Look at the grand total. It must be ₦11,026,300.

That is the same number you proved in lesson 2.5 against the till system's TOTAL JANUARY row, and reconciled again in Module 3. If your pivot's grand total matches, every cell in it is drawn from the dataset you verified.

If it doesn't match, stop. One of these is true:

That last one deserves its own section.

When a pivot shows 938 instead of ₦11,026,300

Microsoft's support page is explicit: PivotTable fields placed in the Values area are displayed as a SUM by default, but if Excel interprets your data as text, the data is displayed as a COUNT. Microsoft adds that this is why it matters not to mix data types in value fields.

So a pivot that stubbornly counts instead of totalling is not a pivot problem. It is telling you that Module 2's conversion missed something, and the fix is back in lesson 2.2: test with ISNUMBER, compare COUNT with COUNTA, convert properly.

A pivot table is an excellent data-quality detector. It surfaces in one glance what a column of formulas will hide for weeks.

Pivots do not update themselves

This catches everyone once.

Microsoft's page: if you add new data to your source, any PivotTables built on that source need to be refreshed. Right-click anywhere in the PivotTable range and select Refresh; for several at once, select a cell in any PivotTable, then PivotTable Analyze → the arrow under Refresh → Refresh All.

The failure mode is quiet and expensive: you add April's sales, glance at the dashboard, and the numbers look plausible because they're last month's. After changing source data, refresh, then re-check the grand total.

Reading the table you just built

Look at the crosstab again, because it says something.

Grains is ₦5,542,200 — 50.3% of everything Bisi sells. Half her business is rice, beans, garri and semolina. That concentration is the most important fact on the page: it means her results depend heavily on one category's supply and price, and a rice shortage is not a category problem, it's a business problem.

And it holds in every branch: Grains is the top category at Yaba, Ikeja and Surulere alike. That's worth knowing, because it means the branches differ in size, not in what they are.

Common mistakes

The short version

• A pivot table summarises a long list by Rows, Columns, Values and Filters, without formulas, and without changing your data.
• Google: Insert → Pivot table, then add fields in the side panel. Each column needs a header.
• Excel: Insert → PivotTable, then tick fields in the PivotTable Fields pane. Single header row.
• Always check the grand total: ₦11,026,300. It ties the pivot back to the figure you proved in Module 2.
• Microsoft: values default to SUM, but show as COUNT if the data is read as text — a free data-quality check.
• Pivots don't refresh themselves. Refresh after changing source data.
• Bisi: Grains is 50.3% of revenue, and the top category in all three branches.

Try it yourself

1. Build the branch × category pivot from your clean table. Confirm the grand total reads ₦11,026,300.
2. Compare three cells against the SUMIFS answers you built in lesson 3.4. They must agree exactly.
3. Swap category for payment_type in Rows. You should get Transfer ₦4,501,600, Cash ₦3,442,150, POS ₦3,082,550.
4. Drag branch from Columns to Rows and back. Note that the grand total never changes, only the arrangement.
5. Break it deliberately: in a copy of your data, retype one amount with a naira sign so it becomes text. Rebuild the pivot and record what happens to the total.
6. Add a row to your source data, then look at the pivot before and after refreshing. Write one sentence about what you saw.
7. Write two sentences for Bisi about what the Grains figure means for her business.

You'll know you've got it when building a two-way summary takes you under a minute, and you check the grand total before you read a single cell.