Every field in the Values area carries two independent dials, and mixing them up is a common source of "the pivot shows a wrong number."
The first dial is Summarize Values By, the aggregation. It decides how the source rows are combined. Sum adds them, Count tells you how many records there are, Average gives the mean per record, and Max / Min pick out the extremes. There's also Product, StdDev and Var for the statistically minded. To change it, right-click any value cell ▸ Summarize Values By ▸ pick one. For the full dialog, right-click ▸ Value Field Settings, or double-click the value field's header.
Why does a pivot show Count instead of Sum?
When you drop a field into Values, Excel picks the aggregation for you, and the rule it follows is exact and has consequences. If every cell in that source column is a number, it defaults to Sum. If the column contains even one blank or one stray piece of text, it defaults to Count. So a revenue column with a single empty cell arrives as "Count of Revenue". That's a tally of how many transactions there were, and it tells you nothing about how much money came in. Nothing turns red and nothing warns you, so unless you read the field header, you'll present a headcount as a rupee figure. This is the count-when-you-meant-sum trap, and the fix comes in two parts. Change Summarize Values By ▸ Sum. Then go back and clean the blank or text cell in the source so the default stops fighting you.
So which of the four everyday aggregations should you use? Sum for money and quantities, and it's the default you want 90% of the time. Count for "how many orders/customers/tickets". Average for "typical order size" or "mean days to pay". Max/Min for "largest single sale" or "earliest date." Pick the aggregation by asking what the number means. Don't just accept whatever Excel guessed.
Advertisement
What does Show Values As do?
The second dial is Show Values As, the display transform. It leaves the aggregation alone and re-expresses each cell relative to other cells, as a percentage, a cumulative running total, or a change. Right-click a value cell ▸ Show Values As ▸ choose. Here are the everyday options and the question each one answers:
- % of Grand Total is this cell's share of the entire pivot. Maharashtra's January revenue of ₹3,40,000 is 18.28% of the ₹18,60,000 grand total.
- % of Column Total is this cell's share within its own column. That same ₹3,40,000 is 54.84% of the January column (₹6,20,000). It's the workhorse for "what share of each month came from each state."
- % of Row Total is this cell's share within its own row. The ₹3,40,000 is 50.75% of Maharashtra's full-quarter ₹6,70,000, which answers "what share of each state's sales fell in each month."
- Running Total In is the cumulative sum down (or across) a chosen field. Maharashtra's running total across the months reads ₹3,40,000, then ₹5,20,000, then ₹6,70,000, the classic year-to-date column.
- % Difference From is the percentage change against a chosen base item, e.g. each month against the previous one, which gives you month-on-month growth.
Does Show Values As change the underlying number?
One rule saves a lot of confusion. Show Values As changes the display and never the underlying aggregate. The cell is still the sum, ₹3,40,000, and you're only choosing to show it as 54.84%. So the standard analyst move is to add the same field to Values twice, once as raw ₹ (Sum) and once as % of Column Total, and the report then shows the money and its share side by side. To reset, choose Show Values As ▸ No Calculation.