Pivot Table Planner

List the columns in your data, mark each as text, number or date, and assign it to Rows, Columns, Values or Filters. The Pivot Table Planner returns the build steps and warns about layouts that will not work, such as summing a text field. Use it before you insert a PivotTable on a dataset you have not summarised before.

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

  1. 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.
  2. Enter each column name in the planner and choose its type: text, number or date.
  3. Assign each field a role: Row, Column, Value, Filter, or ignore.
  4. For each Value field, choose the summary: Sum, Count, Average, Max or Min.
  5. Read the warnings and adjust the layout until none apply.
  6. 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.

Related