How to Use AI to Create Spreadsheet Formulas in Excel and Google Sheets

You know what you want the spreadsheet to do. "Add up West region sales for March." "Pull the price for each SKU from the other tab." You just don't know the formula, and the Stack Overflow answer you found uses a function your version of Excel doesn't have.

This is one of the jobs AI is genuinely good at. Copilot in Excel, Gemini in Google Sheets and free chatbots like ChatGPT, Claude or Gemini can all turn a plain-English request into a working formula and explain it line by line. The catch: they only get it right when you describe your data precisely, and you still need to check the result on real rows before you trust it.

The short version: describe your columns and header row exactly, say which app and version you use, ask for the formula plus an explanation, then test it on a handful of rows, including the awkward ones. The seven steps below walk through that, followed by six worked examples you can copy and adapt. Plan on about 30 minutes the first time.

Which AI tool should you use?

You've got three routes, and any of them works:

• Copilot in Excel. As of September 2026, Microsoft lists eligible plans as Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or a Copilot Chat-eligible business or enterprise subscription (Microsoft Support FAQ). You open it from the Copilot icon in the lower-right corner of Excel (Get started with Copilot in Excel). Excel for Microsoft 365, Excel for iPad and Excel for the web can also suggest a complete formula as soon as you type an equals sign, based on your headers and nearby cells (formula suggestions).
• Gemini in Google Sheets. Click Ask Gemini at the top right and describe what you need, or start typing = in a cell and press Ctrl+Alt+G (Cmd+Ctrl+G on Mac) (Google Docs Editors Help). It needs an eligible Google Workspace or Google AI plan. Since a September 2025 update, Gemini can explain complex formulas step by step, offer several alternatives and fix a formula that errors out (Google Workspace Updates).
• Any chatbot. ChatGPT, Claude, Gemini and friends all write solid formulas on their free tiers. You'll do more describing, because they can't see your sheet, but that's also a privacy plus. Our ChatGPT vs Claude vs Gemini comparison covers the differences, and our list of free AI chatbots with no sign-up is handy if you just want a quick answer.

For most beginners, we'd start with whatever built-in assistant you already pay for, and fall back to a chatbot for tricky or version-specific formulas. The built-in tools see your headers, which saves typing. A chatbot makes you spell things out, which, oddly, often produces better formulas.

1. 1

   Step 1: Work on a copy and strip sensitive data

   Before you paste anything anywhere, save a copy of the file. AI assistants that can edit your workbook (Copilot has an "Allow editing" mode) will change things, and an undo history is not a backup.

   If you're using an outside chatbot, don't paste real customer names, salaries or account numbers. You rarely need them. Three or four made-up rows that match your real structure are enough for the AI to write the formula.

2. 2

   Step 2: Describe your data layout precisely

   This is the step that separates a working formula from a confident-looking wrong one. Vague prompts ("sum sales by month") force the AI to guess your column letters and ranges, and it will guess.

   Tell it:

   • Sheet names and which one the formula goes in
   • Column letters and headers, e.g. "A: Order ID, B: Order date, C: Customer"
   • Where the header row is and where data starts and ends
   • What the data looks like, with 3 to 5 sample rows
   • Data types: are dates real dates or text? Are amounts numbers or text with a currency symbol?
   • The output you want and which cell it goes in

   I'm using [Excel for Microsoft 365 / Excel 2019 / Google Sheets].
   
   Sheet "Orders", headers in row 1, data in rows 2-500:
   A: Order ID (text, e.g. ORD-1042)
   B: Order date (real dates, no times)
   C: Customer (text, some blanks)
   D: Region (North, South, East, West)
   E: Amount (numbers, no currency symbol)
   
   Sample rows:
   ORD-1042 | 2026-03-02 | Acme Ltd   | West  | 1250
   ORD-1043 | 2026-03-02 |            | North | 310
   ORD-1044 | 2026-03-05 | Birch & Co | West  | 88.5
   
   Start date is in H1, end date in H2.
   In H3, I want the total Amount for the West region between
   those dates, inclusive.
   
   Give me one formula, explain each part, and tell me which
   edge cases could break it.

   If your prompts usually come back half-right, our guide on how to write better prompts covers the general techniques behind this template.

