How to Use AI to Write Excel Formulas (and Check They Work)

Written by

You know what the sheet needs to do. Pull the account manager in next to each order, total the North region for the quarter, flag the big deals. You just do not know the formula, and the last forum thread you opened was from 2014 and used functions your Excel does not even show.

Here is the short answer. Describe your sheet to an AI assistant (the sheet names, the column letters, what is in each column, and your Excel version), say exactly what result you want in which cell, and ask for the formula plus a one-line explanation. Then test it on three rows where you already know the answer before you fill it down. ChatGPT, Claude and Gemini all do this well in a normal chat. If you have Copilot in Excel or Gemini in Google Sheets, you can ask right inside the sheet.

Below is the prompt that gets a working formula on the first try, four real examples you can copy, and the five-minute check that stops a wrong formula from ending up in a report.

Want one practical AI workflow like this every week?

No password needed – your reset opens instantly.

No password. Just your email, and you’re in.

Why the AI gives you a wrong formula (and how to stop it)

Most bad formulas from AI are not the AI being dumb. They come from a vague question. “Write a VLOOKUP for my customer list” leaves the assistant guessing your sheet names, your columns, whether you want an exact match and what should happen when a name is missing. It fills those gaps with assumptions, and one wrong assumption gives you a formula that looks fine and returns the wrong number.

Give it four things and the guessing stops:

The layout. Sheet names, column letters and headers, where the data starts (row 2 if row 1 holds headers).
The result. What should appear, in which cell, for one example row. “In F2, the account manager for the customer in B2.”
The edge cases. What to show when there is no match, a blank cell or a zero.
Your version. Excel 365, Excel 2019 or Google Sheets. This matters: Microsoft notes that XLOOKUP is not available in Excel 2016 and Excel 2019, so a modern formula can break on an older install (Microsoft Support).

The copy-paste prompt for any Excel formula

Paste this into ChatGPT, Claude or Gemini and fill in the brackets. It works for one-off formulas and for the ones you will fill down a whole column.

I need an Excel formula. I use [Excel 365 / Excel 2019 / Google Sheets].

My data:
Sheet "[name]": row 1 = headers, data starts in row 2.
Column A = [header] ([what it holds, e.g. text, dates, amounts in euros])
Column B = [header] ([...])
(add every column the formula needs)

What I want:
In cell [F2], show [the result] for that row.
Example: for row 2 I expect [the answer you know is right].
If there is no match or the cell is empty, show [blank / 0 / "Not found"].

Give me:
1. The formula for [F2], ready to fill down.
2. One plain sentence per part explaining what it does.
3. Anything in my data that could make it fail (text stored as numbers, extra spaces, merged cells).

The line “for row 2 I expect” is the one people skip, and it does the most work. It gives the assistant a target to check its own answer against, and it gives you a test you can run in ten seconds.

Four formulas people ask AI for most, with working examples

Picture a simple sales sheet. On the sheet Orders: column A Order ID, B Customer, C Region, D Order date, E Amount. On a second sheet Customers: column A Customer, B Account manager. These are the formulas a good prompt gets you.

1. Look up a value from another sheet (XLOOKUP)

Prompt: “In F2 on Orders, show the account manager for the customer in B2, taken from the Customers sheet. Show Not found if the customer is missing.”

=XLOOKUP(B2,Customers!A:A,Customers!B:B,"Not found")

XLOOKUP searches column A of Customers for the name in B2 and returns the matching value from column B. The fourth part is what shows when there is no match; without it you get #N/A (Microsoft Support: XLOOKUP). On Excel 2019 or older, ask for the INDEX and MATCH version instead:

=IFERROR(INDEX(Customers!B:B,MATCH(B2,Customers!A:A,0)),"Not found")

2. Total with conditions (SUMIFS)

Prompt: “Total the Amount column for region North with an order date in the first quarter of 2026.”

=SUMIFS(E:E,C:C,"North",D:D,">="&DATE(2026,1,1),D:D,"<"&DATE(2026,4,1))

SUMIFS adds up column E only where every condition is true: the region is North, the date is on or after 1 January and before 1 April (Microsoft Support: SUMIFS). Using “before 1 April” instead of “up to 31 March” means an order logged on 31 March with a time on it still counts.

3. Flag rows with a rule (IF)

Prompt: “In G2, show Large if the amount in E2 is 5,000 or more, otherwise Standard.”

=IF(E2>=5000,"Large","Standard")

Simple, but this is where AI saves the most time once the rules pile up (“Large over 5,000, Medium over 1,000, flag anything from the West region in red”). Describe the rules in plain words, in order, and let it build the nesting.

4. Count days since a date

Prompt: “In H2, show how many days have passed since the order date in D2.”

=TODAY()-D2

If the result shows as a date instead of a number, the cell has a date format. Set it to Number. That is exactly the kind of small snag worth asking the assistant about, in the same chat.

