Plan a Pivot Table That Answers a Specific Business Question

Get a pivot table plan mapping your fields to rows, columns, values and filters, with aggregation choices, data shape fixes and build steps.

You getData shape check, pivot layout and build steps

Prompt

Prompt
You are a data analyst who builds pivot tables in Excel and Google Sheets every day.

Your job: plan one pivot table that answers my business question, before I build it. First ask me these questions in one message, then wait for my answers:
1. What business question do you need answered?
2. Paste your column headers and 5-10 sample rows.
3. Excel or Google Sheets, and roughly how many rows?

Rules:
- Assign every useful field to exactly one slot (Rows, Columns, Values or Filters) and say why in one short line.
- For each value field, name the aggregation (Sum, Count, Average, Distinct Count, % Difference) and say why it fits the question better than the alternatives.
- Check the sample data for shape problems first: blank or merged headers, dates or numbers stored as text, total rows inside the data, blank key fields, months spread across separate columns.
- Give click-by-click build steps for my tool, 8 steps maximum.
- Point to exactly which cells in the finished pivot answer the question.

Output format:
1. Data shape check (table: Problem | Where | Fix)
2. Pivot layout (table: Slot | Field | Aggregation | Why)
3. Build steps (numbered)
4. How to read the answer
5. One follow-up pivot worth building

End with one line naming any assumption you made or data you still need.

Example output

At a glanceBuilding the Q1 vs Q2 revenue pivotExcel pivot plan for 4,800 order rows, by region and category
  1. Fix data shapeConvert text dates, delete total row, fill 37 blank regions
  2. Make table and insert pivotCtrl+T, then Insert > PivotTable > New Worksheet
  3. Set rows and columnsRegion then Category in Rows; Order Date grouped by Quarters
  4. Add Revenue twiceSum, plus % Difference From previous quarter
  5. Apply filtersStatus = Completed, Year = 2024
  6. Sort and readSmallest Q2 % Difference first, e.g. North, Outdoor Furniture, -22.4%

Example for: a regional sales manager in Excel, 4,800 order rows, asking which product category's revenue fell most from Q1 to Q2 in each region.

1. Data shape check

ProblemWhereFix
Some dates stored as textOrder Date, rows like "03/14/2024" left-alignedSelect column, Data > Text to Columns > Finish, then format as Date
Grand Total row inside dataLast row of sheetDelete it; the pivot makes its own totals
Blank region37 rows in RegionFill from the rep's assigned region or label "Unassigned"
Cancelled orders mixed inStatus columnKeep rows, filter them out in the pivot

2. Pivot layout

SlotFieldAggregationWhy
RowsRegion, then Category—Compares categories inside each region
ColumnsOrder Date grouped by Quarters—Puts Q1 and Q2 side by side
ValuesRevenueSumTotal money per quarter is the question
ValuesRevenue (second copy)% Difference From previous quarterShows the drop directly, no manual math
FiltersStatus = Completed—Cancelled orders would inflate Q1
FiltersYear = 2024—Stops quarters from different years merging

3. Build steps

  1. Click any cell in the data, press Ctrl+T to make it a table.
  2. Insert > PivotTable > New Worksheet > OK.
  3. Drag Region, then Category, to Rows.
  4. Drag Order Date to Columns; right-click a date > Group > select Quarters and Years.
  5. Drag Revenue to Values twice.
  6. On the second Revenue: Value Field Settings > Show Values As > % Difference From > Base field: Order Date (Quarters), Base item: (previous).
  7. Drag Status to Filters and select Completed only; set Years filter to 2024.
  8. Sort each region's categories by the Q2 % Difference column, smallest to largest.

4. How to read the answer

Under each region, the top category in the Q2 "% Difference" column is the biggest drop. Example: North, Outdoor Furniture, -22.4%.

5. Follow-up pivot

Same layout with Units as Sum and Unit Price as Average, to see whether the drop came from fewer sales or lower prices.

Assumption: Revenue already excludes refunds; if refunds are separate rows, filter them out too.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for sample rows first lets the model spot text dates, total rows and blanks before they break the pivot.
  • Forcing every field into one slot with a reason stops cluttered pivots that answer nothing.
  • Naming the exact cells that answer the question turns a layout into a usable result.

Use before building a pivot so you don't rebuild it three times. Have your question, column headers and a few sample rows ready. Works in ChatGPT, Claude or Gemini.

Common edits

  • Ask for a chart recommendation to pair with the pivot for a slide.
  • Request the same plan as a QUERY formula for Google Sheets instead of a pivot.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets