AI is good at spreadsheet thinking (picking the right pivot, naming the outlier hypothesis, writing the formula) and bad at raw 10K-row CSVs pasted into chat (the token budget blows, types get guessed wrong, the model invents rows it never saw). The fix is to feed it a schema, a 5-row sample, and one aggregate question at a time, then verify every number it returns against the actual file. These 11 prompts cover the analytical workflow start to finish: clean columns, hunt outliers, propose pivots, write defensible formulas, and draft narrative summaries for your boss. Pair them with Excel formula explanation prompts when an existing VLOOKUP or LAMBDA needs decoding before you change it.
TL;DR
- Never paste the whole sheet. Give a column schema + 5 sample rows + one question. Pasting thousands of rows wastes tokens and triggers hallucinated rows.
- For real number-crunching, upload the file to a sandbox. ChatGPT’s Advanced Data Analysis runs Python (pandas) on your uploaded XLSX/CSV; CSV and spreadsheet uploads cap at roughly 50 MB as of June 2026. Free accounts get 3 uploads/day.
- For spreadsheet-native work, use an add-in. The Claude for Excel add-in (
.xlsx/.xlsm, 30 MB/file) opened to all Pro ($20/mo) subscribers on Jan 24, 2026, alongside Max, Team, and Enterprise. Gemini lives inside Google Sheets via the “Ask Gemini” sidebar and the=AI()function. - Always run prompt #10 (the self-audit) before you forward an AI-generated number to anyone.
Pick the right surface
| You want to… | Best surface (June 2026) | Why |
|---|---|---|
| Quick read on a small sheet | Chat with schema + sample | Fast, no upload, no setup |
| Crunch a real file (group-by, charts) | ChatGPT Advanced Data Analysis | Runs Python/pandas in a sandbox; up to ~50 MB |
| Edit a live workbook with formulas | Claude for Excel add-in | Reads multi-tab books, cites cells, preserves formula deps |
| Work entirely inside Google Sheets | Gemini sidebar + =AI() | Native, analyzes across multiple tables |
The prompts below work in any chat model (GPT-5.5, Claude Opus 4.7 / Sonnet 4.6, Gemini 3.1 Pro). When the task needs exact arithmetic over many rows, run them against a file-upload surface, not a plain chat box.
Best for
- Sales reports
- Survey data
- Product KPIs
- Financial sheets
1. Schema + ask
I have a CSV with these columns: {list with types}. 5 sample rows below. Goal: {question, e.g., "find drop-off in funnel"}. Give me the analytical approach + the formulas / queries to run.
Samples:
{paste 5 rows}2. Clean text columns
Column {col} contains messy text: {paste 20 samples}. Generate: regex / formula / Python snippet to normalize into {target format}. Show 5 before-after examples.3. Detect outliers
For column {col} (samples below), describe a method to detect outliers and the threshold I should use given my context: {what kind of data}. Give the formula.
{samples}4. Suggest pivot table
My data has columns: {list}. Goal: {analytical question}. Suggest 3 pivot table configurations (rows, cols, values, filters) that would answer this. Rank by usefulness.5. Explain a row / cell anomaly
Row {N} looks anomalous: {paste}. Given the rest of the sheet has these characteristics: {summary}, what’s likely going on? Suggest 3 hypotheses + how to check each.6. Write a formula for X
I want to compute {goal} in Excel / Google Sheets. Sample data structure: {paste}. Give me a formula. Explain what each part does so I can adapt it.7. Compare two periods
I have monthly data for {months}. Compare {metric} between {period A} and {period B}: absolute change, % change, biggest swings by category. Output as a 5-row table.8. Spreadsheet → narrative
Below is a pivot table summary (paste). Write a 150-word narrative I can send to my boss explaining what happened. Include 1 hypothesis for the biggest change.
{paste}9. VLOOKUP / XLOOKUP / INDEX-MATCH chooser
I need to join {sheet A} to {sheet B} on {key}. Sample structures: {paste}. Recommend the best lookup approach for my version of Excel / Sheets, the formula, and 2 edge cases (duplicate keys, missing values) and how to handle each.10. Sanity-audit on AI’s own answer
You just produced {paste AI's answer with numbers}. Audit it for: (a) units / scale, (b) rows that may have been dropped, (c) silent assumptions about null handling, (d) sample-bias from the 5 rows I showed. Output a checklist I can verify before sending the answer to my boss.11. Reproducible workbook structure
For {analysis goal} on data shaped {schema}, recommend a 3-sheet workbook structure: raw data, transforms, dashboard. Spec: what goes on each sheet, which formulas live where, what's a named range, what's a query. End with the 1 cell my boss should look at first.Common mistakes
- Pasting 5,000 rows into chat — token budget blows and the model invents rows
- No schema given — AI silently guesses column types and propagates the error
- Trusting AI’s numbers without spot-checking against the actual file
- Using AI’s formula without understanding what each part does — un-debuggable later
- Asking for “insights” with no specific question — output is generic and unhelpful
- Forgetting locale (comma vs semicolon as argument separator) when applying the formula
Related
- Chart takeaway prompts
- Survey analysis prompts
- Project Risk Analysis Prompts: 12 Templates Beyond a Risk Register
- Workflow Bottleneck Analysis Prompts: Find Where Work Stalls
- How to Use AI to Clean Up Messy Excel Columns: Names, Cases, Typos, Duplicates
- How to Use AI to Analyse Business Data: From Operational Noise to a ‘So What’
- How to Use AI for Competitor Analysis: A Structured Teardown from Public Web Data
- Excel Formula Explanation Prompts for VLOOKUP and LAMBDA