How to Prompt AI to Analyze a Spreadsheet

When people ask how to prompt AI to analyze a spreadsheet and get useless answers, the prompt is almost never the problem. The sheet is. Human spreadsheets carry meaning in merged cells, colour, blank spacer rows, and a title block sitting above the real headers, and none of that survives the trip into a model's context. Fix the shape first, then the prompt becomes almost trivial.

Ten minutes of preparation buys you more accuracy than any amount of prompt tuning. Here is what to fix, and what to ask once you have.

Why your spreadsheet does not survive the upload

A spreadsheet is a picture of a table, not a table. When it gets converted to text for a model, everything that was visual becomes either nothing or noise.

• Merged cells flatten into one populated cell and several empty ones, so a category spanning six rows becomes a category on row one and five orphans.

• Colour disappears entirely. If red means "disputed", that information no longer exists.

• Blank spacer rows read as end-of-data or as rows full of nulls, depending on the converter.

• A title block above the headers means row one is "Q3 Report v4 FINAL" and the model treats that as your column names.

• Numbers stored as text, with currency symbols or thousands separators baked in, stop being numbers.

The CSV format specification is a useful sanity anchor here: one header row, one record per line, no decoration. Anything your sheet does that a CSV cannot express is information you are about to lose.

The ten-minute pre-flight

Make a copy first. Never do this to the working file.

1. Delete everything above the header row, so the header is row one.

2. Unmerge every cell and fill the values down, so each row stands alone.

3. Delete blank spacer rows and blank columns.

4. Turn colour coding into a real column. If red meant disputed, add a disputed column with true or false.

5. Strip currency symbols and thousands separators, and make sure dates are in one format, ideally ISO.

6. Rename ambiguous headers. Amt, Amt2, and Total become invoice_amount_eur, paid_amount_eur, line_total_eur.

7. Export to CSV and open it in a plain text editor. What you see there is what the model sees.

That last step is the one people skip and the one that catches everything. If the CSV looks confusing to you in a text editor, it will confuse the model too.

Say what the columns mean, not just what they are called

Renaming headers gets you halfway. The other half is a short data dictionary in the prompt, because column names cannot carry business rules.

This CSV has one row per invoice line, not per invoice.
invoice_id repeats across lines.

status: one of open, paid, disputed, void.
  "void" rows were cancelled and must be excluded from every total.
paid_amount_eur: 0 until fully paid. There are no partial payments.
due_date: ISO date. Blank means the invoice was issued on account
  with no due date, which is normal for two customers.

When a metric could be computed per line or per invoice, compute
per invoice and say so in your answer.

Four sentences and the model now knows more about your data than a new analyst would on day one. The line about grain, one row per invoice line rather than per invoice, prevents the single most common wrong answer, which is counting invoices by counting rows.

Ask for the method before the number

The habit that changes the quality of the output more than anything else: do not ask for the answer. Ask for the approach, agree it, then ask for the answer.

Instead of "what was our average time to payment last quarter", ask:

Before computing anything, tell me how you would calculate average time to payment from this data. Name the exact columns, how you would handle rows with a blank due date, and whether you would compute per line or per invoice. Do not give me a number yet.

You will frequently discover the model was about to average across invoice lines, or include void rows, or treat blank due dates as zero. Correcting the method costs one message. Correcting a number you already put in a board deck costs considerably more.

Once the method is agreed, ask for the number and the intermediate counts alongside it. "Give me the figure, plus how many rows you included and how many you excluded and why." Those two counts are your audit trail.

Verify with a slice you can check by hand

Never accept an aggregate over a sheet you have not spot-checked. Pick one customer, one month, something with maybe eight rows, and ask for the same calculation restricted to that slice. Then check it yourself in the spreadsheet.

If the small case is right, the method is probably right and the remaining risk is data quality rather than reasoning. If the small case is wrong, you just saved yourself from a wrong conclusion at full scale. Either way it costs two minutes. The general problem of trusting a confident wrong answer is covered in how to tell if an AI answer is hallucinated.

When the sheet is too big

Context limits bite sooner than people expect, and a truncated sheet produces answers that look complete because the model does not announce what it never saw.

Three options, in order of preference:

• Aggregate before uploading. If the question is monthly totals, upload monthly totals. A 40,000-row transaction log becomes 24 rows and the answer gets better, not worse.

• Ask for code instead of an answer. Have the model write the formula, pivot definition, or short script, then run it yourself over the full file. The reasoning happens in the model, the computation happens where all your data lives.

• Slice by the dimension you care about. One region, one product line, one quarter, analysed separately.

The second one generalises well. If you find yourself repeating the same analysis every month, that is the point where the spreadsheet has outgrown being a spreadsheet, and our guide to turning a spreadsheet into an app covers the next step.

FAQ

Should I upload the file or paste the data?

Paste for anything under a few hundred rows, because you can see exactly what the model received. Upload for larger files, and then ask it to echo back the header row and total row count before anything else, so you can confirm it read the whole thing.

Why does it get different numbers each time I ask?

Usually because the question is ambiguous rather than because the model is inconsistent. Two defensible methods produce two different numbers. Pinning down the method first, as above, removes most of the variance.

Can it read multiple sheets or tabs?

Treat each tab as a separate file and say how they relate, including the join key. Models handle a stated relationship well and infer a relationship badly.

How do I get results back in a format I can paste into the sheet?

Ask for the output as CSV with a named header row, or as a structured object. Our post on getting JSON output from AI covers making that reliable rather than occasional.

Is there a shortcut if I do this every week?

Yes: write the data dictionary once and keep it in a file you paste every time. The technique generalises to any recurring task, and the broader system for it is in our prompt engineering guide.