AI Excel Formula Generator: How to Get Formulas That Actually Work

By the XLsheetAI Team · Updated October 15, 2026 · 12 min read

The short answer

An AI Excel formula generator turns a plain-English request into a working formula, and it works well when you describe your data the way Excel sees it: column letters, a sample row, your Excel version and what should happen when nothing matches. When an AI formula fails, it is rarely because the AI does not know Excel. It is because it guessed something about your sheet and guessed wrong. Fix the prompt, test the result on three rows, and you will paste in formulas that work the first time much more often.

Why AI formulas fail

An AI that writes formulas is predicting what a correct formula looks like for the sheet it imagines you have. Every gap in your description gets filled with a default, and these five defaults cause most of the trouble.

1. It invents your ranges

Ask for "a VLOOKUP to get the price" and you get something like =VLOOKUP(A2,Sheet2!A:B,2,FALSE). The AI does not know your product code is in column B, your price table lives on a sheet called Products, or that the price is the third column, not the second. The formula is valid, so Excel accepts it, and then it returns #N/A or, worse, a number from the wrong column.

2. It assumes the newest Excel

Most AI tools reach for XLOOKUP, FILTER, UNIQUE and LET first because they are the modern answer. Microsoft lists XLOOKUP, LET and dynamic arrays as new in Excel 2021 for Windows, so in Excel 2019, 2016 or older those formulas show #NAME?. The same thing happens when you build a file in Microsoft 365 and send it to someone on an older version. If you are not sure which lookup to use, XLOOKUP vs VLOOKUP covers the trade-off.

3. It uses the wrong separator or language

Excel takes its argument separator from your system's regional settings. Microsoft notes that the comma is the default list separator for US English while many European locales use a semicolon, and a formula typed with the wrong one is rejected (Microsoft Learn: formula errors when the list separator is not set correctly). Most AI answers are written with commas and English function names. If your Excel is in German, it also expects SVERWEIS rather than VLOOKUP and WENN rather than IF.

4. It treats text as numbers, and dates as text

Excel stores a real date as a serial number with a date format on top. Dates that came from an import or a paste are often plain text that only looks like a date. A SUMIFS that compares against DATE(2026,3,1) skips text dates silently and returns a total that is too low, with no error to warn you. Microsoft's fix is in Convert dates stored as text to dates. Product codes and IDs have a similar problem: a code stored as the number 1042 will not match the text "1042" in a lookup.

5. It gets absolute and relative references wrong

An AI writes the formula for one cell. It does not know you plan to fill it down 500 rows unless you say so. =C2*F1 is right in row 2 and wrong in row 3, where it becomes =C3*F2 and multiplies by an empty cell. The fix is one pair of dollar signs, $F$1, but the AI only adds them if it knows the formula will be copied.

The prompt recipe

Every fix above comes down to telling the AI what it would otherwise guess. A prompt that works has four parts, and none of them takes long to type.

• Columns with letters. "A = Date, B = Region, C = Amount, data in rows 2 to 1000." Name the sheet if the data is on a different one.
• One sample row. "Row 2: 14/03/2026, East, 1250." This tells the AI whether a date is a real date, whether 80% is stored as 0.8 or 80, and what a product code looks like.
• Version and separator. "Excel 2019, comma separator" or "Microsoft 365, semicolons, Excel in English." Say Google Sheets if that is where it will run.
• The no-match case. "If the code is missing, show Not found" or "if there are no rows, show 0." Without it you get whatever the AI picks, often a bare #N/A.

Add one more line if the formula will be copied: "I will fill this down from D2 to D500."

Five prompts, before and after

Each pair below shows a vague request, the kind of formula it tends to produce, and a recipe prompt with the formula it should produce. The "after" formulas are written for the exact layout described, so they work as typed on that layout.

1. A lookup that has to survive missing codes

Before: "Write a VLOOKUP to get the price." Typical result: =VLOOKUP(A2,Sheet2!A:B,2,FALSE), wrong column, wrong sheet, #N/A for any new code.

After: "Orders sheet: A = Order ID, B = Product code (like P-104), C = Qty. Products sheet: A = Product code, B = Name, C = Unit price, rows 2 to 500. In Orders D2, return the unit price for the code in B2. Excel 2019, comma separator. If the code is missing, show Not found. I will fill it down."

=IFNA(VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE),"Not found")

IFNA only catches the no-match case, so a typo elsewhere still shows up as an error instead of hiding behind "Not found" the way IFERROR would. On Microsoft 365 or Excel 2021 the same request gives =XLOOKUP(B2,Products!$A$2:$A$500,Products!$C$2:$C$500,"Not found"), using the built-in if_not_found argument described on Microsoft's XLOOKUP page.

2. A monthly total that keeps returning zero

Before: "Sum sales for March for the East region." Typical result: =SUMIFS(C:C,B:B,"East",A:A,"March"), which returns 0 because column A holds dates, not the word March.

