AI prompts for Excel and Google Sheets formulas

How to prompt ChatGPT, Claude or Gemini for spreadsheet formulas that work the first time: describe the layout, give sample rows and the expected result, name your app, and ask for edge cases. Includes templates for writing, explaining and fixing formulas.

← Back to guides

Why spreadsheet prompts fail

The model can't see your sheet. 'Sum the sales for each region' gives a formula with guessed columns and ranges.
Excel and Google Sheets differ: some functions exist in only one of them, and argument separators depend on your locale (comma vs semicolon).
Messy data breaks clean formulas: blank cells, numbers stored as text, extra spaces and inconsistent dates.
Without an expected result you can't tell whether the formula is right, only whether it returns something.

What to include in a formula prompt

Your app and version or locale: 'Excel for Microsoft 365, Spanish locale' or 'Google Sheets, US locale'.
The layout: sheet names, which column holds what, the header row, and where the data starts and ends (or that it grows).
3–5 sample rows pasted as text, with the result you expect for them, including at least one awkward row.
Where the formula goes and whether it should spill or be filled down.
Edge cases and what to return for each: blanks, errors, no match, duplicates.
Whether you want one formula or are open to a helper column, which is often easier to maintain.

How to check the answer

Test the formula on the sample rows first and compare with the results you wrote down.
Ask the model to explain the formula piece by piece; if the explanation doesn't match your intent, the formula doesn't either.
Try the edge cases on purpose: an empty row, a text value in a number column, a lookup value that doesn't exist.
Don't paste confidential data. Replace real names and amounts with realistic fake values that keep the same format.

Templates

Write a formula from sample data

App: [Excel for Microsoft 365 / Google Sheets], [locale, e.g. US — comma separators]

Layout:
- Sheet "[name]": headers in row 1, data from row 2 down, grows weekly
- Column A: [what it holds], Column B: [...], Column C: [...]

Sample rows (as text):
[paste 3–5 rows]

Goal: in [cell or column], return [what you need].
Expected results for the sample rows: [list them]

Edge cases: blank rows → [result]; no match → [result]; text in number columns → [result]

Return:
1. The formula, ready to paste into [cell]
2. A plain-language explanation, one line per part
3. Any assumption you made about the data

Opens the analyzer with the prompt prefilled.

Open in Promptea

Explain or fix a formula

App: [Excel / Google Sheets], [locale]

Formula in [cell]:
<formula>
[paste formula]
</formula>

What it should do: [one sentence]
What happens instead: [error message or wrong result, with the input row that causes it]
Relevant columns: [what each referenced column holds]

1. Explain what the formula does now, part by part.
2. Identify why it fails for the row above.
3. Give a corrected formula and say what changed. Keep the same structure unless it can't be fixed without rewriting.

Opens the analyzer with the prompt prefilled.

Open in Promptea

FAQ

Which AI is best for Excel and Google Sheets formulas?

Current models from OpenAI, Anthropic and Google all write common formulas well. The prompt matters more than the model: name your app and locale, describe the layout and give sample rows with expected results. Promptea's Find the Best AI mode can recommend a model for a specific spreadsheet task.

Why does the formula show a parse error when I paste it?

Usually the argument separator: many non-English locales use semicolons instead of commas. Function names can also be localized in Excel. State your app and locale in the prompt, or ask the model to convert the formula to your locale.

Should I ask for one big formula or several steps?

If other people will maintain the sheet, ask for helper columns or named steps. A single nested formula is compact but hard to debug. Tell the model which you prefer; otherwise it will choose for you.