Updated November 21, 2026. To get a spreadsheet formula from AI, describe your data and the result you want in plain English, including the column letters, the first data row and whether you use Excel or Google Sheets. For example: "In Excel, column A has dates and column C has sales. Give me a formula for total sales in March 2026." Then test the formula on a few rows you can check by hand. This guide shows the prompts that work best, how to fix errors, and how to use AI inside Excel and Sheets.
Why AI is so good at spreadsheet formulas
Formulas follow strict rules, and AI models have seen huge numbers of examples. That makes them very good at translating "I want to add up sales for each region" into something like =SUMIFS(C:C,B:B,"North"). They can also explain formulas you've inherited, find errors and suggest better approaches.
The catch: AI doesn't see your spreadsheet unless you give it the details, and it can confidently produce a formula that looks right but gives the wrong answer. Testing is essential.
Step 1: Describe your data precisely
The single biggest improvement is describing your sheet clearly. Include:
The app: Excel or Google Sheets (and version, if you know it). Some functions differ.
Column letters and headers: "Column A is Date, B is Region, C is Sales."
Where data starts: "Headers are in row 1; data is in rows 2 to 500."
The result you want: "Total sales for the North region in March 2026."
Where the formula goes: "The formula will be in cell F2 and copied down."
Your locale, if outside the US: some countries use semicolons instead of commas between formula arguments.
Example prompt
"I'm using Excel. Row 1 has headers. Column A has dates, column B has region names, and column C has sales amounts, in rows 2 to 500. Write a formula for cell F2 that totals sales for the region named in E2 during March 2026. Explain how it works."
Step 2: Paste a small sample
If your data is messy, paste a few rows into the chat (with private information removed) so the AI sees the real format: dates as text, extra spaces, merged categories and so on. This catches problems before they cause wrong answers.

Step 3: Test the formula
Never trust a formula you haven't tested:
Pick three or four rows you can check by hand or with a calculator.
Compare the formula's result with your manual answer.
Try edge cases: blank cells, zero values, text where numbers should be.
If anything's off, tell the AI exactly what happened: "It returns 0 for March, but there are sales in March."
Step 4: Ask the AI to explain
Ask: "Explain this formula piece by piece, in plain English." This helps you spot mistakes and learn the formula for next time.
Useful formulas AI can help you build
Look up a value: XLOOKUP (in newer Excel and Google Sheets), or VLOOKUP and INDEX/MATCH for older files.
Add up with conditions: SUMIFS, such as total sales by region and month.
Count with conditions: COUNTIFS, such as the number of orders over $100.
Clean text: TRIM, PROPER, TEXTSPLIT and SUBSTITUTE.
Work with dates: EOMONTH, NETWORKDAYS and DATEDIF.
Handle errors: IFERROR, to show a blank or message instead of #N/A.
Filter and sort: FILTER, SORT and UNIQUE in newer versions of Excel and Google Sheets.
Google Sheets extras: QUERY, IMPORTRANGE and GOOGLEFINANCE.

Common needs and the formulas behind them:
You want to... | Formula to ask for |
Look up a value from another table | XLOOKUP (or VLOOKUP) |
Add up values that meet conditions | SUMIFS |
Count items that meet a condition | COUNTIF / COUNTIFS |
Show different results by rule | IF / IFS |
List unique values | UNIQUE |
Join text from cells | TEXTJOIN or CONCAT |
How to fix formula errors with AI
Paste the formula and the error, and describe what you expected:
"This Excel formula returns #N/A: =XLOOKUP(E2,A:A,C:C). E2 contains product codes like 'AB-102'. Column A has the same codes. What's wrong, and how do I fix it?"
Common causes the AI will check:
#N/A: no match found, often because of extra spaces or numbers stored as text.
#VALUE!: wrong type of data, such as text in a math formula.
#REF!: a reference to cells that were deleted.
#NAME?: a misspelled function, or a function your version doesn't support.
#DIV/0!: dividing by zero or an empty cell.
Using AI inside Excel and Google Sheets
Copilot in Excel
Microsoft includes Copilot in Excel on some Microsoft 365 plans. Microsoft's US pricing page lists Microsoft 365 Personal at $9.99 a month and Premium at $19.99 a month for individuals. Copilot can suggest formulas, highlight data and create charts. It works best when your data is formatted as a table with clear headers.
Gemini in Google Sheets
Google offers Gemini in Workspace apps on eligible plans. Google's subscriptions page lists Gemini in Google apps as part of Google AI Pro. Look for the Gemini option in Sheets to help create formulas, tables and summaries.
Free option: any AI chatbot
You don't need a paid plan. Free versions of ChatGPT, Gemini, Claude and Copilot can all write and explain formulas. You just copy the formula into your sheet yourself.
10 AI prompts for spreadsheets
"Write an Excel formula to [goal]. My data is in [describe columns and rows]."
"Convert this Excel formula to work in Google Sheets: [formula]."
"Explain this formula in plain English: [formula]."
"Why does this formula return [error]? Here's my data: [sample]."
"Give me a simpler way to do this: [formula]."
"Write a formula that ignores blank cells and errors."
"Create a formula to clean these names: remove extra spaces and capitalize properly."
"How do I count unique values in column B?"
"Suggest a pivot table to summarize sales by month and region."
"Write step-by-step instructions to create a chart of this data."
Privacy: be careful with real data
Spreadsheets often contain sensitive information, such as salaries, customer details and financial records. Before pasting data into an AI chatbot:
Replace real names and figures with made-up examples that have the same format.
Use AI tools your employer has approved for work data.
Check your chatbot's data settings. In ChatGPT, OpenAI's data controls guide explains how to stop your chats being used for model training.
Frequently asked questions
Can ChatGPT write Excel formulas?
Yes. ChatGPT, Gemini, Claude and Copilot can all write and explain Excel and Google Sheets formulas. Describe your data clearly and test the result.
Is AI accurate for spreadsheet formulas?
Usually, for common tasks, but not always. AI can misunderstand your layout or use a function your version doesn't support. Always test on rows you can check.
Do I need Copilot to use AI in Excel?
No. Any AI chatbot can write formulas for you to paste in. Copilot in Excel is more convenient because it works inside your spreadsheet.
Why does an AI formula work in Google Sheets but not Excel?
Some functions exist only in one app, or behave differently. Tell the AI which app and version you use.
3 tips before you go
Always test a new formula on five rows where you already know the right answer.
Replace real names and figures with dummy data before pasting a sample into a chatbot.
Ask the AI to add a comment explaining each formula so colleagues can follow it later.
