Pivot table rows, columns, values: get the layout from AI

A 120-row sales sheet, two questions: how much did each region sell per month, and which products sold less in June than in April? Which field goes in Rows, Columns or Values is a fair thing to ask an AI. If the source data is messy, though, a correct layout still gives wrong numbers.

The source

Date,Region,Product,Qty,Amount2026-05-12,South,Mug,11,992026-04-07,North,Tote bag,2,242026-05-17,North,Tote bag,4,482026-04-05,North,Mug,7,632026.04.09,South,Tumbler,9,162…

The data is made up for the test, with two problems planted on purpose: 8 dates are text like 2026.04.09, and South is spelled three ways: South, South (trailing space) and South Region.

The prompt

The model never sees all 120 rows. Paste the first few rows plus the values shown in the Region column's filter list, which is where spelling variants show up. Fix the answer order with numbers: Anthropic's prompting best practices recommend numbered steps when order or completeness matters. Here the cleanup list has to come before the layout.

I want to build a PivotTable from an Excel sales sheet. Answer in this order. <sheet>Sheet name: {Sales} · {120} data rows (plus header) · range {A1:E121}Header and first 5 rows:{header and 5 rows, comma-separated}Values shown in the filter list of the {Region} column: {East / North / South / South  / South Region / West}</sheet> <questions>Q1. {How much was sold (Amount) per region, per month?}Q2. {Which products sold a smaller Qty in June than in April?}</questions> 1. List what must be fixed in the source data before building the PivotTable. For each item, one line each for: what the problem is, how to find it in Excel, how to fix it.2. For each question, give the PivotTable layout as a table with these columns: Question | Filters | Rows | Columns | Values (summarize by) | Date grouping. Do not put the same field in Filters and in Rows or Columns at the same time.3. Give 2 formulas to check the PivotTable against the source: one for the grand total, one for the single Q1 cell "{South} · {April}" (SUMIFS).4. Write nothing beyond these three parts.

The result

Part of the second answer, from a run without the last sentence of step 2:

| # | Problem | How to find it | How to fix it ||---|---|---|---|| 1 | Dates in mixed formats: some are text like `2026.04.09`, so they can't be grouped by month. | Text dates are left-aligned. `=COUNT(A2:A121)` returns less than 120. Filter column A and look for entries outside the date hierarchy. | Select column A, Find & Replace `.` with `-`, then Data > Text to Columns > Finish (Date: YMD). Set the format to `yyyy-mm-dd`. Re-check that `COUNT` returns 120. || 2 | Region has a trailing space: `South ` is a separate item from `South`. | `=SUMPRODUCT(--(LEN(B2:B121)<>LEN(TRIM(B2:B121))))` returns more than 0. The filter list shows two "South" entries. | In a helper column use `=TRIM(B2)`, copy it, Paste Special > Values over column B, then delete the helper. Do this before fix 3. |…| Question | Filters | Rows | Columns | Values (summarize by) | Date grouping ||---|---|---|---|---|---|| Q1. Amount per region per month | none (optional: Years) | Region | Date | Amount (Sum) | Group Date by Years + Months, so the same month in different years stays separate || Q2. Products with lower Qty in June than April | Region (leave at All) | Product | Date | Qty (Sum) | Group Date by Years + Months. In the Months column filter, tick only April and June. Add a column beside the pivot with `=June cell − April cell`; a negative result means June sold less. |

Both English runs put the text dates and the region spellings at the top of the cleanup list, and both laid out Q1 the same way. For Q2 the first run used Show Values As > Difference From (base item April); the second added a helper column =June cell − April cell next to the pivot.

Check: when the pivot and SUMIFS are wrong together

The suggested SUMIFS check only works after cleanup. Run it before, and the SUMIFS counts only the exact spelling South and only real dates. The pivot's South × April cell is expected to do the same, so the two agree and the pivot looks right. That pivot half is an assumption: Excel was not opened, and with text dates in the column Excel may refuse the month grouping altogether instead of quietly leaving those rows out. The grand-total check passes too, since split rows still land in the grand total.

The same arithmetic in Python shows the gap. recheck.py computes what Excel's SUM, COUNT and SUMIFS would return before and after cleanup (usage: recheck.py sales.csv REGION MONTH).

$ python3 -I recheck.py sales.csv South 4rows                120SUM(amount)         10434COUNT(date)         112region spellings    ['South', 'South ', 'South Region']SUMIFS raw          669SUMIFS after fix    1047

South in April is 669 before cleanup and 1,047 after. In Excel, these two checks catch it:

Where following the answer gets stuck

Without the last sentence of step 2, both runs of the Korean prompt put Date (Months) in Filters and in Columns at once; the two English runs did not, so treat it as a risk rather than a certainty. Excel keeps a field in only one of Filters, Rows or Columns; dragging it to another area moves it (Microsoft Support). To keep only April and June, leave Filters empty and use the column label drop-down. With that sentence added, four reruns (two English, two Korean) none put the date in Filters. Only some of them spelled out how to keep April and June, and where they did it was in the Date column: one English run and one Korean run said to tick them there, another English run called it optional, and the second Korean run gave no selection step.