Organize information

Design an Excel PivotTable for a business question

Map a decision question to rows, columns, values, filters, and validation totals

6 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Business questionSource table fieldsMetric and filter rules

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design an Excel PivotTable for this business question.

Question:
[question]

Source table grain, fields, definitions, types, and units:
[schema]

Metric, period, filter, and missing-value rules:
[rules]

Confirm that one source row represents the intended unit before assigning fields. Return the exact Rows, Columns, Values, Filters, and Slicers layout; aggregation for every value; any calculated fields or formulas that must remain outside the PivotTable; sorting and display settings; and a validation plan. Do not average percentages or double-count repeated entities. Show source-to-Pivot totals and a small manual cross-check.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Business question: Which product category and region combinations contributed most to Q2 net revenue, and which had high refund rates that need investigation?
Source table fields: Excel Table Sales; one row per order line. Fields OrderID, OrderDate, Region, ProductCategory, Quantity, GrossRevenueGBP, RefundAmountGBP. One order can have multiple lines.
Metric and filter rules: Q2 2026 only. NetRevenue=GrossRevenue−RefundAmount. Refund rate=RefundAmount/GrossRevenue. Show order-line count and distinct OrderID. Blank Region is Review, not a region. Rates must use summed amounts.

Example output

One source row is an order line, so ordinary Count of OrderID is a line count, not an order count. Add a source NetRevenue column and use the Data Model for Distinct Count of OrderID.

Layout: Rows = ProductCategory, then Region; Values = Sum of GrossRevenueGBP, Sum of RefundAmountGBP, Sum of NetRevenue, Sum of Quantity, Count of order lines, Distinct Count of OrderID; Filter = OrderDate grouped to Years and Quarters with 2026/Q2 selected; Slicer = Region and ProductCategory if interactive review is useful. Sort descending by Sum of NetRevenue. Show values as currency or whole counts, not mixed.

Calculate RefundRate outside the PivotTable from each row's summed refund divided by summed gross, or with a Data Model measure; never use Average of row refund rates. Route blank Region to a Review label in a helper column before refresh.

Validation: Q2 filtered source gross, refunds, net, quantity, and line count must equal Pivot grand totals; distinct OrderID must equal a UNIQUE/FILTER count. Manually choose one category-region pair, SUMIFS its gross/refund/net, and compare. A high refund rate is a diagnostic flag, not evidence that the category or region caused refunds.

Why this works

1
Row-grain validation prevents a well-formatted PivotTable from double-counting entities.
2
Explicit aggregations and reconciliation make the result reproducible rather than visual guesswork.