AI for Spreadsheets and Data Analysis: From Formulas to Insights

Here's the one-sentence version: an AI that writes and runs code to analyze your data is trustworthy; an AI that reads your data and tells you the answer in prose is not. That distinction — between a model that computes and a model that recites — is the whole game when you point a language model at a spreadsheet. Get it right and you'll clean messy exports, build pivots, and generate charts in a fraction of the usual time. Get it wrong and you'll ship a slide with a confidently wrong total, because the model guessed the sum instead of adding the numbers.

This is a practical guide to using AI on tabular data without getting burned. It covers the two fundamentally different ways AI touches a spreadsheet, the specific failure modes that will bite you (silent arithmetic errors, hallucinated columns, quietly dropped rows), and the verification habits that let you move fast without lying to yourself. No hype, no "just ask it anything" — just where the tools are reliable, where they aren't, and how to tell the difference on your own data.

Table of contents

• Key takeaways
• The two ways AI touches a spreadsheet
• How a language model actually "sees" a table
• Why LLMs silently miscount
• Natural-language-to-formula vs natural-language-to-code
• The failure modes that will bite you
• Grounding: verifying AI against your own data
• Natural-language formulas: the safe sweet spot
• Connecting AI to your real data
• Reproducibility and auditability
• Privacy: uploading company data
• Where AI analysis actually fails
• A workflow that keeps you honest
• What AI is genuinely great at here
• FAQ
• The bottom line

Key takeaways

• Two modes, very different trust levels. A code interpreter (the AI writes Python/pandas and actually executes it) computes real answers. A chat model reading a table in its context pattern-matches and can invent numbers. Prefer the one that runs code whenever a number matters.
• LLMs cannot do arithmetic reliably from context. They predict plausible-looking digits. A total can be off by a rounding error or by an order of magnitude, and it will look equally confident either way. This is the single most important thing to internalize.
• The dangerous errors are silent. Hallucinated column names, rows dropped by a bad filter, a join that duplicates records, a date parsed as text — none of these throw an error. The output looks clean and is wrong.
• Natural-language formulas are great for the hard-to-remember, not the hard-to-verify. "Write me an XLOOKUP that..." is a huge time-saver because you can read the formula. "What's the average?" typed into a chat box is a trap because you can't see the computation.
• Verify the process, not just the vibe. Ask for the code, check the row counts, spot-check a few cells by hand, and re-run on a known subset. Trust comes from reproducibility, not from the answer sounding right.
• AI is best at the boring 80%: cleaning, reshaping, formula-writing, first-pass charts, and explaining what a dataset contains. Keep a human on the judgment calls and the final numbers.

The two ways AI touches a spreadsheet

Almost every "AI for data" feature is one of two architectures, and confusing them is how people get hurt.

Mode 1 — the model reads your data as text. You paste a table into a chat window, or the tool stuffs your rows into the model's context window, and you ask a question. The model responds in prose. Under the hood, nothing is calculated — the model predicts the most likely next tokens given a table that looks like yours. For "summarize what this dataset is about" that's fine. For "what's the sum of column C" it is guessing, and the guess is unreliable in a way that scales with how many numbers are involved.

Mode 2 — the model writes code and a sandbox runs it. This is the code interpreter pattern (sometimes branded "Advanced Data Analysis," "Code Interpreter," "data analyst mode," or built into a notebook agent). You give it a file; it writes Python — usually pandas — executes it in a real sandbox, and reports what the code actually returned. The number in the answer came from df["C"].sum(), not from vibes. If you understand how chatbots work under the hood, the difference is stark: one is next-token prediction over your data; the other is next-token prediction over code that then runs deterministically.

The practical rule follows immediately:

	Reading-as-text (Mode 1)	Code interpreter (Mode 2)
Where the number comes from	Predicted by the model	Computed by executed code
Reliable for arithmetic?	No	Yes (if the code is right)
Can you audit it?	No — no artifact	Yes — read the code, re-run it
Good for	Explaining, brainstorming, drafting formulas	Cleaning, aggregating, charting, real answers
Main failure mode	Confident wrong numbers	Wrong logic in otherwise-real code

When a number matters, you want Mode 2 — and you want to see the code. Everything below assumes that.

