Session 4: Pivot Tables & Summaries

A pivot table is a grouped total that you can rebuild without touching the data. Ask it for revenue by state and it groups 1,200 rows into three; drag Product into Rows instead and it groups them into thirty. The source sheet is never modified, which is why a pivot table is safe in a way that a hundred manual SUMIF formulas are not.

That rebuilding is the whole value. Analysis is a conversation — a manager hears Lagos is 61 per cent of revenue and immediately asks and which products? A pivot table answers in two seconds by dragging one field. If your analysis is a fixed grid of formulas, every follow-up question is new work, which is why people stop asking.

The requirement is that the data must be ready, and this is where the last three sessions pay off. A pivot table needs tidy data: one header row, no blank rows or columns inside the data, no merged cells, no total rows, and real dates rather than text. Pivot on our raw export and LAGOS, Lagos and Lagos State appear as three states, and you will report three figures for one state with complete confidence.

Every pivot table has four areas and each has a specific job. Rows holds what you are grouping by — State, Product, SalesRep. Values holds what you are measuring — the sum of LineTotal, the count of orders. Columns splits each row into sub-columns — State in Rows with Month in Columns gives you a grid of state by month. Filters restricts the whole table — one sales rep, one quarter.

The mistake to avoid is dragging fields around until something looks right. Decide the question first, then place the fields: the thing you are grouping by goes in Rows, the thing you are measuring goes in Values. Which products sell most in Ogun? puts Product in Rows, LineTotal in Values, and Ogun in Filters.

Then a detail that catches everyone: drag a text field into Values and you get a Count, not a Sum. Excel chooses for you based on type. If you drag Product into Values expecting quantities, you will get the number of order lines containing that product — which is a different and usually useless number. Always check Value Field Settings to confirm you are summing what you think you are.

The first thing to know is that pivot tables do not update automatically. They read from a cached copy of the data, so when you add rows or change a value you must right-click and Refresh — or use Data, Refresh All. A report that quietly shows last month's numbers because nobody refreshed is a very common failure, and it is invisible unless you check the row count.

Grouping is what turns raw fields into useful ones. Select any date in the pivot and choose Group, and you can group by month, quarter or year — which is why the dates had to be real dates, since text dates cannot be grouped at all. You can also group numbers into bands: select the UnitPrice values and group into ranges of 5,000, and a list of thirty products becomes five price bands you can actually reason about.

Calculated fields add a new measure built from existing ones — Profit as LineTotal minus cost, or margin as a ratio. They are useful, with one trap worth memorising: a calculated field operates on the summed values, not row by row. So a margin percentage calculated field computes total profit divided by total revenue, which is a weighted average and usually what you want — but it will not give you the average of the individual row margins, and if you expected that, your number is wrong. Compute percentages in a column on the source data when you need the row-level version.

Value Field Settings offers far more than Sum, and the extras answer better questions. % of Grand Total turns Lagos is 4.2 million into Lagos is 61 per cent of revenue, which is what a manager can act on. % of Column Total shows the product mix within each state, revealing that Ogun buys mostly buckets while Lagos buys across the range.

Running Total In turns monthly figures into a cumulative line, which is how you see whether the year is tracking ahead or behind. Difference From compares each month with the previous one, which is what are we growing? actually means. Rank shows which products are first, second and third without sorting anything.

The habit to build is asking which denominator makes this number meaningful. A raw total answers how much; a percentage answers how important; a change answers which way. Most analysis that fails to persuade anyone is analysis that reported a total when the reader needed a comparison.

Three numbers describe a set of values and they answer different questions. The mean is the total divided by the count and is what most people mean by average. The median is the middle value when sorted. The mode is the most common. For symmetrical data they are close; for business data they usually are not.

Our order values are skewed, because a few large wholesale orders dwarf the many small retail ones. If the mean order value is 38,000 naira but the median is 9,500, then reporting the mean tells a manager that a typical order is 38,000 — and almost every order is far below that. The mean is being pulled up by a handful of large orders. The median describes the typical order; the mean describes the total. Both are true and they answer different questions.

So report the median whenever you say typical, and be suspicious of any mean on money data until you have looked at the distribution. A quick way to see the problem is the spread: compare minimum, maximum, median and mean. If the maximum is fifty times the median, the mean is not describing a typical anything, and a single chart of the distribution will show a manager more than any average could.