← All Kits · Excel Kit · Article 0: the build order

A Pivot Table Is a Question, Not a Report

Article 5 of Excel for Business Analysts · One sentence, two halves, two drop zones · Part of the Analyst Prep Kit

This article gives you the reframe that makes pivot tables stop feeling like a machine with too many slots: a pivot is one question, asked of every row at once. Rows is what you group by. Values is what you count or measure. Write the question as a sentence and the two halves tell you where they go.

"How many games are in each segment?" Group by segment, count games. Drag Segment to Rows, drag any always-filled column to Values as a count. The pivot is the sentence, answered.

The short version. Every pivot question has the shape "for each X, how many, or how much, of Y?" X goes to Rows. Y goes to Values. If you cannot say the sentence, you are not ready to drag anything.

The example running through this article is the real one from the build behind this series: 82,956 Steam games, labelled into four groups in article 1, and counted by a pivot in step 3 of the build.

What's here

Why pivots feel confusing, and what they actually are
The sentence, and where its halves go
Build the real one: four groups, one count
The other two zones, briefly
Reports come out of it, questions go into it
Check it before you chart it
Run it on your own file
A cheat sheet

Why pivots feel confusing, and what they actually are

Before the explanation, answer honestly: when you open the PivotTable field list, what do you feel? For most beginners the answer is a small dread, four empty boxes and no idea which field belongs where.

The dread comes from approaching it as a layout tool: "what do I want the report to look like?" That question has no handle. There are thousands of possible layouts, and the boxes give no hint which one is yours.

Approach it as a question tool and the handles appear. A pivot does exactly one thing: it takes a question of the shape "for each X, how much Y?", asks it of every row, and prints the answers as a small table. The layout is not something you design. It is what the answer to your question happens to look like.

The sentence, and where its halves go

Say your question out loud before touching the field list. It must fit this shape:

For each  [group]  , how many / how much  [thing]  ?

The half	The drop zone	From the build
The group, the "for each X"	Rows	Segment: the four labels from article 1
The thing measured, the "how much Y"	Values	A count of games

Two rules keep the halves honest.

The Rows field must be a label. A column with a small number of repeating values: segment, genre, region, month. Grouping by a column where every value is different, like a game's name, produces 82,956 groups of one, which answers nothing. This is why labelling came four articles before pivoting. The label column is what makes the question askable.

The Values field must survive the arithmetic you are asking for. Counting works on anything filled in. Summing and averaging only mean something on true quantities: price, hours, revenue. Sum a label-wearing-numbers column like an ID and you get confident nonsense, which is exactly what happened in this build, and it is the whole of the next article.

Build the real one: four groups, one count

The question from the build: how many games are in each segment?

Click inside the Games table, then Insert > PivotTable. Because the data is a named Table, the range is right by construction, and new rows will be included on refresh.
Drag Segment to Rows. Four rows appear, one per label. Nothing is counted yet. You have only declared the "for each".
Drag AppID to Values, and make sure it says Count. Any column that is filled on every row works as a count. If the box says Sum of AppID, click it, Value Field Settings, Count. Why it defaults to Sum is the next article.

The answer comes back:

Unproven             78,064
Proven, not loved     4,127
Loved, found            590
Loved, hidden           175
Grand Total          82,956

Say out loud what the Grand Total row is doing for you. It is article 2's addition check, delivered without being asked: the groups sum to the row count of the file, so no game was dropped and none was counted twice.

The other two zones, briefly

The field list has four boxes, and beginners feel obliged to fill all four. You are not.

Columns is a second "for each", crossed with the first. "For each segment, for each price band, how many games?" makes a grid. Add it only when the question genuinely has two group-bys. A question with one group-by makes a taller, clearer table.

Filters restricts which rows are allowed to answer. "Only games released after 2015." It is the one zone that removes rows, so everything article 1 said about filters applies: fine for looking, dangerous for comparisons you are about to present.

Most working pivots are Rows plus Values and nothing else. An empty box is not an unfinished pivot. It is a question with the right number of parts.

Reports come out of it, questions go into it

The reframe earns its keep when the pivot is built and someone asks the next question, because there is always a next question. "Fine, but is it different for expensive games?" With the report mindset, that is a new report to design from scratch. With the question mindset, it is one drag: add PriceBand to Columns, read the new sentence back, done.

This is also the boundary between a report and an analysis, which has its own guide: a report states numbers, an analysis answers a question someone will act on. A pivot built from a sentence is already halfway to the second.

Picture the last pivot someone sent you. Try to say its sentence. If you cannot, that pivot was a layout, and every reader of it had to invent their own question.

Check it before you chart it

A pivot is a computed result, and computed results get checked, per article 2. The build's checks, both cheap:

The corner. Read what the Values box calls itself before reading any figure. Count of AppID is the question you asked. Sum of AppID is a different, meaningless question, answered confidently.

One group, one COUNTIF. =COUNTIF(Games[Segment],"Loved, hidden") in a spare cell should say 175, matching the pivot's row. One agreeing group plus an agreeing Grand Total is strong evidence the whole table is honest.

Run it on your own file

Write the sentence first. "For each ___, how many ___?" on paper or in a cell. If the first blank has no label column yet, build it, article 1's way.
Place the two halves. Group to Rows, measure to Values. Touch nothing else.
Read the corner. Count of, or Sum of? Make it say what your sentence says.
Check one row and the total. A COUNTIF for one group, and the Grand Total against the row count.
Only then consider Columns or Filters, and only if the question genuinely grew a second part.

Get the reps on pivots and the formulas that check them.

The Excel Kit drills the pivot workflow, COUNTIF and the rest of analyst Excel with worked examples and a mock exam. The sentence habit is yours from this page.

Open the Excel Kit →

A cheat sheet

You want	Do	Watch for
To start any pivot	Say the sentence: for each X, how much Y?	No sentence, no dragging
The group half	X goes to Rows	Must be a label column with repeating values
The measure half	Y goes to Values	Read the corner: Count of vs Sum of
A count of rows	Any always-filled column, set to Count	Sum of an ID column is nonsense with confidence
A second group-by	Columns zone	Only if the sentence really has two "for each" parts
To restrict the rows answering	Filters zone	It removes rows. Comparisons need every row present
To trust the result	One COUNTIF, plus Grand Total vs row count	Predict the values before you look
New data arrived	Right-click the pivot, Refresh	Works because the source is a named Table

The one habit to keep. Say the question as a sentence before you open the field list. Rows is the "for each". Values is the "how much". Everything else is optional.

The series: Excel for Business Analysts.

Every article comes out of one step of the same real build, in build order. Links go live as each one publishes.

Back to the feeling from the top, the four empty boxes. Say your next pivot's sentence now, while it is fresh: for each what, how much of what?