3. 3

   Step 3: Say which app and version you use

   Excel and Google Sheets share most basic functions, but the modern ones differ, and AI assistants love modern functions.

   Function	Excel for Microsoft 365 / 2024	Excel 2021	Excel 2019 and earlier	Google Sheets
   XLOOKUP	Yes	Yes	No	Yes
   FILTER, UNIQUE	Yes	Yes	No	Yes
   LET	Yes	Yes	No	Yes
   LAMBDA	Yes	No	No	Yes
   TEXTSPLIT, TEXTBEFORE	Yes	No	No	Use SPLIT
   ARRAYFORMULA	Not needed (dynamic arrays)	Not needed	No	Yes
   QUERY	No	No	No	Yes

   Microsoft's own docs confirm XLOOKUP isn't available in Excel 2016 or 2019 (XLOOKUP), and that LAMBDA and TEXTSPLIT need Excel 2024 or Microsoft 365 (LAMBDA, TEXTSPLIT). If you share the file with someone on an older version, say so too, and ask for a formula that works for both of you.

   Not sure which Excel you have? Go to File, then Account, and look under Product Information.

4. 4

   Step 4: Generate the formula and ask for an explanation

   Paste your prompt and ask for the explanation every time, even if you only want the formula. The explanation is how you catch mistakes: if the AI says "this sums column D" and your amounts are in column E, you've found the bug before it costs you anything.

   In Excel, you can also select any cell and ask Copilot to "explain the formula in the selected cell," which Microsoft pitches for large or inherited workbooks (Understand formulas with Copilot). In Sheets, Gemini now returns step-by-step explanations for complex formulas.

   If the formula throws an error, paste the exact error back (#N/A, #VALUE!, #NAME?, #SPILL!, #REF!) along with the formula. Both built-in assistants and chatbots are good at diagnosing their own errors when they have the message.

5. 5

   Step 5: Test on a few rows, then on the awkward cases

   Never trust a formula you haven't checked by hand. Pick three or four rows, work out the answer yourself (a calculator or a quick filter is fine), and compare.

   Then deliberately test the edge cases that break spreadsheet formulas most often:

   • Blank cells. Does a blank customer count as a customer? Does a blank amount become zero or an error?
   • Numbers stored as text. They often sit left-aligned with a small green triangle in Excel. SUMIFS will skip them. Check with ISNUMBER on a suspicious cell.
   • Dates stored as text or with hidden times. A date of 31 March at 2:15 PM is "after" 31 March, so an inclusive end date can silently drop it.
   • Extra spaces. "West " with a trailing space doesn't match "West." TRIM fixes it.
   • Duplicates in a lookup column. XLOOKUP and VLOOKUP return the first match only.
   • No match at all. What should the cell show?

   If something breaks, tell the AI exactly which row failed and what you expected.

6. 6

   Step 6: Convert it to a robust version

   A formula that works in one cell can fall apart when you copy it down 400 rows. Ask the AI to harden it:

   • Absolute references ($E$2:$E$500) for ranges that shouldn't shift when you copy the formula.
   • Excel Tables (Ctrl+T) or whole-column ranges so new rows get picked up automatically. With a table named Orders, you can write Orders[Amount] instead of a fixed range.
   • Error handling with IFERROR or XLOOKUP's built-in "if not found" argument. Use it carefully: wrapping everything in IFERROR hides real problems. Ask for a specific fallback, like "Not found," rather than a blank.
   • LET (Excel 2021 and later, and Sheets) to name repeated pieces so the formula is readable.

   This formula works in H3: [paste formula]
   Make it robust:
   - Absolute references where needed so I can copy it
   - Handle blanks and no-match cases with a clear message, not a blank
   - Don't hide unexpected errors with a blanket IFERROR
   - Must work in [Excel 2021 / Google Sheets]
   Explain what you changed and why.

7. 7

   Step 7: Document what the formula does

   Future you will not remember why cell H3 contains 140 characters of nested functions. Add a short note: a cell comment, a "Notes" tab, or a label in the cell next to it. Ask the AI to write it for you in one or two sentences, including the assumptions (for example, "dates in column B must be real dates").

   If other people use the file, also note which version of Excel or Sheets the formula needs. That one line saves a lot of #NAME? errors later.

Six worked examples

These examples use the "Orders" layout from Step 2 unless stated otherwise: headers in row 1, data in rows 2 to 500, with order date in B, customer in C, region in D and amount in E. They're illustrative formulas written to each app's documented syntax. Adjust the ranges to match your own sheet, and test them as in Step 5.

1. Replace VLOOKUP with XLOOKUP

A "Products" sheet has SKU in column A and price in column C. On Orders, the SKU is in F2, and you want the price in G2. The classic VLOOKUP breaks if someone inserts a column in the price list. XLOOKUP doesn't, and it has a built-in "not found" result.

Old:
=VLOOKUP(F2,Products!$A$2:$C$200,3,FALSE)

New:
=XLOOKUP(F2,Products!$A$2:$A$200,Products!$C$2:$C$200,"Not found")

Excel 2019 and earlier (INDEX/MATCH):
=IFERROR(INDEX(Products!$C$2:$C$200,MATCH(F2,Products!$A$2:$A$200,0)),"Not found")

Test it with a SKU that has a trailing space and one that doesn't exist.

2. SUMIFS for a date range (and a region)

This is the formula the Step 2 prompt should produce. It works the same in Excel and Google Sheets.

Total between the dates in H1 and H2 (inclusive):
=SUMIFS($E$2:$E$500,$B$2:$B$500,">="&$H$1,$B$2:$B$500,"<="&$H$2)

Same, West region only:
=SUMIFS($E$2:$E$500,$B$2:$B$500,">="&$H$1,$B$2:$B$500,"<="&$H$2,$D$2:$D$500,"West")

If column B contains times as well as dates:
=SUMIFS($E$2:$E$500,$B$2:$B$500,">="&$H$1,$B$2:$B$500,"<"&$H$2+1)

The third version matters more than it looks. It catches orders placed later in the day on the end date, which the "less than or equal to" version quietly skips.

3. Count unique customers

You want the number of distinct customers in column C, ignoring blanks.

Excel 2021, 2024, Microsoft 365 (also works in Google Sheets):
=COUNTA(UNIQUE(FILTER(C2:C500,C2:C500<>"")))

Google Sheets shortcut:
=COUNTUNIQUE(C2:C500)

Excel 2019 and earlier:
=SUMPRODUCT((C2:C500<>"")/COUNTIF(C2:C500,C2:C500&""))

Without the FILTER step, UNIQUE treats blank cells as one more "customer," which is a classic AI-generated off-by-one. Also watch for "Acme Ltd" versus "Acme Ltd." with a period. No formula will know those are the same company.

4. Split full names into first and last

A "Contacts" sheet has full names like "Maria Lopez" in column A. You want the first name in B and the last name in C.

First name (B2):
=IFERROR(TEXTBEFORE(TRIM(A2)," "),TRIM(A2))

Last name (C2), takes the last word, so middle names are skipped:
=IFERROR(TEXTAFTER(TRIM(A2)," ",-1),"")

Excel 2021 and earlier, first name:
=LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1)

