How to use AI for spreadsheets and analysis

AI is useful for spreadsheets when you treat it as an analyst assistant — someone who can clean data, write formulas, suggest segments, and point at things worth investigating, but who must show its work. The dangerous failure mode is asking a language model to do arithmetic and accepting the numbers it generates. Models don't calculate; they predict text that looks like calculation. That's fine for suggesting what to compute. It's not fine for doing the computation.

This guide is for the spreadsheets you handle every week: cleaning a CSV, writing a SUMIFS formula, looking at campaign results, checking pipeline health, finding outliers in a 200-row dataset.

A 4-step working model

The same loop works whether you're cleaning customer exports, analyzing a funnel, or trying to make sense of a Stripe report.

1. Clean the sheet

This is where AI helps most reliably and where most people skip it. Before you analyze anything, you want consistent columns, no duplicates, no broken formats.

Useful prompts:

Here are 10 rows of my spreadsheet. List every column. For each, tell me:
- the data type it appears to be
- any inconsistencies (mixed formats, blanks, weird values)
- the cleanup step I should run before analysis

This column has dates in mixed formats (DD/MM/YYYY, MM-DD-YY, "Jan 5"). Write the Google Sheets formula or Python snippet to normalize to YYYY-MM-DD.

This list of company names has duplicates with slight variations ("Acme Corp", "Acme Corporation", "ACME"). What's the right way to dedupe?

The model is good at spotting inconsistencies and writing the cleanup code. It's terrible at executing the cleanup on the full dataset — that's your job, in the sheet.

2. Ask better questions

Don't ask "what does this data show?" That gets you a vague summary. Ask the specific question you need answered.

Bad: "Analyze this campaign data."

Better:

Here's last month's email campaign data — sends, opens, clicks, conversions per campaign.

Question: which 2 campaigns underperformed our 22% open-rate baseline by more than 5 points? For each, what's the most likely cause given the subject line and send-day data also in the sheet?

The model can now produce a useful answer because the question is specific. Note: it still might be wrong about cause. Treat causal claims as hypotheses to verify, not conclusions.

3. Generate formulas and transformations

AI is reliably good at translating "I want X" into the right spreadsheet formula or SQL query.

Google Sheets. I have columns: A=date, B=customer, C=plan, D=MRR. Write a formula in cell F2 that returns total MRR for customers on the "Pro" plan whose first signup was in the current quarter.

Same data. Write the formula to count distinct customers who upgraded plan tier between Jan and Mar.

The model writes the formula. You paste it. You verify it on 2-3 rows manually. If the count matches what you can confirm by eye, scale it up.

Always ask the model to explain what the formula does, line by line. This catches errors faster than reading the formula yourself, and it teaches you patterns for next time.

4. Summarize findings with evidence

Once the analysis is done, AI is good for turning "here are the numbers" into "here's what to do." But the summary has to cite the numbers, not make them up.

Useful prompt pattern:

Here are the results: [paste the actual numbers].

Write a 6-bullet summary for a Monday update. Each bullet:
- leads with the number
- explains what it means
- flags what's not yet clear

Do not include any number that isn't in the data I gave you.

That last line is the critical one. Without it, models invent supporting statistics to make summaries sound complete.

A real example

You have a 200-row CSV of last quarter's pipeline. Bad approach: "Analyze my pipeline."

Better approach, in three turns:

Turn 1 — clean and orient:

Here are 15 sample rows from a 200-row pipeline CSV. Columns: deal_id, stage, owner, account_name, amount, created_date, last_activity, source.

What's the cleanup I should do first? Any column with formatting issues?

Turn 2 — targeted question:

Once cleaned: I want to know which deals over $10K have had no activity in 14+ days. Write the formula and explain what it does.

Turn 3 — interpret, with evidence:

Here are the 23 deals that matched: [paste actual rows].

Group these by owner. For each owner: count, total deal value, average days stale. Don't infer cause — just the numbers.

Now you have a real report. The model wrote the formula; you ran it; the model summarized the actual results. No hallucinated numbers.

Where AI helps with spreadsheets

