What the Pivot Table Planner does
A PivotTable summarises a table by grouping rows and calculating totals. The result depends on where each field is placed: Rows and Columns decide how the data is grouped, Values decides what is calculated, and Filters decide which records are included. The Pivot Table Planner lets you make those decisions on paper first.
It produces a short list of drag-and-drop steps for the PivotTable Fields pane, a text preview of the layout, and warnings. The warnings cover text fields set to sum, date fields set to sum, too many nested row fields, and layouts with more than one column field.
How to use it
- Make sure your data has one header row, no blank rows, and one record per row. Formatting it as an Excel Table with Ctrl+T helps.
- Enter each column name in the planner and choose its type: text, number or date.
- Assign each field a role: Row, Column, Value, Filter, or ignore.
- For each Value field, choose the summary: Sum, Count, Average, Max or Min.
- Read the warnings and adjust the layout until none apply.
- In Excel, select a cell in the data, choose Insert > PivotTable, then follow the generated steps.
Worked examples
Revenue by region
Input: Rows: Region. Values: Revenue (Sum).
Result: One row per region with the total revenue for each, plus a grand total.
Why: Region is text, so it groups the records. Revenue is a number, so Sum adds the amounts in each group.
Orders per month
Input: Rows: Order Date (date). Values: Order ID (Count).
Result: A count of orders for each month after the dates are grouped by Months.
Why: Count in a PivotTable counts non-empty entries, so it works for text IDs. Date fields are grouped by right-clicking a date in the PivotTable and choosing Group.
A layout the planner flags
Input: Values: Product (Sum), where Product is text.
Result: The planner warns that a text field cannot be summed and suggests Count.
Why: Sum only works on numbers. Placing a text field in Values with Sum would not produce a meaningful total.
What the warnings mean
- Text field with Sum or Average
- The field contains words, so only counting makes sense.
- Date field with Sum
- Adding dates gives a meaningless serial number. Use Count, Max for the latest date, or Min for the earliest.
- More than three Row fields
- Deep nesting makes the table hard to read. Move a field to Filters or use a slicer.
- No Row or Column field
- The PivotTable will show a single total cell.
Frequently asked questions
Why does my PivotTable show Count instead of Sum?
Excel defaults to Count when the column contains any text or blank cells. Clean the column so it contains only numbers, then change Value Field Settings to Sum.
Why don't new rows appear in the PivotTable?
PivotTables do not update automatically. Use PivotTable Analyze > Refresh. If the source is a plain range rather than an Excel Table, also check that the source range includes the new rows.
What is the difference between Count and Count Numbers?
Count counts every non-empty entry, including text. Count Numbers counts only numeric entries.