Prompt AI to Write a Spreadsheet Formula That Works

To prompt AI to write a spreadsheet formula that works, give it three things the formula cannot guess: the exact column layout, two or three real example rows with the answer you expect, and which program you use. Then do not trust the result until it has passed a five-row test sheet built from your own edge cases. A formula that returns a believable number is not a formula that returns the right number.

The AI part takes thirty seconds. The checking is where spreadsheets go wrong, because a wrong formula rarely shows an error. It shows a clean, confident, incorrect total.

What to include when you prompt AI to write a spreadsheet formula

Most bad formulas come from a vague question like "sum the sales for each customer". The model has to guess where the data lives and what counts as a match. Give it the layout instead.

• The program. Excel and Google Sheets share most functions but not all. Sheets has ARRAYFORMULA and QUERY; newer Excel has LET and dynamic arrays. Name which one you are in.

• The sheet map. Write it as plain text: "Sheet Orders, columns A to F: order date, customer, SKU, quantity, unit price, line total. Sheet Prices, column A SKU, column B price."

• Two or three example rows with the answer you want. "Row 2 has SKU AB-100 and should return 24.50." Examples remove more ambiguity than any amount of description.

• The cell where the formula goes, so relative references come out right.

• Your locale. Some regional settings use semicolons instead of commas between arguments. If you paste a formula with commas into a semicolon locale, it fails to parse, and the reverse is true too.

A prompt that works:

I use Google Sheets. Sheet "Orders" has columns A:F = order date, customer,
SKU, quantity, unit price, line total. Sheet "Prices" has SKU in A and price in B.
I want the formula for E2 that looks up the unit price by SKU from Prices.
Row 2 has SKU "AB-100" and should return 24.50. If the SKU is not found,
show "missing SKU" instead of an error. Give me one formula and explain each argument.

The last sentence matters. Asking for an explanation of each argument makes the model commit to a reading you can check, instead of handing back a black box.

The five-row test sheet

Before the formula goes anywhere near real data, copy five rows into a scratch tab. Write the answer you expect in a column next to each row before you paste the formula. If you write expectations after seeing results, you will talk yourself into accepting them.

Run the formula down all five. Four out of five passing is a fail. A typical answer to the prompt above looks like this:

=IFERROR(VLOOKUP(C2, Prices!$A:$B, 2, FALSE), "missing SKU")

It passes rows 1 and 3, fails row 2 because of the trailing space, and fails row 5 because text 1042 does not match number 1042. Tell the model what failed, with the row, and ask for a fix:

=IFERROR(VLOOKUP(TRIM(C2), Prices!$A:$B, 2, FALSE), "missing SKU")

TRIM fixes row 2. Row 5 is a data problem, not a formula problem, and the honest answer is to convert the column to one type rather than stack more functions on top. A good formula and a clean column beat a clever formula over a dirty one.

The failures AI makes most often

After a few dozen of these, the same mistakes keep showing up.

1. Approximate match by accident. VLOOKUP with the last argument left out, or set to TRUE, does an approximate match and happily returns a neighbouring row if the list is unsorted. For exact lookups the last argument should be FALSE.

2. Hardcoded cell references. The formula points at Prices!$A$2:$B$40 and silently ignores row 41 when the list grows. Whole-column ranges like Prices!$A:$B are safer for growing lists, though slower on very large sheets.

3. Wrong function for the program. A formula using a function your version does not have returns #NAME?. Say which program you use, and which version if it is an older Excel.

4. Text and number confusion. Dates and IDs that look like numbers but are text. The visual tell is left alignment in a cell you expected to be right aligned.

5. Fixing one cell, not the column. The formula works in E2 and nobody fills it down. In Google Sheets, ARRAYFORMULA lets one formula return values for a whole range; the Google Sheets help page documents the syntax as ARRAYFORMULA(array_formula), for example ARRAYFORMULA(A1:C1+A2:C2). Use it when new rows arrive all the time and nobody should have to copy formulas down.

When the answer still looks wrong

Ask the model to evaluate the formula by hand on your failing row, step by step, and compare each intermediate value to what you see using Excel's Evaluate Formula tool, or by splitting the formula into helper cells in Sheets. Models are decent at this and it exposes the exact step where the data disagrees with the assumption. For numeric work in general, the habits in how to check AI math before you trust it apply to spreadsheets too: recompute one total independently.

If the problem is the data rather than the formula, cleaning messy spreadsheet data with AI is the better first step. If you want AI to read a whole sheet and answer questions rather than write one formula, see how to prompt AI to analyze a spreadsheet. And if the sheet has started acting like a small database for a real app, the limits in using Google Sheets as an app backend are worth knowing before you build more on top of it.

For the wider picture of getting dependable output from a model, the prompt engineering guide covers the habits that carry across tasks.

Frequently asked questions

Can AI write Excel formulas reliably?

For common patterns like lookups, conditional sums and text cleanup, usually yes. For anything involving unusual data types, regional settings or very large ranges, treat the output as a draft and run it through a small test sheet first.

Why does my AI formula return the wrong number but no error?

Usually an approximate match, a range that stops short of the data, or text that looks like a number. None of these raise an error, which is why checking against rows with known answers matters more than checking for error messages.

Should I paste my real spreadsheet into the AI?

Paste the structure and a few invented or anonymised rows, not customer data. The model needs the layout and examples, not your actual figures.

What if the AI gives me a formula that does not work in my version of Excel?

Tell it the exact error and the program version. A #NAME? error almost always means a function your version does not support, and the model can usually offer an older equivalent.