First name (B2):
=INDEX(SPLIT(TRIM(A2)," "),1,1)

Last name (C2):
=INDEX(SPLIT(TRIM(A2)," "),1,COUNTA(SPLIT(TRIM(A2)," ")))

Test with a single-word name ("Cher"), a middle name ("Mary Ann Smith") and a surname with a particle ("Ludwig van Beethoven"). The last one will come out as "Beethoven," which may or may not be what you want. That's a decision for you, not the AI.

5. A conditional formatting rule for overdue invoices

Say column F holds due dates and column G holds a status. You want to highlight the whole row when the due date has passed and the status isn't "Paid."

Apply to range: $A$2:$G$500

Formula:
=AND($F2<>"",$F2<TODAY(),$G2<>"Paid")

In Excel, go to Home, then Conditional Formatting, then New Rule, and pick "Use a formula to determine which cells to format." In Sheets, go to Format, then Conditional formatting, and choose "Custom formula is." The dollar sign before F and G locks the columns, while the unlocked row number lets the rule move down each row. That detail is the most common thing AI gets wrong in formatting rules, so check it.

6. Google Sheets QUERY: sales by region for a date range

QUERY is a Sheets-only function that uses a SQL-like language (QUERY function). It's powerful and fiddly, which makes it a good job for AI.

