🔬 Research
Excel Data Analysis Prompt: Formulas, Pivots, and Findings
Paste your Excel headers, a few sample rows, and your question to get a cleanup plan, exact formulas, a PivotTable setup, a chart pick, and a findings template.
0Reviews
Prompt
Act as a data analyst who works in Microsoft Excel every day and teaches colleagues how to answer a business question from a raw export, step by step, without guessing at numbers that are not in the sample. Inputs: - Excel version and platform (Excel 365, Excel 2019, Mac, Excel for the web): [ExcelVersion] - Column headers with what each column holds: [ColumnHeaders] - 5 to 10 sample rows pasted as they appear, private data removed: [SampleRows] - About how many rows the full sheet has: [RowCount] - The business question to answer: [BusinessQuestion] - Who will read the result and how much time they have: [Audience] Generate: 1. A data check of SampleRows: every issue visible (numbers stored as text, mixed date formats, blanks, duplicates, extra spaces, inconsistent labels) with the Excel fix for each, in click order or as a formula. 2. Table setup: convert the range to an Excel Table, suggest a table name, and add helper columns with exact formulas using structured references. Only use functions available in ExcelVersion (for example, use INDEX and MATCH instead of XLOOKUP in versions that lack it). 3. Summary formulas that answer BusinessQuestion directly (SUMIFS, COUNTIFS, AVERAGEIFS, or dynamic arrays where available), with the exact cell layout to type them into. 4. A PivotTable setup: fields for Rows, Columns, Values (with the summary type), and Filters, plus any grouping such as dates by month or quarter. 5. One chart recommendation for the Audience and why it fits the question. 6. A findings template with blanks to fill from the calculated results. Do not state totals or trends, because only SampleRows are visible. 7. Three checks to confirm the formulas are right before sharing. Rules: - Explain each formula in one plain sentence. - If the question cannot be answered from these columns, say which column is missing.
Instructions
Replace every [bracket] with your real column headers and 5 to 10 sample rows, with names and private data removed. Works on ChatGPT, Claude, and Gemini. The model only sees the rows you paste, so run the formulas in Excel to get the real totals.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
ExcelVersion: Excel 365 on Windows ColumnHeaders: OrderID, OrderDate, Region, Product, Category, Units, UnitPrice, Discount, Channel (Web or Marketplace) SampleRows: 10231 | 07/03/2026 | West | Fiddle Leaf Fig 10in | Large Plants | 1 | $48.00 | 0.1 | Web 10232 | 2026-07-05 | west | Snake Plant 6in | Small Plants | 2 | $24.00 | | Marketplace 10233 | 07/05/2026 | East | Ceramic Pot 8in | Pots | 3 | 18 | 0 | Web 10233 | 07/05/2026 | East | Ceramic Pot 8in | Pots | 3 | 18 | 0 | Web 10240 | 04/28/2026 | South | Pothos 6in | Small Plants | 4 | $16.00 | 0.15 | Marketplace RowCount: about 6,200 rows, April to September 2026 BusinessQuestion: Which category and channel explain the drop in revenue from Q2 to Q3 2026? Audience: The shop owner, who wants a five minute read before Monday's planning call.
Example Output
1. DATA CHECK - Mixed dates: 07/03/2026 and 2026-07-05. Select OrderDate, Data > Text to Columns > Delimited > Next > Next > Date: MDY > Finish. Confirm with =ISNUMBER(B2) returning TRUE. - Prices as text with $: select UnitPrice, Find and Replace "$" with nothing, then set the column to Number. - Extra space and case: "west " vs "West". Helper column: =PROPER(TRIM([@Region])). - Blank Discount: treat as 0 in formulas with N(). - Duplicate row: OrderID 10233 appears twice with identical values. Check whether repeats are real (same order, two lines) before Data > Remove Duplicates on all columns. 2. TABLE SETUP Select the data, Ctrl+T, name it Sales. Helper columns: - Revenue: =[@Units]*[@UnitPrice]*(1-N([@Discount])) gives line revenue after discount. - Quarter: ="Q"&ROUNDUP(MONTH([@OrderDate])/3,0) gives Q2 or Q3. - Year: =YEAR([@OrderDate]) keeps quarters from mixing years. - RegionClean: =PROPER(TRIM([@Region])). 3. SUMMARY FORMULAS On a new sheet: A2 down, the categories with =UNIQUE(Sales[Category]). B1 "Q2", C1 "Q3", D1 "Change". B2: =SUMIFS(Sales[Revenue],Sales[Category],$A2,Sales[Quarter],B$1,Sales[Year],2026) sums revenue for that category and quarter. Copy to C2. D2: =C2-B2, then E2: =IF(B2=0,"",D2/B2) for percent change. Repeat the block with Channel in place of Category, then a third block with both: add Sales[Channel],"Web" as an extra criteria pair. 4. PIVOTTABLE Insert > PivotTable from Sales. Rows: Category. Columns: Quarter. Values: Sum of Revenue. Filters: Year = 2026, Channel. Then move Channel to Rows under Category to see which pairs fell most. 5. CHART Clustered column chart of Q2 vs Q3 revenue by category, one chart per channel. The owner can see the biggest gap in seconds. 6. FINDINGS TEMPLATE "Q3 revenue was [___] versus [___] in Q2, a change of [___]%. Most of the drop came from [category] sold through [channel], down [___]. [Second category] was [up/down] [___], so the drop is [concentrated/broad]." 7. CHECKS - Total of the Q2 and Q3 columns equals =SUMIFS(Sales[Revenue],Sales[Year],2026,Sales[Quarter],"Q2") plus the same for "Q3". - Recalculate one row by hand: 1 x 48 x 0.9 = 43.20 for order 10231. - Filter OrderDate for any cells that are not dates after the fix (should be none).