A few practical wrinkles blur the neat two-box picture, and it's worth naming them so you don't get lulled. First, some tools switch modes invisibly. A single chat product may answer "what's 2+2 across these rows" by writing code one time and by free-associating the next, depending on how it routed your request internally. You cannot tell from the prose which happened. The only reliable signal is an artifact: a visible code block, a "ran Python" indicator, a downloadable output. No artifact, no computation — treat the number as a guess. Second, Mode 2 still uses Mode 1 to decide what code to write. The model reads your question as text, picks columns as text, and only then emits code. So the language-understanding step — the part that hallucinates — sits upstream of the deterministic step. That's why a code interpreter can hand you a real, correctly-computed sum of the wrong column. The arithmetic is bulletproof; the choice of what to add up is not. Third, the sandbox is real but ephemeral. The Python environment usually resets between sessions and sometimes mid-session, so the file you uploaded an hour ago may be gone, and a re-run can silently operate on stale or partial data. Reproducibility (covered below) is what protects you from this.

Keep the mental model precise: Mode 2 doesn't make the AI smart about your data. It bolts a calculator onto a fluent guesser and lets you inspect the wiring. That's a genuine upgrade, but the guesser is still driving.

How a language model actually "sees" a table

To use these tools well you need a mechanical picture of what happens to a spreadsheet when it enters a model. It is stranger than most people assume, and the strangeness explains every failure mode later in this guide.

A table is flattened into a stream of tokens. When you paste rows into a chat window, the model does not receive a grid with columns and types. It receives a one-dimensional sequence of tokens — the same fragments-of-text units it uses for prose. A cell containing 1,024.50 might be split into several tokens (1, ,, 024, ., 50), and the model has to infer from surrounding commas, tabs, or pipes where one cell ends and the next begins. There is no float in there, no column object, no notion that this value lives in row 12 of "amount." The two-dimensional structure you see is reconstructed, imperfectly, from a flat string. This is why a stray comma or an unquoted delimiter inside a field can shift the model's sense of which value belongs to which column — it is parsing geometry out of punctuation.

Numbers are text, and digits are just characters. Because a token like 847 carries no magnitude, the model has no built-in operation that maps 847 + 156 to 1003. It has only learned, from training text, that sequences resembling addition problems tend to be followed by certain digit sequences. For small or common sums this pattern-completion is often right; for arbitrary long columns it degrades, because the model is recalling the shape of an answer, not computing one. Positional value, carrying, and decimal alignment — the mechanics a seven-year-old learns — are not represented anywhere in the forward pass. This is the root reason a chat model can nail a differential-equations explanation yet fumble a 40-row sum: language is what it models; arithmetic is a party trick it half-memorized.

Long tables get truncated or compressed. A spreadsheet with 50,000 rows will not fit in a context window. Depending on the tool, the model may see only the first N rows, a sampled subset, or a summarized description — and then answer as if it saw everything. "The maximum value is 9,900" may simply mean "the maximum value in the 200 rows I was shown is 9,900." Nothing warns you that the tail was cut. This is a silent, structural limit of the reading-as-text mode, and no amount of prompting fixes it; only handing the whole file to code that iterates over every row does.

Code execution changes the physics entirely. When the model instead writes df = pd.read_csv("file.csv"), the file is parsed by pandas — a real parser that assigns dtypes, preserves all rows, and holds actual floating-point numbers in memory. Now df["amount"].sum() runs a genuine, order-independent addition over every value, and the result is returned to the model as a short string it simply relays. The model never touched the numbers; it authored a recipe and read back what the kitchen produced. Every reliable "AI did my analysis" story is this pattern underneath — a language model orchestrating deterministic tools, not a language model doing math. Understanding how chatbots work under the hood makes the boundary obvious: fluency generates the code; the interpreter supplies the truth.

The takeaway isn't "models are dumb." It's that they are the wrong type of machine for arithmetic, and the fix is architectural, not motivational. You cannot prompt your way to reliable mental math; you route the math to something that can actually do it and keep the model in the role it's good at — reading intent and writing code.

Why LLMs silently miscount

It's worth being concrete about why a chat model can't be trusted with a column of numbers, because the failure is counterintuitive: these systems write flawless essays and pass hard exams, so why would they flub a sum a calculator nails?

Because a language model doesn't have a number in it. It has tokens — fragments of text — and it predicts the next one. When you ask for the total of 47 values, it has never "added" anything; it produces a string of digits that looks like a plausible total given everything it has seen. For small, round numbers that plausibility often coincides with the truth. For long columns, decimals, or anything requiring carrying digits, it drifts. The model is equally fluent when right and when wrong, which is exactly what makes it dangerous: there's no tremor in its voice when it's off by 10,000. This is the same root cause behind why models hallucinate — fluency is optimized, not correctness.