=QUERY(A1:E500,
 "select D, sum(E)
  where B >= date '"&TEXT(H1,"yyyy-mm-dd")&"'
    and B <= date '"&TEXT(H2,"yyyy-mm-dd")&"'
    and D is not null
  group by D
  order by sum(E) desc
  label sum(E) 'Total sales'",1)

The piece AI assistants most often miss is the date handling: QUERY needs dates written as date 'yyyy-mm-dd' inside the query text, so the TEXT function converts the cells in H1 and H2 into that format. Also note that QUERY decides a column's type by its majority data type, so a few text entries in an amount column can turn into blanks. In Excel, a PivotTable does the same job. If you like this style, the thinking carries over directly to our guide on writing SQL queries with AI.

When not to use AI functions inside cells

There's a difference between AI writing a formula and an AI function being the formula.

Excel's =COPILOT() function let you put a prompt directly in a cell. It never left preview (it was limited to the Frontier and Microsoft 365 Insider programs), and Microsoft retired it on September 14, 2026. Cached results stay in existing workbooks, but any cell that recalculates now returns #NAME? (Microsoft Support). Microsoft points users to the Copilot pane instead. While it was live, Microsoft's own guidance told people to avoid it for numerical calculations and to use native formulas like SUM, AVERAGE and IF for "any task requiring accuracy or reproducibility" (as reported by PC Gamer).

Google Sheets has a similar function, =AI() (you can also type =GEMINI()), with the syntax AI("prompt", optional range). It needs an eligible Workspace or Google AI plan, returns text only, can't see the rest of your spreadsheet, and has short- and long-term generation limits; Google also warns that Gemini features may suggest inaccurate information (Google Docs Editors Help). Results don't update when your data changes unless you refresh them.

Where to go next

Formulas are one piece of a wider AI toolkit for office work. Our roundup of the best AI productivity tools for work covers the rest, from meeting notes to email. If your spreadsheets are mostly invoices and expenses, the AI tools for bookkeeping and invoices comparison is the better next stop.

Frequently asked questions

Can ChatGPT write Excel formulas?

Yes, and it does it well on the free tier, as long as you describe your columns, header row and Excel version. It can't see your file unless you upload it, so give it sample rows. Always test the formula on a few real rows before copying it down.

Is Copilot in Excel free?

No. As of September 2026, Microsoft lists Copilot in Excel for Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, commercial Copilot subscriptions and Copilot Chat-eligible business or enterprise plans. If you don't have one of those, a free chatbot is the easiest substitute.

Does Google Sheets have a free AI formula helper?

Gemini in Sheets requires an eligible Google Workspace or Google AI plan, such as Business Standard or Google AI Pro. If you don't have one, any free chatbot can write Sheets formulas; just say "Google Sheets" in your prompt so it uses functions like SPLIT, QUERY and ARRAYFORMULA.

Why does the AI's formula give a #NAME? error?

Usually because it used a function your version doesn't support, such as XLOOKUP in Excel 2019 or TEXTSPLIT in Excel 2021, or a Sheets-only function like QUERY in Excel. Tell the AI your exact app and version and ask for a compatible alternative.

Can I use the COPILOT function in Excel anymore?

No. Microsoft retired the =COPILOT() function on September 14, 2026. Existing cells keep cached results until they recalculate, then show #NAME?. You can still ask Copilot for formulas and analysis through the Copilot pane.