Describe your data, Excel version, PivotTable source type, and reporting goal. ChatGPT can then propose a field layout, grouping steps, calculated-field or measure approach, and validation checks that you can apply and verify in Excel.
I need to build a PivotTable report in Excel. EXCEL VERSION: [Microsoft 365 / Excel 2024 / 2021 / 2019 / 2016] PIVOTTABLE SOURCE TYPE: [worksheet table/range / Data Model / external OLAP source / not sure] MY DATA (paste 5–10 rows including headers): [Paste your header row and sample rows here] REPORTING GOAL: [e.g. "Revenue by Region and Product Category, with gross margin %, filtered to current year"] CALCULATED FIELDS NEEDED (if any): [e.g. "Gross Margin % = (Revenue − COGS) / Revenue" — leave blank if none] DATE GROUPING (if your data has a date column): [e.g. "Group dates by Month and Quarter" — or "No date grouping needed"] FILTER REQUIREMENTS: [e.g. "Filter by Year, current year selected by default" — or "No filters needed"] Rules: - Use only the column names from my actual data. Do not invent columns that are not present. - If a calculated field requires a column that does not exist, say so explicitly — do not assume. - Specify field placement exactly: Rows / Columns / Values / Filters. - For each Values field, specify: summarisation type (Sum/Count/Average) and number format (Currency, Percentage 1dp, etc.). - For date grouping: give exact steps (right-click → Group → level). - If I ask for a calculated metric, first decide whether I need: a classic PivotTable Calculated Field, a source helper column, or a Data Model/Power Pivot measure. - Do not give Calculated Field menu steps if my PivotTable source does not support classic calculated fields. - For ratios, explain whether the required business result is a ratio of aggregated totals or an aggregation of row-level ratios. Output: 1. Pivot layout specification (Rows / Columns / Values / Filters) 2. Step-by-step Excel setup instructions 3. Calculated field / helper-column / measure recommendation, with formula if applicable 4. Recommended sort order and number format for each Values field 5. Date-grouping and slicer instructions, if supported 6. At least one control calculation I can use to verify the PivotTable result
Paste your real column headers and sample rows — ChatGPT uses your actual column names in the instructions. Works in ChatGPT.
I need to add a calculated metric to an existing PivotTable in Excel. Excel version: [Microsoft 365 / Excel 2024 / 2021 / 2019 / 2016] PivotTable source: [worksheet table/range / Data Model / external OLAP / not sure] Underlying source fields available: [e.g. Revenue, COGS, Order ID] Current Values area: [e.g. Sum of Revenue, Sum of COGS, Count of Orders] The metric I want to add: [e.g. "Gross Margin % = (Revenue − COGS) / Revenue"] Please tell me first whether this PivotTable supports a classic Calculated Field. Then tell me: - Whether the business metric should be calculated from aggregated field totals or from row-level values - Whether a classic Calculated Field gives the correct subtotal/grand-total behavior - The exact formula and menu path if a classic Calculated Field is appropriate - Otherwise, whether I should use a source helper column or a Data Model/Power Pivot measure - One numeric test I can use to verify the Grand Total
Calculated field syntax is different from regular Excel formulas — ChatGPT writes it correctly for the dialog.
My pivot table has a problem. Please diagnose it. Problem: [describe what you see — e.g. "(blank) rows appearing", "can't group dates by month", "grand total is wrong", "calculated field shows wrong %"] Excel version: [version] PivotTable source: [worksheet table/range / Data Model / external source / not sure] My source data columns: [list them] The field causing the issue: [e.g. "Date column", "Category column"] Sample of the raw data for that column: [paste a few values] What I already tried: [refresh / regroup / changed source / nothing] Please: 1. List the plausible causes supported by the evidence I provided 2. Tell me what to check first before changing data 3. Give the exact fix only after stating the assumption it depends on 4. Tell me whether I must refresh the PivotTable or change its source 5. Give me a verification check so I can confirm the fix worked
Useful evidence to include: real date values vs text, blank cells, trailing spaces/aliases, subtotal rows, source type, and whether Refresh changes the result.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Rep | Category | Date | Revenue | COGS |
| 2 | North | A. Reid | Hardware | 15/01/2026 | £8,400 | £5,190 |
| 3 | South | B. Shaw | Software | 22/01/2026 | £12,200 | £3,480 |
| 4 | West | C. Fox | Services | 03/02/2026 | £6,750 | £2,550 |
| 5 | North | A. Reid | Software | 14/02/2026 | £9,800 | £2,940 |
| 6 | … 476 more transaction rows | |||||
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 3 | Region | Hardware | Software | Services | Grand Total |
| 4 | North | £312,400 | £198,750 | £84,200 | £595,350 |
| 5 | GM % | 38.2% | 71.4% | 62.1% | 52.7% |
| 6 | South | £274,100 | £143,600 | £61,800 | £479,500 |
| 7 | GM % | 41.0% | 68.9% | 58.4% | 51.6% |
| 8 | West | £198,600 | £112,400 | £47,300 | £358,300 |
Name: GM_Pct Formula: =(Revenue-COGS)/Revenue Format: Percentage, 1 decimal place Why it works for this metric: the calculated field uses the aggregated Revenue and COGS for each PivotTable cell, then divides. If the PivotTable uses the Data Model / OLAP source, use the appropriate measure approach instead. Slicer: Region or Quarter ✓
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 3 | Region | Revenue | COGS | Row GM% | Pivot GM% |
| 4 | North | £420,000 | £189,000 | 55.0% | 55.0% |
| 5 | South | £180,000 | £126,000 | 30.0% | 30.0% |
| 6 | Grand Total | £600,000 | £315,000 | — | 42.5% |
| A | B | C | D | |
|---|---|---|---|---|
| 3 | Region | Revenue | COGS | GM_Pct |
| 4 | North | £420,000 | £189,000 | 55.0% |
| 5 | South | £180,000 | £126,000 | 30.0% |
| 6 | Grand Total | £600,000 | £315,000 | 47.5% |
Gross profit = £600,000 − £315,000 = £285,000 GM % = £285,000 / £600,000 = 47.5% ✓ For this metric, do not use Average of a row-level GM% helper column as the Grand Total. Classic calculated fields operate on summed underlying fields, which is why =(Revenue-COGS)/Revenue produces 47.5% here. If the PivotTable is Data Model / OLAP-based, use a measure instead.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Revenue | Pivot row |
| 2 | North | £312,400 | North |
| 3 | North | £84,200 | North |
| 4 | north region | £28,100 | north region |
| 5 | (blank) | £14,600 | (blank) |
| 6 | South | £274,100 | South |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region raw | Revenue | Region clean | Pivot result |
| 2 | North | £312,400 | North | North £424,700 |
| 3 | North | £84,200 | North | |
| 4 | north region | £28,100 | North | |
| 5 | (blank) | £14,600 | Unknown | Unknown £14,600 |
| 6 | South | £274,100 | South | South £274,100 |
=IF(TRIM(A2)="","Unknown",
IF(OR(LOWER(TRIM(A2))="north",
LOWER(TRIM(A2))="north region"),
"North",
PROPER(TRIM(A2))))
North total:
£312,400 + £84,200 + £28,100 = £424,700 ✓
The explicit mapping avoids accidentally classifying unrelated labels such as "Northwest".
Blank stays Unknown until you have evidence for its region.
Refresh the PivotTable after filling the helper column.
Remove subtotal rows, unmerge header cells, and give every column a unique name. Check that date cells contain real Excel date serial values, not text that merely looks like a date. Number formatting alone does not convert text into a real date, and text dates can prevent reliable date grouping. Fix these first or the pivot will produce wrong results regardless of what ChatGPT specifies.
ChatGPT uses your actual column names in the layout instructions — "put Region in Rows, Category in Columns" — which is far easier to follow than generic instructions. A sample of 5–10 rows is enough; you don't need to paste the entire dataset.
"Revenue by region and category with gross margin, filtered to current year" is a useful goal, but also provide the actual headers, source type, and any business definition that is not obvious from the column names. ChatGPT can then propose field placement, summarisation, number formats, and sort order for you to verify.
Validate percentage metrics at both detail and Grand Total level. First decide what the business definition requires: a ratio of aggregated totals, an average of row-level ratios, or something else. A classic calculated field uses aggregated underlying fields; a source helper column is aggregated according to its Value Field Settings; a Data Model PivotTable may require a measure.
TRIM handles ordinary extra spaces, while PROPER only changes presentation case. For aliases such as "north region" → "North", use an explicit mapping rule or lookup table rather than a broad substring test that could also catch unrelated labels.Classic calculated fields are available for supported non-OLAP PivotTables. If the PivotTable uses the Data Model or an OLAP source, use the appropriate measure/calculation workflow instead of assuming the Calculated Field command is available.
For a classic supported PivotTable, use PivotTable Analyze → Fields, Items & Sets → Calculated Field. If that command is unavailable because of the source type, do not force this method.
Use source field names—not worksheet cell references—in the formula. After adding the metric, independently calculate one subtotal and the Grand Total from the underlying fields to confirm the PivotTable behavior matches the business definition.
Find which specific row, column, or filter is generating blanks, errors, or splitting identical categories in your pivot.
Copy the prompt and provide ChatGPT with a few raw data lines specifically for that problematic column.
Apply the suggested data cleaning formula or setting in your source sheet. Then right-click anywhere inside the pivot table and click Refresh to see the fix applied.
PivotTable Analyze → Fields, Items & Sets → Calculated Field dialog. For classic non-OLAP PivotTables, calculated fields operate on aggregated underlying fields. ChatGPT should first determine whether that behavior matches your metric and whether the PivotTable source supports classic calculated fields; Data Model/OLAP PivotTables may require a measure instead.