Check the formula before you trust it

AI formulas fail quietly. They rarely throw an error; they return a plausible number that is wrong. Microsoft says the same about its own assistant: Copilot can generate formulas that are inaccurate, so you need to review and verify them (Copilot in Excel FAQ). This five-minute check catches almost all of it:

Test three rows by hand. Pick rows where you know the answer: a normal one, one at an edge (a missing customer, the last day of the quarter), and one with a blank. If all three match, fill it down.
Cross-check totals with a filter. For a SUMIFS, filter the sheet on the same conditions and look at the sum in the status bar. Same number? Good.
Step through it. In Excel, Formulas > Formula Auditing > Evaluate Formula shows each part being calculated, which makes a wrong range easy to spot (Microsoft Support).
Ask for the explanation, then read it. If the explanation says “column C” and your region is in column D, you found the bug before it found you.

When the formula breaks: let the AI fix it

Paste the formula, the exact error and one example row. Do not just say “it doesn’t work”.

This formula in F2 returns #N/A:
=XLOOKUP(B2,Customers!A:A,Customers!B:B)
B2 contains "Jansen BV". On the Customers sheet, A14 also shows "Jansen BV".
Why does it fail, and what is the fixed formula?

The usual causes are boring and very fixable. For #N/A, Microsoft lists the main ones: the value really is not in the lookup list, or the two cells hold different data types, such as a number stored as text on one side (Microsoft Support: correct a #N/A error). Invisible extra spaces at the end of a name cause the same thing; the assistant will often suggest wrapping the lookup in TRIM.

Explain a formula you inherited

The other big use: a colleague left, and their workbook is full of formulas three lines long. Paste one in and ask:

Explain this Excel formula step by step, in plain English, for someone who knows basic Excel.
Then tell me what would make it return a wrong result.
[paste the formula]
Column A holds [...], column B holds [...].

The second question is the useful one. It tells you where the old sheet is fragile before you build next month’s report on it. Copilot in Excel can do the same from inside the cell: select it and ask Copilot to explain the formula (Microsoft Support: Copilot in Excel).

Chat assistant or the AI inside your spreadsheet?

Both write formulas. The difference is what they can see and what you need to pay for.

A chat assistant (ChatGPT, Claude, Gemini) works with any spreadsheet and any version, including the free plans. It only knows what you describe, so the prompt above matters. If you are new to these tools, start with our ChatGPT tutorial for beginners or the Claude tutorial for beginners.
Copilot in Excel sees your actual columns, suggests formula columns and explains existing formulas. It needs a Microsoft 365 plan that includes Copilot, and Microsoft says it works on files saved to OneDrive or SharePoint with AutoSave on, with your data set up as a table or a clean range (Copilot in Excel FAQ).
Gemini in Google Sheets lets you type = in a cell and press Ctrl + Alt + G (Cmd + Ctrl + G on a Mac) to describe the formula you want, and offers a Fix button on cells that show an error. It requires an eligible Google Workspace or Google AI plan (Google Docs Editors Help). Our Gemini tutorial for beginners covers the basics.

If you have the built-in option, use it for formulas on the sheet in front of you. Keep the chat assistant for planning a whole workbook, or when you are on a version or plan without it.

Keep company data out of the chat

A formula never needs your real customer names or revenue. Send the headers and two or three made-up rows with the same shape. The formula works the same on the real data.

If you do paste real data into ChatGPT on a personal Free, Plus or Pro plan, know that sharing data for model training is on by default there, and you can switch off “Improve the model for everyone” under Settings > Data controls. OpenAI says content from Business, Enterprise and Edu workspaces is not used for training by default (OpenAI Help Center: data controls). Your company’s own AI policy comes first either way.

Where this goes next

Once writing formulas stops being the bottleneck, the next step is asking AI what the numbers actually say. Our guide on how to use ChatGPT to analyze a spreadsheet for work picks up right there. And if your sheet is fed by copying numbers out of emails or invoices every week, that part can be automated too: see the n8n workflow for automatic invoice processing.

FAQ

Can ChatGPT write Excel formulas for free?

Yes. Writing and explaining formulas is plain text work, and the free plans of ChatGPT, Claude and Gemini handle it. You only need a paid plan for the in-sheet assistants like Copilot in Excel or Gemini in Sheets.

Does the same formula work in Google Sheets?

Often, but not always. Common functions like SUMIFS, IF and XLOOKUP exist in both, while some functions and date handling differ. Say which one you use in the prompt and you get the right version.

Why does the AI formula work on row 2 but not further down?

Usually a reference that should stay fixed moves when you fill down. Ask the assistant: “Which references in this formula need $ signs so it fills down correctly?”

Is it cheating to use AI for formulas?

No more than looking it up in a forum. The difference is speed. Read the explanation each time and you will find yourself writing the simple ones without help after a few weeks.

Get one hands-on AI workflow every week.