• Formula generation. SUMIFS, INDEX/MATCH, ARRAYFORMULA, regex patterns. Reliable and fast.
• Data cleanup recipes. "How do I normalize these dates / dedupe these names / split this column?" — the model knows the patterns.
• Suggesting segmentations. "What are the obvious ways to slice this data?" When you don't know what cuts to look at, AI proposes a starting set.
• Anomaly spotting in small datasets. Paste 30 rows, ask "what looks off?" — useful for small-scale outlier detection.
• Explaining formulas you inherited. When opening a sheet someone else built with nested formulas you can't parse, AI is faster than reading docs.
• Translating between spreadsheet and code. "Convert this Sheets formula to a Python pandas operation" — accurate enough to ship.

Where AI goes wrong with spreadsheets

The failure modes here are the most expensive in this guide. Be paranoid.

Inventing totals. Models generate digits that look like sums and are not sums. "Total revenue for Q3 was $1,247,392" — if the model didn't run a real calculation, that number is fabricated.

Math errors. Even when the model attempts the calculation step by step, simple arithmetic errors are common. A model that says "47 × 3 = 142" is making a generation error, not a math error.

Spurious precision. "Conversion rate improved 14.7% in Q3." The .7 looks specific. It's usually invented to make the number feel rigorous.

Causal claims from correlation. "Revenue went up because we ran more emails" — the data shows revenue went up and emails went up. The model can't see what else changed.

Treating a sample as the whole. You paste 30 rows from a 5,000-row sheet. The model writes a summary as if the 30 rows are representative. They might not be.

Confident extrapolation from too little data. "Based on these 4 weeks, the trend is X." Four weeks is not a trend.

The general rule: if a number matters, you computed it. Not the model.

A good prompt pattern: code interpreter mode

The single most reliable way to use AI for real data work is to use a model with code execution (ChatGPT with Code Interpreter / Advanced Data Analysis, Claude with code execution). When the model runs Python on your actual file, the numbers are computed, not generated.

The difference in reliability is huge. Prose-generated analysis: untrustworthy. Code-generated analysis from your actual file: trustworthy enough to ship, with verification.

Pattern:

Here is my CSV [upload].

Run code that:
1. Loads the file
2. Counts rows
3. Returns the columns and their data types
4. Reports any missing values per column

Show me the code, the output, and don't summarize until I've seen the numbers.

When AI runs Python on your file, you get a different reliability tier. Use it for anything that matters.

How to verify the output

For any non-trivial analysis, run a 30-second check before trusting the result:

1. Re-compute one key number yourself. If the model says "total = 4,231," pick 2-3 rows and verify the sum.
2. Check segment totals add up. If the model says "Group A = 60%, Group B = 30%, Group C = 10%," they should sum to 100. They often don't in fabricated analyses.
3. Spot-check 5 rows. Pick 5 rows at random and verify the model classified them correctly.
4. Ask the model to explain step by step. "Walk me through how you got the average conversion rate." If it can't, the answer was generated, not computed.
5. Look for suspiciously round or suspiciously precise numbers. Both are tells.

What AI should never be trusted to do alone

• Produce totals that go into a board deck or revenue report without you re-running the math.
• Calculate without showing the logic.
• Infer cause from correlation.
• Analyze a sample as if it were the full dataset.
• Generate numbers from a description ("revenue is roughly $1.2M, so margin is around...") — that's pure fabrication.

If the output goes into a financial document, a customer-facing report, or a strategic decision, the numbers must be derived, not generated.

Common mistakes

1. Asking for analysis instead of formulas. Get the formula, run it, then ask the model to interpret the real output.
2. Trusting prose-mode analysis on real data. Use code interpreter for anything that matters.
3. Pasting too much data. A model can't reliably aggregate 1,000 rows from a paste. Aggregate first, then analyze the aggregate.
4. No verification. The 30-second sanity check catches most fabrications.
5. Treating the explanation as proof. A model that explains its reasoning isn't necessarily correct; it's just plausible-sounding.
6. Letting AI infer cause. Correlation in your sheet is not causation in the world.

The summary, plainly

AI is a fast analyst assistant. It writes formulas, suggests cleanups, proposes segmentations, and explains what you're looking at. It is not a calculator. For anything where being slightly wrong about a number is expensive, either run the math yourself or have AI run code on the file — never accept generated arithmetic. The leverage is in pairing AI's pattern recognition with your verification.

---

Related:

• How to verify AI output before you trust it — verification techniques by content type, including data
• What AI is good at, and what it still gets wrong — the broader capability map
• /prompts/analysis — copy-paste prompts for data analysis tasks
• /skills/spreadsheet — Claude Skills built for spreadsheet workflows