The tell is that the errors aren't random noise you can average out. They're confident point estimates. If you paste a budget and ask "did we go over?", a reading-as-text model can answer "no, you're under by $2,300" with total composure while the real answer is "over by $8,000." Nothing about the response signals doubt. This is why "I asked the AI and it said the numbers look fine" is not a control — it's a coin flip wearing a lab coat.

There's a second-order trap worth naming: the model can be directionally right, which is more dangerous than being wildly wrong. If it estimates a sum as 1.18 million when the truth is 1.21 million, nobody's alarm goes off — the figure is close enough to pass a sniff test and wrong enough to blow a forecast. Gross errors get caught because they look absurd. Plausible errors sail through review, get pasted into a deck, and become the number everyone quotes. The reading-as-text mode specializes in plausible errors, which is precisely why it's a poor fit for anything that feeds a decision.

Note also what does not help. Asking the model "are you sure?" or "double-check that" produces another fluent pass over the same tokens, not an independent recomputation; it will often "confirm" a wrong number or, just as uselessly, flip a right one. Raising or lowering temperature changes how adventurous the sampling is, not whether addition happens. Bigger, newer models miscount less often on short inputs, which is arguably worse — the failures get rarer and therefore easier to stop checking for, right up until a long column brings one back. The only real fix is to stop asking the model to be the calculator.

Code interpreters fix the arithmetic (the computer really adds) but introduce a different failure: the code can encode the wrong logic. More on that below, because it's the trap people fall into once they start trusting the tool.

Natural-language-to-formula vs natural-language-to-code

"AI for spreadsheets" actually splits into two translation tasks that feel similar and behave differently. Both take plain English and emit an artifact you can inspect — which already puts them ahead of reading-as-text — but they differ in scope, in where they run, and in how they fail.

Natural-language-to-formula turns "flag every order over $500 from a repeat customer" into a cell formula: an IF, a SUMIFS, an XLOOKUP, maybe a LET or LAMBDA. The formula lives in your sheet, recalculates live as data changes, and is bounded by what the spreadsheet's function language can express. Its great virtue is locality: one formula in one cell, evaluated against rows you can see, with the sheet itself flagging #N/A, #REF!, or #VALUE! the moment the logic is malformed. Its ceiling is also the spreadsheet's ceiling — anything requiring a multi-step pipeline, a join across files, or a statistical model strains the formula bar into unreadable nested parentheses.

Natural-language-to-code turns the same request into Python (pandas), SQL, or an R snippet that runs outside the grid, over the whole dataset, with the full expressive range of a programming language: joins, group-bys, regex, date arithmetic, statistics, plotting. This is what powers code-interpreter mode. The virtue is power and the fact that it operates on every row, not just the visible ones. The cost is distance: the code runs in a sandbox you don't live in, on a snapshot of your data, and a subtle bug (wrong join key, silent type coercion) hides inside real-looking output instead of lighting up a cell red.

The two also fail in characteristically different places. A bad formula usually fails loudly and locally — you see the error value, or the one cell is visibly wrong next to twenty right ones. A bad script usually fails quietly and globally — it computes a clean number over subtly corrupted data, and there's no red cell to catch your eye. That asymmetry should shape which you reach for: for a check you'll eyeball against known rows, a formula's locality is a feature; for a pipeline over data you can't see all of, code's power comes with an obligation to verify row counts and intermediate steps.

	NL → formula	NL → code