After: "A = Date (real dates, row 2 is 14/03/2026), B = Region, C = Amount, rows 2 to 1000. In F2, total Amount for the region typed in E1, for March 2026 only. Microsoft 365, comma separator."

=SUMIFS($C$2:$C$1000,$B$2:$B$1000,$E$1,$A$2:$A$1000,">="&DATE(2026,3,1),$A$2:$A$1000,"<"&DATE(2026,4,1))

Two date conditions, on or after 1 March and before 1 April, catch every day in the month whatever the time stamp. If the total still looks low, check whether some dates are text, as covered above.

3. A pass or fail rule in a semicolon locale

Before: "Pass if the score is 50 or more and attendance is 75% or more." Typical result: =IF(AND(B2>=50,C2>=75),"Pass","Fail"). On a semicolon system Excel rejects it, and if C holds 80% it also fails everyone, because 80% is stored as 0.8.

After: "B = Score out of 100, C = Attendance formatted as a percentage. Row 2: 62, 80%. Pass if score is at least 50 and attendance is at least 75%, otherwise Fail. Excel in English, semicolon separator."

=IF(AND(B2>=50;C2>=0.75);"Pass";"Fail")

The sample row did the real work here. It told the AI the attendance is a fraction, so the threshold became 0.75. More patterns like this one are in Excel IF formula examples.

4. A commission that breaks when you fill it down

Before: "Calculate commission using the rate in F1." Typical result: =C2*F1, correct in row 2 and zero from row 3 down.

After: "C = Sale amount, rows 2 to 200. The commission rate is in F1 only. Put commission in D2 and I will fill it down to D200."

=C2*$F$1

The only change is the dollar signs, and they only appear because the prompt said the formula would be copied.

5. A filtered list on an older Excel

Before: "List every order over 1000." Typical result: =FILTER(A2:C500,C2:C500>1000), which is correct on Microsoft 365 and shows #NAME? on Excel 2019.

After: "A = Order ID, B = Customer, C = Amount, rows 2 to 500. In E2, list the orders with Amount over 1000, all three columns. Excel 2019, so no FILTER. Show blanks when the list runs out."

=IFERROR(INDEX(A$2:A$500,AGGREGATE(15,6,(ROW($C$2:$C$500)-ROW($C$2)+1)/($C$2:$C$500>1000),ROWS(E$2:E2))),"")

Enter it in E2, copy it across to G2, then fill all three down as far as you need. AGGREGATE with option 6 skips the error values created for rows that fail the test, so the formula works without Ctrl+Shift+Enter. It is also much harder to read than =FILTER(A2:C500,C2:C500>1000,"None"), which is why an honest AI should also mention that a plain AutoFilter might be the simpler answer on Excel 2019. The FILTER function page explains the third argument for an empty result.

How to check an AI formula in 60 seconds

Microsoft's own advice for Copilot is to "review, edit, and verify anything Copilot creates" (Copilot in Excel FAQ), and that goes for every AI tool. This is a quick way to do it.

1. Read the ranges. Do the column letters match your sheet? Does the range end at your last row? Are the dollar signs on the parts that should stay fixed?
2. Test three rows you can do in your head. Pick a normal row, one near the top and one near the bottom. If the formula disagrees with your own answer, stop there.
3. Try the edge cases. Blank a cell, type a code that does not exist, enter a number as text, add a duplicate key. The formula should do what you asked for each one, not just avoid an error.
4. Step through anything nested. Select the cell and go to Formulas, Formula Auditing, Evaluate Formula. It shows each part being calculated in order, as described in Evaluate a nested formula one step at a time. In the formula bar you can also select one piece and press F9 to see its value, then press Esc so you do not save the result in place of the formula.

If a check fails, add the missing detail to your prompt and ask again rather than patching the formula by hand. You will get a formula that fits, and you will know what to say next time.

What AI is good and bad at in spreadsheets

AI spreadsheet automation is useful, but it is uneven. Knowing where it is reliable saves time.

The pattern is that AI is strong on Excel knowledge and weak on knowledge of your file. That is also the main difference between the ways you can get a formula generated.

Ways to get a formula generated

There are three common routes. This compares what each one can do, based on Microsoft's documentation for Copilot and on how the other two work.

For a closer look at the first two, read Copilot vs ChatGPT for spreadsheet work. Whichever you pick, the prompt recipe still applies: Copilot can read your headers, but it still cannot guess your business rules or what you want when nothing matches.

If you use Google Sheets, most of this carries over. Sheets has FILTER and its own XLOOKUP, so the version problem matters less, but the sample row and no-match case matter just as much.

Want a formula that fits your sheet?

Describe what you need in plain English and XLsheetAI writes the Excel or Google Sheets formula, explains each part so you can check it, and lets you practise it until it sticks. It also writes VBA, Apps Script and SQL, and turns a photo of a table into an Excel file. Free to download, on iPhone and Android.

Want to know what to ask for in the first place? The Excel formulas cheat sheet lists the formulas worth knowing by name, XLOOKUP vs VLOOKUP explains which lookup to request for your version, and Copilot vs ChatGPT helps you pick the tool.