Blog / Data Analysis
What AI Gets Wrong in Excel (and How to Work Around It)
A workbook is not a document. It is a set of tabs that talk to each other. ChatGPT, Claude, Gemini, and Copilot will still give you a tidy paragraph. That does not mean they saw the whole file, or followed the formulas that actually produce the number.
If you only remember one thing: you still own the number. The model is a fast reader with no memory of how your office built this file. It cannot sit next to you, click every tab along the bottom, and trace “this total comes from that sheet.” When it sounds sure, check anyway.
Before you upload anything, do this in Excel yourself: count the tabs. Click a few totals and look at the formula bar (the long strip above the grid). If you see another sheet name in there, that cell is a chain, not a fact. Write those tab names on a sticky note. Then ask the AI how many tabs it saw. If its list is shorter than yours, do not trust the analysis.
1. It cannot read every tab
The issue. A real file often has twelve tabs: Raw, Mapping, Assumptions, Jan–Jun, YTD, Pivot, Notes. Tools drop tabs when the file is large, when a sheet is hidden, or when they only ingest the first sheet. The model will still talk as if it read “the workbook.”
You will not get a humble “I only saw two sheets.” You will get a revenue story built on the pretty Summary tab, while the complete list lives on Raw.
Work around it. Open the file. Count the tabs with your finger. Ask: “List every tab name you can see, and how many rows each has. If you cannot see a tab, say so.” If the list is incomplete, do not ask for a grand total. Export the one tab that is the source of truth as its own CSV, or paste that range only. One honest tab beats a fake tour of the whole book.
2. Formulas that point at other tabs
The issue. Dashboard cells often look like simple numbers. Click one and the formula bar shows something like =SUM('Jan'!F:F)+'Assumptions'!B2. That total is not stored on the dashboard. It is pulled from other sheets, which may pull from still other sheets. AI mostly sees the finished number on the screen. It does not walk the chain.
So it can “analyze Q2” from a dashboard that is still pointing at last year’s Assumptions tab, or at a named range you renamed last Tuesday. The sentence will still sound current.
Work around it. For any number you care about, click the cell. If the formula bar mentions another sheet, that is your map. Tell the model: “Do not use the Dashboard numbers as source data. Recalculate from tab X, columns A–G, rows 2–500.” Better: copy the source range, not the dashboard. If you cannot follow the formula in ten seconds, do not outsource the total.
3. It sees the number, not the recipe
The issue. Excel shows 1,240,000. The cell might be =E12*Assumptions!C4, or a value someone pasted yesterday to “lock” a slide. AI cannot tell a live formula from a dead paste. If you ask “why did this change,” it will invent a business reason instead of “someone overwrote the formula.”
Work around it. In Excel, use Formulas → Trace Precedents (or click the cell and look up). If it is a formula, ask AI only after you say so: “This cell is =SUM(Raw!F2:F900). Check my range, do not re-guess the total.” If it is a paste, treat it as a snapshot. Do not let the model update it.
4. Lookups that quietly grab the wrong row
The issue. VLOOKUP, XLOOKUP, INDEX/MATCH stitch tabs together: “find this customer ID on the Rates sheet and bring back the fee.” Duplicate IDs, extra spaces, or an approximate match will pull the neighbor’s rate. AI then averages those fees like they are gospel. You cannot see the miss unless you spot-check IDs.
Work around it. Pick three IDs you know by heart. Find them on both tabs. If the lookup fee does not match, stop the AI job. Tell it: “Join on column A only. Exact match. If an ID is missing, return UNKNOWN, do not skip or guess.” Never let it “fill in” missing rates.
5. Titles, notes, and TOTAL rows get treated as data
The issue. Row 1 is a report title. Row 3 is headers. The last row says TOTAL. AI adds those into counts, averages, and “top customers.” Your average revenue jumps because it included the grand total as if it were one more customer.
Work around it. Say it out loud in the prompt: “Header is row 3. Skip rows 1–2. Do not include any row that says Total, Subtotal, or Note.” Then in Excel, click the data block and glance at the count in the status bar. If AI used 201 rows and Excel shows 200 plus a total, you found it.
6. A filter is still on (or rows are hidden)
The issue. You filtered to West last week and left it. You see 40 rows. The sheet has 400. Hidden rows and merged header cells make it worse. The model often treats the visible slice as the whole dataset, then writes “company-wide.”
Work around it. Data → Clear (the funnel icon). Unhide rows. Ask yourself: do I want all rows, or only this filter? Write that in the prompt. If you need a regional cut, filter in Excel first, save a copy named west-only.csv, and upload that. Do not hope the model noticed the little funnel on column C.
7. Mixed units, currencies, and Excel date numbers
The issue. One tab is in thousands. Another is in dollars. Dates show up as 45900. Percents are 0.15 on one sheet and 15 on another. AI adds them, then writes a clean sentence. Nobody at the meeting will catch it until finance does.
Work around it. Name the unit in the prompt: “Column F is USD, not thousands. Column G is a percent stored as 0.15.” Ask for five raw sample values before any summary. If a “date” is a five-digit number, fix the column in Excel first. Do not ask the model to interpret it.
8. It adds the summary tab to the detail tab
The issue. Monthly tabs plus a YTD tab that already sums the months. A pivot next to the source list. Ask “total revenue” and it may add every tab. Same trap as stacking a dashboard that already references the months.
Work around it. One source of truth. Either the detail rows or the YTD sheet, never both. Say: “Sum Jan–Jun on the monthly tabs only. Ignore YTD, Dashboard, and Pivot.” If you cannot name the source tab in one breath, you are not ready to ask for a total.
9. Broken cells get filled in
The issue. #REF!, #N/A, cutoff pastes, blurry screenshots. The model would rather finish the sentence than say it cannot see the cell. That is how a made-up $1.2M lands in a deck.
Work around it. Put this in every data prompt: “If a figure is missing, filtered, or unreadable, write UNKNOWN. Do not guess.” Then pick three numbers from its answer and find them in the file with your own eyes. If you cannot click them, they do not ship.
A human checklist before you send it
- Did I count the tabs? Did the AI list the same ones?
- For the headline number: is it a typed value, or a formula pointing at another sheet?
- Did I name one source tab, not “the whole file”?
- Are filters off, unless I meant them?
- Did a TOTAL row get counted twice?
- Same units (thousands vs dollars, 0.15 vs 15%)?
- Can I click three of its numbers in Excel?
If any answer is “I’m not sure,” do not paste it into an email. That pause is the job. For a wider review habit, see AI workflow evals and human review.
Prompt to paste
Turn one catch into proof
Run this on a file you already use. Screenshot the wrong total, the formula bar that showed another tab, and the corrected number. That is a stronger career artifact than “I use AI for analysis.”
Case study template Prompt library