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

Excel Just Summed Your ID Numbers and Said Nothing

Article 6 of Excel for Business Analysts · The most common pivot trap, and the one-line test that catches it · Part of the Analyst Prep Kit

This article gives you the test that catches the most common beginner mistake in pivot tables, and the ten-second fix. The test is one line: would adding two of these together mean anything? If the answer is no, the column is a label, and it must never be summed.

The mistake happens without you doing anything wrong. You drag a column into a pivot's Values area, and Excel decides, on its own, how to summarize it. For anything numeric, it picks Sum. If that column was an ID, you now have the sum of a set of ID codes, printed with the same confidence as revenue.

This is not hypothetical. In the build behind this series, over 82,956 Steam games, this exact trap fired in step 3, survived a first glance, and was caught by a check cell. Then it turned up in three more fields on the same page.

What happened in the build

The pivot from article 5 was meant to answer: how many games in each segment, as a share of the total? AppID went into Values, because a count needs any always-filled column, and AppID is filled on every row.

Excel made it Sum of AppID. The pivot summed the ID codes of each group's games and reported each group's share of the total ID sum. The loved-and-found group came out as 0.45% of the total.

Stop on that number for a second. It is small, it has decimals, it is not round. It looks exactly like an answer. The correct figure, a count, was 0.71%: 590 games of 82,956. Both numbers are plausible. Nothing on the screen distinguished them. The check cell from article 2 did.

Before the next section, say out loud what Sum of AppID actually computed. Forcing the sentence is the point: "it added up the catalog numbers of 590 video games." Said aloud, the absurdity is audible. On screen, it never is.

Why Sum is the default

Excel's rule is mechanical. A column dropped into Values gets Sum if it is numeric, Count if it holds text or blanks. The rule is not wrong; for true quantities like price and revenue it is exactly what you want, and quantities are what Values was built for.

The rule misfires because it cannot see meaning. An ID is stored as a number for convenience, but it is a name written in digits. Excel has no way to know that, so the decision defaults to you, and it gets made silently on your behalf every time you do not read the corner of the pivot.

Why it survives review

Summing IDs does not error, and it does not produce an obviously broken number. It produces a large, confident, completely meaningless figure. A pivot cell reading 41,283,904,117 does not look like a bug. It looks like big data.

Worse, the failure compounds quietly. A share computed from a nonsense total, like the build's 0.45%, is small, tidy, and shaped like every honest percentage around it. Reviewers scan for numbers that look wrong. This trap produces numbers that look right, which is why the countermeasure is a check computed a second way, not a harder stare.

The test, and the column types it flags

Run the one-line test on any column before it goes into Values: would adding two of these together mean anything?

Year deserves its own sentence, because it is the one that fools experienced people. It is genuinely numeric, ordering matters, subtraction even means something. Addition still does not. Any pivot showing Sum of Year has this bug.

The ten-second fix

1. Read the corner. The Values box and the pivot's header name the operation: Sum of AppID, Count of AppID. Read it before reading any figure. This is the whole habit.
2. Click the field in Values > Value Field Settings.
3. Choose Count. OK. The header now says Count of AppID, and the sentence from article 5 is back to the one you meant to ask.

Then do what the build had to do: check the other fields on the page. This mistake travels in groups, because the same drag built every pivot on the sheet. One found means others likely.

The family this trap belongs to

This is the second of three articles about the same underlying thing: Excel deciding what your data is, and never mentioning it.

Article 4 is the decision at import: 08053 becomes the number 8053. This article is the decision at analysis: an ID column becomes a quantity. Article 15, later in the series, is the ugliest of the three: a file's alphabet guessed wrong, and 6% of a column's names corrupted.

The common defense across all three is the same: know which of your columns are labels, declare them where the tool lets you, and read what the tool decided where it does not ask. Labels are counted and grouped. Quantities are summed and averaged. That one distinction, applied at three doors, closes the family.

Run it on your own file

1. Open any workbook with a pivot in it. Yours or inherited.
2. Read every Values entry. Just the corners: Sum of what, Count of what, Average of what.
3. Run the test on each summed column. Would adding two together mean anything?
4. Fix any label that is being summed. Value Field Settings, Count. Note what the number was before and after, because someone may have already quoted the before.
5. Add the check. One COUNTIF beside the data for one group, per article 2, so the next silent default gets caught by arithmetic instead of luck.

A cheat sheet

Go read the corners of the last pivot you shipped. What does each Values box say it did, and is that the sentence you meant?