Runs where	Inside the spreadsheet cell	In a sandbox / notebook / DB
Operates on	The rows in your sheet	The whole dataset (or a snapshot)
Expressive ceiling	Spreadsheet functions	A full programming language
Recalculates live?	Yes, as data changes	No — it's a one-shot run
Typical failure	Loud and local (#N/A, one wrong cell)	Quiet and global (clean number, wrong logic)
Best for	Bounded lookups, flags, per-row logic	Cleaning, joins, aggregation, stats, charts

Neither is "better." A fluent workflow uses formulas for the things that belong in the sheet and code for the things that don't, and — critically — verifies each on its own terms. The next sections are about that verification, because both artifacts are only as trustworthy as your habit of checking them.

The failure modes that will bite you

Even in code-interpreter mode, the output can be wrong. The good news is these failures are findable if you know their shapes. The bad news is none of them throw an error — the run succeeds, the chart renders, the number is just wrong.

Hallucinated columns. You ask about "revenue"; your file calls it net_sales. A sloppy run invents a revenue column, or worse, silently maps to the wrong one. Always confirm the model is operating on the columns that actually exist — ask it to print df.columns and the first few rows before it computes anything.

Silently dropped rows. A filter like "exclude test accounts" that matches on the wrong string can quietly delete 30% of your data. The aggregate still computes; it's just computed on a subset. Row counts are your seatbelt. Ask for the row count before and after every filter, join, or dedupe.

Join fan-out. Merging two tables on a key that isn't unique multiplies rows. Your customer count triples, your revenue total inflates, and the code ran fine. After any join, check that the row count is what you expected — not "roughly," exactly.

Type coercion. Dates read as strings sort as 1, 10, 11, 2. Numbers stored with currency symbols or thousands separators get read as text and silently excluded from sums. IDs with leading zeros get truncated. Ask what dtype each key column has.

Missing-value math. Depending on the tool, blanks can be skipped, treated as zero, or propagate as NaN and nuke an entire average. "Average order value" over a column with blanks can mean three different numbers. Make the handling explicit.

Timezone and locale drift. Timestamps shifted by a timezone assumption move events across day boundaries; 1.000 means one-thousand in some locales and one in others. On any date- or currency-heavy dataset, this is a top suspect.

The plausible-but-wrong chart. The model picks a chart type that renders cleanly but misleads — a truncated y-axis, a pie chart of things that don't sum to a whole, a trend line through categorical data. The image looks professional; the encoding is wrong.

Double-counting through aggregation order. Averaging a column that already contains averages, summing a "total" row that's part of the data, or computing a rate-of-rates (averaging per-day conversion rates instead of dividing total conversions by total visits) all yield numbers that are internally consistent and externally false. The code is correct; the statistics are not.

Silent sampling. In reading-as-text mode, or with tools that cap how much data reaches the model, an answer may describe only the rows that fit. "The top customer is Acme" can mean "the top customer among the 500 rows I was shown." Ask explicitly whether the computation ran over the full file — and in code mode, confirm len(df) matches the source's real row count.

Stale reruns. Because sandboxes reset, a follow-up question may execute against a re-uploaded or truncated version of your file, or against a variable that was overwritten two steps ago. The answer changes and nobody knows why. Pinning the exact input (a saved file, a known row count) is the antidote.

Notice the pattern: every one of these produces clean-looking output. That's the core skill — assuming success is not the same as verifying it. A useful frame borrowed from engineering: these are all silent failures, and the only defense against a silent failure is an active check. The output will never volunteer that it's wrong; you have to go looking. The rest of this guide is a set of cheap, repeatable ways to look.

Grounding: verifying AI against your own data

"Grounding" is the discipline of forcing every claim the model makes back onto evidence you can point at in your actual file. It's the single habit that separates people who get real leverage from these tools from people who quietly ship errors. The reason it matters so much here is specific: on general knowledge, a hallucinated fact can sometimes be caught because it contradicts things you already know. On your private dataset, you have no prior — if the model says "churn was 6.2%," there is nothing in your head to contradict it. You are maximally dependent on the tool exactly where the tool is least accountable. Grounding rebuilds the accountability by hand.

Concretely, grounding means three things. First, bind the model to your schema before it computes anything. Have it print the real column names, dtypes, and the first and last few rows. This is not busywork — it's how you catch the hallucinated revenue column that's really net_sales, or the "date" column that's actually stored as text. If the model can't correctly describe the file, nothing downstream is trustworthy.

Second, demand traceability from every number to its rows. A good answer isn't "average order value is $84." A grounded answer is "average order value is $84, computed as df['amount'].sum() / len(df) over 12,431 non-null rows, with 209 blank amounts excluded." Now you can check each part: is 12,431 the right row count? Should those 209 blanks have been excluded or treated as zero? The number is no longer an oracle; it's a claim with a receipt.

Third, cross-check against a fact you already trust. Every real dataset has an anchor you know independently — total headcount, last month's revenue from the accounting system, the number of orders from the order confirmation emails. Make the AI reproduce that known quantity first. If it can't recover a number you're certain of, its confidence about the numbers you don't know is worthless. This is the data-analysis version of calibrating an instrument against a known weight before you trust its readings.

Grounding is also the honest answer to "how do I know it didn't hallucinate?" You don't prevent hallucination; you make it cheap to catch by insisting that every claim carry enough provenance to check. A model that shows the code, the row counts, and the excluded values has given you the tools to falsify it — and a claim you can falsify but can't is worth ten claims you simply have to believe.

Natural-language formulas: the safe sweet spot

There's one use of AI on spreadsheets that's almost unambiguously good, and it's worth calling out because it sidesteps the whole trust problem: generating formulas.

"Write me a formula that returns the most recent order date per customer, ignoring cancelled orders" is a fantastic prompt. Why? Because the output is a formula you can read, drop in one cell, and verify against a row you know. The AI isn't giving you an answer to trust — it's giving you an artifact you audit. If the SUMIFS has the wrong criteria range, you'll see it. If the XLOOKUP returns #N/A, the sheet tells you. The spreadsheet itself is the verification layer.

This flips the usual weakness into a strength. The model is excellent at remembering syntax you use twice a year — the argument order of INDEX/MATCH, the nesting for a regex extract, the array-formula incantation — and you're excellent at checking whether the result is right on one visible example. That's a good division of labor. As with any prompting, being specific pays off: name the columns, state the edge cases ("blank means not-yet-shipped"), and say what to return when nothing matches.

One caveat keeps this from being a free lunch: AI-written formulas fail on edge cases you didn't mention. The model writes for the happy path it inferred from your prompt. If your data has blanks where it assumed values, duplicate keys where it assumed uniqueness, or mixed types where it assumed numbers, the formula returns something plausible and wrong on exactly those rows — and those rows are usually the ones that matter. The fix is to state the edge cases in the prompt ("blank means not-yet-shipped, treat as no date") and then to deliberately test the formula on a messy row, not a clean one. A formula that's right on your tidiest record and wrong on your ugliest is worse than useless, because you'll trust it.

The line to hold: use AI to write formulas (which you verify), not to be the calculation (which you can't). "Give me the XLOOKUP" — yes. "Just tell me each customer's latest order" pasted into chat — no.

Connecting AI to your real data

Everything so far assumes you can get your data in front of a model that runs code. In practice that happens through several distinct plumbing arrangements, and each has different implications for trust, privacy, and how much you have to verify.

Upload to a chat-based code interpreter. You hand a CSV or XLSX to a chat product with a Python sandbox. This is the most flexible option — full pandas, real charts, arbitrary transforms — and the easiest to audit, because the code is right there. Its limits are the ones described earlier: an ephemeral sandbox, a file snapshot frozen at upload time (nothing live-updates), and whatever the tool's row or file-size caps are. Best for one-off analyses where you want maximum control and a visible artifact.

In-app AI inside the spreadsheet (Excel Copilot, Google Sheets' Gemini features). Here the AI lives where your data already is. It can read the sheet's structure, write formulas into cells, and describe ranges. This is superb for the natural-language-to-formula workflow and for "explain this sheet," and it keeps data inside a suite you're presumably already governed to use. Watch the boundary carefully, though: some in-app features write a formula (verifiable, lives in the cell, recalculates) while others generate a prose answer or a summary (the reading-as-text trap, now wearing your company's colors). The convenience makes it easy to stop asking which mode you're in. Ask anyway — prefer the features that drop a formula or a pivot you can inspect over the ones that just tell you a number.

Notebook agents (AI inside Jupyter, or "data science agents"). The AI writes and runs cells in a real notebook against a live kernel. This is the most powerful and the most auditable option for anyone comfortable reading code, because you keep the entire execution trace: every transform, every intermediate df.head(), every chart, in order, re-runnable top to bottom. It's the gold standard for reproducibility. The cost is that it assumes some fluency and a notebook environment, which not everyone has.

Connected to a database or a semantic layer (text-to-SQL, BI copilots). Instead of a file, the AI queries a live warehouse. Two very different sub-cases hide here. Raw text-to-SQL points the model at table schemas and lets it write queries — powerful, but the model has to guess what your columns mean, and "revenue" might be booked, recognized, or net-of-refunds depending on a business rule it can't see. A semantic layer (a governed metrics catalog where "revenue" and "active user" are defined once, centrally) is the serious answer to that ambiguity: the AI selects from pre-defined, human-vetted metrics instead of inventing the math each time. If your organization has one, route AI analysis through it — it turns "the model interpreted our metric correctly" from a hope into a guarantee, because the definition lives in the layer, not in the prompt.

The pattern across all four: the more your data's meaning is encoded outside the prompt — in a schema, a notebook trace, a semantic layer — the less the model has to guess, and the less you have to verify. Convenience and trust don't have to trade off, but they do when the plumbing lets the model improvise definitions. Pick the arrangement that pins down meaning, not just the one that's fastest to click.

Reproducibility and auditability

An analysis you can't reproduce isn't a result; it's an anecdote. This is the professional core of using AI on data, and it's where the "move fast" crowd and the "get it right" crowd actually reconcile — because reproducibility is what lets you move fast safely, by making every number cheap to re-verify instead of expensive to re-litigate.

The problem AI introduces is that chat is a terrible medium for reproducibility. A conversation is linear, stateful, and ephemeral: the model's sandbox resets, variables get overwritten, "use the previous result" refers to something you can no longer see, and re-asking the same question tomorrow can produce a different number because the model sampled differently or the file wasn't there. If your analysis lives only in a chat thread, you have a result nobody — including you next week — can independently confirm.

The fix is to extract the artifact and treat the chat as scaffolding you throw away:

• Save the code, not the answer. The reusable, checkable object is the script or the SQL, not the sentence the model wrapped around it. Keep the code somewhere versioned; a number without its code is a rumor.
• Pin the input. Record exactly which file (with a row count, ideally a hash or a dated snapshot) the code ran against. "Revenue was $2.1M" is meaningless without "from orders_2026-06.csv, 14,208 rows." Half of irreproducible results are really unpinned inputs.
• Prefer a linear, re-runnable trace. A notebook that runs top-to-bottom, or a single SQL query, beats a twelve-message chat where state accumulated invisibly. If you can't re-run it from a clean start and get the same number, you can't defend it.
• Separate the deterministic part from the AI part. Once the model has written correct code, the code is what you keep and re-run; the model isn't in the loop anymore. That's the goal — use the AI to author a reproducible pipeline, then let the pipeline stand on its own without the AI.

Auditability is the same idea aimed at other people. When someone asks "where did this number come from?" — and in any consequential setting, someone will — the answer should be a runnable artifact and a named input, not "I asked the AI." The first survives scrutiny; the second is how careers end at board meetings. If you can hand a colleague the code and the file and they get your number, the analysis is real. If you can't, it never was, no matter how confident the chatbot sounded.

Privacy: uploading company data

Accuracy is not the only risk. The moment you upload a spreadsheet, you've made a data-governance decision, and it's worth making it deliberately rather than by reflex. The questions are concrete and answerable:

• Does the tool train on your inputs? Many consumer tiers reserve the right to use uploaded content to improve models; many business and enterprise tiers contractually don't. This is usually a setting or a plan tier, not a mystery — find it and know which side of the line you're on before uploading a customer list.
• Where does the file physically go, and for how long? A code interpreter uploads your actual rows to a server-side sandbox. Ask about retention: is the file deleted when the session ends, or cached? Does it cross a region boundary your compliance regime cares about?
• What's in the sheet? A public dataset and a spreadsheet of patient records, salaries, or unreleased financials are not the same upload. Personal data may pull you under GDPR, HIPAA, or contractual confidentiality obligations the moment it leaves your environment — regardless of how good the tool's security is.
• Who at your company already vetted a tool? The in-suite options (Copilot inside your Microsoft tenant, Gemini inside your Google Workspace) often keep data within a boundary your organization has already reviewed, which is frequently the difference between "allowed" and "resignation-generating event."

If the data is sensitive and the answers are unsatisfying, the strongest mitigation is not to send it at all: run a model locally so the data never leaves your machine, or work on a de-identified or synthetic extract. Our fuller treatment of AI privacy lays out the questions to ask before uploading anything you wouldn't email to a stranger. The one-line policy that keeps most people safe: don't paste into a chatbot what you couldn't defend pasting into a public forum, unless you've confirmed the contractual and technical controls that make it safe.

Where AI analysis actually fails

The failure modes earlier were mechanical — wrong column, dropped rows, bad join. There's a higher and more dangerous tier: places where the code is flawless, the numbers are real, and the analysis is still wrong. These are failures of judgment, not computation, and no code interpreter protects you from them.

It answers the question you asked, not the one you meant. Ask "which channel has the highest conversion rate?" and you'll get a correct answer that may be dominated by a channel with nine visitors and three conversions. The model optimized your literal request; it doesn't know that a rate over a tiny denominator is noise. Statistical significance, base rates, and sample size are judgment the model won't supply unless you demand it.

It confuses correlation with cause on command. "Do users who use feature X retain better?" gets a real correlation and, often, a fluent narrative that sounds causal. The model has no access to your confounders — maybe power users both adopt feature X and retain, and X causes nothing. It will not spontaneously warn you; it will help you build a compelling wrong story.

It has no idea what's normal for your business. A model can compute that revenue fell 12% without any sense that 12% is a seasonal dip you see every June, or a five-alarm fire. Domain context — what's expected, what's an artifact of how the data is collected, which "outliers" are actually data-entry errors — lives in your head, not the file. The AI will treat a broken sensor and a real signal identically.

It cannot see what isn't in the data. Survivorship bias, selection effects, the customers who churned before your export window, the transactions that never got logged — these are invisible to any analysis of the rows you have. The model reasons over the file as if the file were the world. Knowing what's missing is the analyst's job and the one thing the tool structurally cannot do.

It's fluent enough to make a weak analysis persuasive. This is the quiet danger. A model will wrap a shaky finding in confident, well-organized prose, complete with a chart, and the packaging raises your credence past what the evidence earns. Fluency is a presentation layer, not a truth signal — and it's most seductive exactly when the underlying analysis is thinnest. The skill is to let the numbers, not the paragraph around them, set your confidence.

The through-line: AI collapses the cost of computing an answer to near zero, which raises the premium on judging whether it's the right answer. The bottleneck moves from "can I get the number" to "do I understand what the number means," and that second part is still entirely yours.

A workflow that keeps you honest

Here's a sequence that lets you move fast without shipping wrong numbers. It works whether your tool is a chat-based code interpreter, a notebook agent, or an in-app "analyze" button.

1. Start with a data dictionary, not a question. Before any analysis, have the AI describe the file: column names, dtypes, row count, and the first five rows. This catches hallucinated columns and type problems up front, and it forces the model to bind to your schema.
2. State the definition of every metric. "Active user = logged in during the last 30 days, excluding internal accounts." Ambiguous metrics are where wrong logic sneaks in. Write the definition; make the model use it.
3. Demand the code. In code-interpreter mode, ask to see the Python. You don't need to be fluent — you're looking for the obvious: does it filter what you meant, join on the right key, handle blanks the way you said?
4. Watch the row counts. After every filter, join, and dedupe: how many rows now? A number that jumped or cratered unexpectedly is a bug, not a finding.
5. Spot-check by hand. Pick two or three rows and verify the computed value yourself. For an aggregate, re-derive one group's number manually. If it matches, your confidence in the whole rises fast.
6. Re-run on a known subset. Feed it ten rows where you already know the answer. If it nails those, the logic is probably sound. If it doesn't, you found the bug cheaply.
7. Keep the artifact. Save the code or the formula, not just the chart. A result you can't reproduce is a rumor. Reproducibility is the whole point — it's what turns "the AI said" into "here's the query, run it yourself."

To make it concrete, here's the loop on a real task — "what was our average revenue per active customer last quarter?" from a raw export. Step one: the model prints the schema and you discover the amount column is gross_amount (text, with $ and commas) and there's no "active" flag at all — active has to be derived. Step two: you define it — "active = at least one order in Q2, excluding accounts where email ends in your own domain." Step three: you read the code and catch that the model first parsed gross_amount to a float (good) and used an inner join to orders that would have dropped customers with zero Q2 orders — which is fine for this metric but you note it. Step four: row counts — 8,842 customers, 6,109 active after the filter; the 31% drop looks right for a quarterly window. Step five: you hand-check one customer whose orders you can see and the per-customer total matches. Step six: you re-run on a ten-customer sample where you pre-computed the answer in your head; it agrees. Step seven: you save the query and note it ran against orders_2026-q2.csv, 47,203 rows. Total overhead beyond just asking: maybe four minutes — and now the number survives someone asking "how'd you get that?"

None of this is slow once it's a habit. It's the difference between a tool you use and a tool you trust blindly, and only one of those keeps you employed after the board meeting.

What AI is genuinely great at here

Skepticism cuts both ways — it would be dishonest to leave you thinking these tools aren't worth it. They are, enormously, for the right jobs:

• Cleaning and reshaping. Splitting a mashed-together name field, standardizing inconsistent categories ("NY", "New York", "new york"), melting wide data to long, parsing dates in fourteen formats. This is tedious, mechanical, verifiable work — the AI's home turf.
• Explaining an unfamiliar dataset. "What's in this file, and what questions could it answer?" is a great opener that orients you fast, as long as you treat specific numbers it mentions as hypotheses to check.
• Formula and query generation. Covered above. Huge, low-risk wins.
• First-pass exploration. "Show me the distribution of each numeric column and flag anything odd." A code interpreter will produce real histograms and real summary stats you can then interrogate.
• Charts as drafts. Fast to generate, easy to eyeball for "is this the right encoding," trivial to iterate. Just check the axes.
• Translating between tools. Turning a gnarly Excel formula into pandas, or a SQL query into a spreadsheet approach, or explaining what an inherited macro does.

The common thread: these are tasks where the output is inspectable — you can look at the cleaned data, read the formula, or eyeball the chart. AI is strongest exactly where verification is cheap. It's weakest where you're tempted to skip verification because the answer arrived as a confident sentence.

FAQ

Can I trust AI to add up a column of numbers? Only if it's running code, not reading the table as text. A code interpreter that writes and executes something like df["amount"].sum() gives you a real total. A chat model that "reads" your pasted table and replies with a number is predicting plausible digits and can be wrong by any margin, with no signal that it's wrong. When a number matters, insist on the mode that runs code — and glance at the code.

What's the difference between a code interpreter and a normal chatbot for data? A normal chatbot generates text, including text that looks like an answer to a math question — but nothing is computed. A code interpreter generates code, runs it in a sandbox, and reports what the code returned. The first is a fluent guesser; the second is a real calculation you can audit. Any serious data work should use the second.

How do I catch errors if I don't know how to code? You don't need to code — you need to check outputs. Confirm the column names match your file. Watch row counts before and after each filter or join (a big unexpected change is a bug). Spot-check two or three cells by hand. Re-run on a tiny sample where you already know the answer. These checks require zero programming and catch the majority of silent failures.

Is it safe to upload my company's spreadsheet to an AI tool? That's a data-governance question separate from accuracy. Check whether the tool trains on your inputs, where the file is stored, and whether it meets your compliance obligations. For sensitive data, prefer tools with a no-training guarantee, or run models locally. See our note on AI privacy for the questions to ask before uploading anything you wouldn't email to a stranger.

Why does the AI sometimes reference columns that don't exist? Because it's pattern-matching on what a dataset like yours usually contains, not strictly reading your headers. If your data "should" have a revenue column, the model may act as if it does. Prevent this by having it print the actual column names and a few sample rows before any analysis, and by naming exact columns in your prompts.

Are natural-language formulas reliable? More reliable than natural-language answers, because a formula is an artifact you can verify. The model writes the XLOOKUP or SUMIFS; you read it and check it against a row where you know the right result. You're not trusting the AI's arithmetic — you're trusting your own eyes on a single visible example. The one caveat: the model writes for the happy path, so test the formula on a messy row (blanks, duplicates, mixed types), not a clean one — that's where AI-written formulas quietly break.

How much data can I hand an AI at once? Less than you think in reading-as-text mode, and effectively all of it in code mode — which is the whole reason to prefer code. If you paste rows into a chat, only what fits the context window is seen, and long files get silently truncated or sampled, so answers may describe just the visible slice. When the model instead reads the file with code (pd.read_csv), it iterates over every row regardless of size (within the sandbox's memory limits). If a dataset matters and it's large, that gap is decisive: insist the analysis run over the file, and confirm the code's row count matches the source.

Is Excel Copilot or a chat code interpreter more accurate? Neither is inherently more accurate — accuracy depends on whether the specific feature computes or predicts. A code interpreter almost always runs real code, so its arithmetic is sound (the risk is wrong logic in real code). In-app assistants are mixed: features that write a formula or build a pivot are verifiable and reliable; features that return a prose summary or a narrative "insight" are the reading-as-text trap in disguise. Judge the feature, not the brand. The right question is always "did this produce an artifact I can inspect — a formula, a pivot, a code block — or just a sentence?"

Can I trust text-to-SQL against my database? Trust the SQL, not the sentence — and mind the semantics. Text-to-SQL emits a query you can read and run, which is genuinely auditable. The catch is meaning: the model guesses what your columns represent, and "revenue" might be booked, recognized, or net of refunds depending on a business rule it can't see. Read the query, check the join keys and filters, and validate the result against a number you already trust. If your organization has a semantic layer (governed, pre-defined metrics), route AI analysis through it so the definitions are fixed rather than improvised per prompt.

Can AI replace a data analyst? It replaces the mechanical parts of the job — cleaning, reshaping, writing formulas and queries, first-pass charts — and it does them fast. It does not replace the judgment: knowing which question to ask, what's normal for the business, whether a correlation is causal, what's missing from the data, and whether a finding is significant or noise. AI collapses the cost of computing an answer, which raises the premium on judging whether it's the right answer. The analyst's role shifts from producing numbers to interrogating them — a change in the work, not its disappearance.

The bottom line

AI on spreadsheets is one of the highest-leverage everyday uses of these tools — and one of the easiest to misuse, because the failure mode is silence. A wrong total doesn't crash; it renders in 14-point font on a slide. The models that read your data as text will hand you confident, wrong numbers with a straight face; the models that write and run code will give you real numbers if the logic is right, which you have to check.

So keep the discipline simple: make the AI show its work, watch the row counts, spot-check the cells, and use natural-language formulas — which you can verify — instead of natural-language answers, which you can't. Do that and you get most of the speed with almost none of the risk. Skip it and you've automated the production of mistakes. The tool is genuinely great; the judgment about which numbers to trust still has to be yours.

---

Related: How AI chatbots work · Why AI hallucinates · How to write better prompts · AI privacy