In the sheet or in a chat?
You can ask for a formula in two places. The first is inside the spreadsheet. Copilot in Excel suggests a formula as you type an equals sign and can add a whole formula column from a description. Gemini in Google Sheets writes formulas from its side panel. OpenAI and Anthropic now offer add-ins too: ChatGPT for Excel and Google Sheets and Claude for Excel. Which ones you can use depends on your plan and your administrator.
The second place is any chat window, where you describe the sheet in words. That is slower, but it works with every plan and every version of Excel, and it keeps customer data out of the conversation if you describe columns instead of pasting rows.
| AI inside the spreadsheet | A chat assistant | |
|---|---|---|
| Sees your data | Yes, so it knows the columns. | Only what you paste or describe. |
| Inserts the formula | Yes, often with a preview. | You copy and paste it. |
| Needs | The right plan, and often a file saved in the cloud. | Nothing beyond a chat account. |
| Still needs testing | Yes. Microsoft’s and OpenAI’s help pages both say to check it. | Yes. |
The steps below work in both. In-sheet tools skip the part where you describe the columns, but they still need the rule in plain words and they still get edge cases wrong.
1. Describe the sheet, then the goal
A model writing a formula has to guess four things you know: which app and version you use, where the data sits, what the rule is exactly, and what the right answer looks like. “Write a formula to calculate commission” leaves all four open, so you get a generic formula with made-up cell references that you then rebuild by hand.
Write an Excel formula to calculate commission.
I use Excel for Microsoft 365 on Windows. The formula goes in a sheet my sales manager reuses every month. My table has headers in row 1: A: Rep ID, B: Region, C: Deal value (dollars), D: Close date Two sample rows: R-101 | West | 12,500 | 3/14/2026 R-102 | East | 48,000 | 3/20/2026 Write a formula for column E that works out each rep's commission: 5% of the deal value up to $20,000, plus 8% of the part above $20,000. Deals that closed before 1/1/2026 get no commission. Example: for the sample rows, the right answers are 625 for R-101 and 3,240 for R-102. Give me the formula for cell E2, ready to copy down, then explain each part in one short line. Avoid helper columns and macros. A good answer gives the right result for both sample rows and still works when a cell in column C is blank. If anything in my description is ambiguous, ask me before you write the formula.
We built the “after” version one addition at a time and scored each step on the Prompt Grader. The version and column headers alone lift it from 1 to 3. The answers the sample rows should give, which take one line, add more than any other single change.
Scores from the Prompt Grader’s free ten checks, run on October 28, 2026. The one check the final version skips is who the answer is for, which matters little for a formula.
Work out the expected answers yourself, with a calculator if you need to. For the second sample row: 5% of the first $20,000 is $1,000, and 8% of the remaining $28,000 is $2,240, so $3,240. If you cannot work out the answer by hand for one row, the rule is not clear enough to put in a formula yet, and the model will not make it clearer.
Describe columns rather than pasting real rows whenever the data is about people or customers. Made-up sample rows like the ones above work just as well. If you do need to paste real data, run it through Safe Paste first.
2. Test it on rows you can check
A formula that returns a number is not a formula that returns the right number. Paste it into a copy of the sheet, not the real one, and check three kinds of row before you trust it: the sample rows you already know the answer to, an edge case at the boundary (a deal of exactly $20,000, a deal closed on January 1), and a blank row.
The blank row catches more than you would expect. A natural first answer to the prompt above is:
=IF(D2<DATE(2026,1,1),0,MIN(C2,20000)*0.05+MAX(C2-20000,0)*0.08)
It gives 625 and 3,240 for the sample rows, exactly as asked. But on a row where the date is filled in and the deal value is empty, it pays $1,000. Excel’s MIN function skips empty cells, so MIN(C2,20000) returns 20,000 when C2 is blank. Nothing errors and nothing looks wrong, which is why the prompt asked for blanks to work. The fixed formula checks for a blank first:
=IF(OR(C2="",D2<DATE(2026,1,1)),0,MIN(C2,20000)*0.05+MAX(C2-20000,0)*0.08)
If a test fails, paste the failing row and the answer you expected back into the same chat. “Row 7 should be 0 and your formula gives 1,000” fixes a formula faster than any description of the problem.
3. Make it explain each part
Ask for a one-line explanation of each part, and keep it. Next quarter, when the rate changes or a colleague inherits the sheet, the explanation is what lets someone edit the formula without asking AI to rewrite it from scratch. Paste it into a cell note or a “How this works” tab.
If the explanation does not match the rule you gave, the formula is wrong even when the sample rows pass. That mismatch is worth more than any error message. For a formula someone else wrote, the inherited spreadsheet template does the same thing in reverse: paste the formulas, get the plain-words version.
When the formula fails
Most failures fall into a handful of patterns. Tell the model which one you see, with the cell and the value, and it can usually fix it in one round.
| What you see | The usual cause | What to tell the AI |
|---|---|---|
| #NAME? | A function your version does not have, such as XLOOKUP in Excel 2019, or a typo. | Your exact version, and “use only functions it supports”. |
| #VALUE! | Numbers or dates stored as text, often from an export. | “Column D holds dates as text like 2026-03-14.” |
| #N/A in a lookup | The value is not there, often because of a stray space or a different spelling. | Two values that should match and do not. |
| #SPILL! | A formula that returns several cells is blocked by cells already in the way. | Where the result should go, or ask for one cell per row. |
| Right on row 2, wrong further down | References that should stay fixed move when copied (missing $ signs). | “It must work when copied down to row 500.” |
| Parse error in Sheets | An Excel-only function, or semicolons and commas mixed up by your language settings. | “Google Sheets, with semicolons as separators.” |
If two attempts fail, try a different model before a third. Formulas are a task where models disagree in useful ways, and running the same prompt through Model Compare shows several answers side by side; whichever one passes your test rows wins.
Beyond one formula
The same habits, describe the sheet and say what right looks like, carry over to the bigger jobs people ask AI to do in spreadsheets. Each has a ready prompt in the Excel and data collection:
- Cleaning up names, dates and codes that arrived in a mess: plan the cleanup of messy columns.
- Summing and counting by group: summarize raw data with a pivot table.
- Repeating the same steps every week: a macro or script from a plain description. Run it on a copy first, always.
- Finding typos, duplicates and impossible values: find errors and anomalies.
For a single formula, the spreadsheet formula template is a fuller version of the brief above, with blanks for your layout and edge cases in the answer. Save your filled-in version to My Prompts so the column descriptions are there next time.
A checklist to copy
In the prompt [ ] App and version (Excel for Microsoft 365, Excel 2019, Google Sheets) [ ] Column letters with their headers [ ] Two sample rows (made up if the data is private) [ ] The rule in plain words, with exact numbers and dates [ ] The answers the sample rows should give [ ] "Ask me if anything is unclear" Before you copy it down [ ] Pasted into a copy of the sheet [ ] Sample rows give the expected answers [ ] Boundary rows checked (exactly the limit, the first date) [ ] A blank row gives a sensible result [ ] The explanation matches the rule you gave [ ] Explanation saved next to the formula
Key takeaways
- Give your app and version, column letters, two sample rows and the rule in plain words.
- Add the answers the sample rows should give. It is the single most useful line in the prompt.
- Test on the sample rows, a boundary case and a blank row, in a copy of the sheet.
- A formula can pass your examples and still be wrong on blanks: Excel’s MIN skips empty cells.
- Keep the plain-words explanation next to the formula for whoever edits it next.
Questions
Can ChatGPT write Excel formulas?
Yes, and so can Claude, Gemini and Copilot. They are good at it when you describe the sheet: your Excel version, the column letters, a sample row or two and the answer you expect. They are unreliable when you only describe the goal, because they have to guess where your data is and what your rule means. Test every formula on rows you can check by hand before you rely on it.
Is Copilot in Excel better than ChatGPT for formulas?
Copilot in Excel, Gemini in Sheets and the ChatGPT and Claude add-ins can see your workbook, so you skip describing the columns, and they insert the formula for you. A chat assistant needs the description but works with any plan or version. Neither is reliably right on edge cases, so the testing step is the same whichever you use.
How do I convert an Excel formula to Google Sheets with AI?
Paste the exact formula, say it comes from Excel and must work in Google Sheets, and ask what had to change. Most everyday functions work the same in both. The differences tend to be Excel-only functions, array behavior and separators in some language settings. Test the converted formula on a few rows where you know the answer.
Why does the formula from ChatGPT give an error?
Usually because the model guessed something you did not say: a function your version lacks (#NAME?), numbers stored as text (#VALUE!), a lookup value with a stray space (#N/A), or cell references that move when copied. Paste the exact error, the cell and one row of data back into the chat, and say which version of Excel you use.
Can AI write Excel macros or Google Apps Script?
Yes. Describe what you do by hand, step by step, and which sheets and columns are involved. Always run the macro on a copy of the file first, because a macro can change or delete data with no undo. The macro template asks for settings at the top, plain-English comments and a confirmation message before anything is overwritten or deleted.