Most pivot table problems are not pivot table problems. They are layout problems in the sheet below. Get the source data into the right shape and the report takes about ninety seconds to build.
The shape your data has to be in
A pivot table needs a flat table: one header row, one record per row, one kind of value per column, and no blanks or merged cells in the middle. Anything that looks like a report already, with subtotals and spacing, will not pivot.
- One header row only, with a unique name in every column.
- No merged cells anywhere in the range.
- No blank rows or columns inside the data.
- Dates stored as dates, numbers as numbers, not as text.
Select the range and press Ctrl + T. A named table grows automatically as rows are added, so your pivot picks up new data on refresh instead of pointing at a fixed range that has gone stale.
The four wells, and what belongs in each
| Well | What goes here |
|---|---|
| Rows | The thing you are counting by. Region, product, month. This becomes the left-hand column. |
| Columns | A second, small breakdown. Keep it under about eight values or the report stops being readable. |
| Values | The number itself. Sum for money and quantities, Count for records, Average with care. |
| Filters | A slice applied to the whole report, such as one year or one channel. |
If your value lands as Count when you wanted Sum, the column contains text somewhere. That is Excel telling you about a data problem, not a setting to override.
Grouping dates and numbers
Right-click any date in the row area and choose Group to roll daily rows up into months, quarters or years without adding a helper column. The same works on numbers, which is how you build age bands or price brackets in a few seconds.
Show a share, not just a total
Drag the same field into Values twice. Leave the first as a sum, then on the second choose Show Values As and pick % of Grand Total. You now have the number and its share side by side, which is almost always the more useful report.
- % of Grand Total for share of the whole.
- % of Parent Row Total for share within a category.
- Running Total In for cumulative figures down a date column.
Before you send it
- Refresh, so the numbers match the current source.
- Rename the value headers. "Sum of revenue" is a machine's phrasing, not a report's.
- Sort largest first, unless the order is a date.
- Turn off the grand total row if it is meaningless, such as an average of averages.
Key takeaways
- Fix the layout first; most pivot faults are source-data faults.
- Convert the range to a Table so the pivot grows with the data.
- A value showing as Count means text is hiding in a numeric column.
- Add the same field twice to show a total and its share together.
Turn this into a career, not a bookmark.
Data Science & Analytics with AI takes you from spreadsheets to dashboards a business runs on.
Explore the course