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
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
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 like this
Summarize Pasted Spreadsheet Data into Key Findings and Trends
Paste rows from Excel or Google Sheets and get numbered findings tied to exact figures, a trend table, data quality flags and the gaps that limit conclusions.
Calculate Year-over-Year and Month-over-Month Growth in Excel Without Misleading Percentages
You get a growth table, exact Excel formulas that handle zero and negative baselines and partial months, plain-language takeaways, and a list of percentages that would mislead.
Write SQL Queries from a Plain-English Question and Your Table Schema
Paste your schema and ask a question in plain English. You get a working SQL query, a line-by-line explanation, and a row-count check query to verify the results.
Build an Excel Sales Forecast Comparing Moving Average and FORECAST.ETS
You get a tested Excel sales forecast with paste-ready formulas for two methods, a MAPE comparison on held-out months, and a clear pick of the better method.
Pick the Right Excel or Google Sheets Chart for the Point You Want to Make
Get one recommended chart tied to your message, exact build steps for Excel or Google Sheets, a takeaway title, and one chart to avoid with the reason.
Write a Weekly or Monthly KPI Report Narrative from Your Excel Numbers
Turns your pasted KPI numbers into a report narrative where each metric states the change, the likely driver and a next action, with every claim tied to a figure.