You have a spreadsheet in front of you. You know exactly what you want ("the total spent on groceries this month", "flag every invoice over 100", "find the price of each product in the other tab"), but you don't know the formula. You search, you find a forum thread from years ago with a formula that almost fits, you paste it, and you get an error.
This is one of the places where an AI assistant really shines. Describe your table and what you want in plain words, and it writes the formula, explains it and helps you fix it when it breaks. This guide shows you how to do it with ChatGPT, step by step, for Excel or Google Sheets. It works the same way with Claude, Gemini or any other assistant. If you've never used one, start with our guide on how to use ChatGPT as a beginner.
First, the rule that matters: the AI can be wrong
An assistant writes formulas that look right. Most of the time they are. But sometimes it points to the wrong column, forgets that your first row is a header, uses a function your version doesn't have, or gets a condition backwards. And it says all of this with the same confident tone (that's what we call a hallucination).
A wrong formula is sneaky: it often doesn't show an error. It just gives a wrong number that looks plausible. That's why this guide has two parts that matter as much as each other: getting the formula, and testing it.
Step 1: describe your table precisely
The assistant can't see your file. Everything it knows comes from your description, so the more precise you are, the better the formula. Before asking, note down:
- the software: Excel or Google Sheets, and if you know it, the version (for example a recent Microsoft 365 or an older Excel installed years ago);
- the language of your software: in a French or Spanish Excel, function names are translated and arguments are often separated by semicolons instead of commas;
- the layout: which columns contain what, and whether row 1 is a header;
- the result you want: where the formula goes, and what it should show;
- an example with the expected answer: "for these five rows, the result should be 42".
That last point is the most useful one, and we'll use it again when testing.
About privacy: you don't need to paste real data. Describe the columns, or invent a few sample rows. Never paste a file full of customer names, salaries or health information into a chatbot. Our guide on ChatGPT and personal data explains why.
Step 2: ask for the formula (and the explanation)
Here is a prompt you can copy and adapt. The bracketed parts are yours to fill in:
I'm using [Excel, Microsoft 365, in English].
My sheet "Expenses" has a header in row 1 and data from row 2 to row 200:
- column A: date
- column B: category (text, for example "Groceries", "Transport", "Rent")
- column C: amount (number)
I want, in cell F2, the total of column C for the category written in cell E2.
Example: if E2 contains "Groceries" and there are three grocery rows of 10, 20 and 5, F2 should show 35.
Please:
1. Give me the formula.
2. Explain each part of it in simple words.
3. Tell me which cases could make it give a wrong result.
A typical answer will look like this:
=SUMIF(B2:B200,E2,C2:C200)
Read the explanation: "SUMIF looks in B2:B200 for cells equal to E2, and adds the matching amounts from C2:C200". If you understand that sentence, you can check the formula yourself. If you don't, ask: "explain it again as if I had never used a formula".
Point 3 of the prompt is the one beginners forget. Asking "what could go wrong?" often reveals the traps before you fall into them: a category typed with an extra space, amounts stored as text, a range that stops at row 200 while your data keeps growing.
Step 3: test on a copy, with answers you know
Never paste a new formula straight into an important file. Here's the habit to adopt:
- Make a copy of the file, or duplicate the sheet. If something goes wrong, the original is safe.
- Build a tiny test: five or six rows where you can compute the answer in your head. Three grocery lines of 10, 20 and 5, two transport lines. The total for groceries must be 35.
- Paste the formula and compare with your expected result.
- Try the edge cases: an empty cell, a category that doesn't exist, a negative amount, a date at the very end of the month. What does the formula show? Is that what you want?
- Only then use it on the real file, and spot-check two or three results by hand.
If the result is wrong, don't start over. Tell the assistant exactly what happened:
With your formula, F2 shows 0 instead of 35.
Column C contains amounts like "10,50" typed by hand.
What is wrong and how do I fix it?
Giving the actual result and the expected one is what lets the AI find the problem. "It doesn't work" alone gets a generic answer.
Step 4: ask for an explanation of a formula you already have
The AI is just as useful in the other direction. You inherited a file from a colleague, and cell H14 contains a formula three lines long. Paste it and ask:
Explain this formula step by step, in simple words, as if I were a beginner:
[paste the formula]
Then tell me what it returns for this example: [describe one row].
Then check that example by hand. If the explanation and your manual result agree, you understand the formula. If they don't, either the explanation is wrong or the formula has a bug, and both are worth knowing.
Three useful formulas to start with
Here are three common needs, written for an English-language Excel or Google Sheets. Depending on your settings, the separator may be a semicolon instead of a comma.
Count rows that match a condition:
=COUNTIF(B2:B200,"Transport")
This counts how many cells in B2:B200 contain exactly "Transport".
Show a message when a value is too high:
=IF(C2>100,"Check","")
In D2, this shows "Check" if the amount in C2 is over 100, and nothing otherwise. Copy it down the column to apply it to every row.
Find a value in another table:
=XLOOKUP(A2,Prices!A2:A100,Prices!B2:B100,"Not found")
This looks for the product in A2 inside the "Prices" sheet and returns its price, or "Not found". XLOOKUP isn't available in older versions of Excel: if yours doesn't have it, tell the assistant and ask for an alternative.
Don't learn these by heart. The point is to recognize the shape of a formula so you can read what the AI gives you.
Going beyond formulas
Once you're comfortable, you can ask for more than one formula:
- Fix an error message: "my formula shows #N/A, here's the formula and an example row, why?"
- Clean data: "column B has names typed in different ways (extra spaces, uppercase, lowercase). Give me a formula that returns a clean version in column C."
- Conditional formatting: "how do I color in red every row where column C is over 100?"
- Charts and pivot tables: ask for the steps, then follow them in your software. Menus change between versions, so if a step doesn't match what you see, describe your screen and ask again.
For repetitive spreadsheet work, see also our guide to automating your tasks with AI, and for other office uses, how to use ChatGPT at work.
Common pitfalls
- Describing the table vaguely: "I have a table of expenses" isn't enough. Give the columns, the header row and the range.
- Forgetting the software and its language: a formula written for an English Excel won't work as is in a French one, and a few functions differ between Excel and Google Sheets.
- Trusting a plausible number: a total that "looks about right" can be wrong. Test with values you know.
- Ranges that are too short: if the formula stops at row 200 and you add row 201, it silently ignores it. Ask the AI how to cover the whole column or use a table that grows.
- Pasting confidential data: describe the structure, invent sample rows. The formula doesn't need real names.
- Copying without understanding: if you can't explain the formula in one sentence, ask for a simpler explanation before relying on it. You'll be the one maintaining the file.
Mini exercise: your first AI formula in 15 minutes
- Build a test sheet (4 min): in a new, empty file, type a header row (Date, Category, Amount) and eight rows of invented expenses with three categories.
- Compute by hand (2 min): write down the total of one category and the number of rows of another. These are your expected answers.
- Ask for the formulas (3 min): use the prompt from step 2, adapted to your test sheet, for both results. Ask for the explanation too.
- Test (3 min): paste the formulas. Do they match your expected answers? Then change one amount and check that the result updates correctly.
- Break it on purpose (3 min): add a row with a category typed with a trailing space, or an amount typed as text. Does the result change? Ask the AI why and how to protect the formula.
What to notice: how much easier the formula is to check when you know the answer in advance. That habit, testing with known values, is worth more than any formula.
Going further
To understand why an assistant can write a perfect formula and then a subtly wrong one with the same confidence, play the free chapter "Light the Lantern" in your browser, no account needed: play the chapter. To practice writing precise requests, try the Lab, and to train your eye for confident mistakes, play a round of Hallucination Hunt.
To put these formulas to work on a simple monthly budget, follow our guide on how to make a budget with ChatGPT.
For a general method to check what an AI tells you, read our guide on how to fact-check an AI answer. And if a term trips you up, the glossary is there.
Frequently asked questions
Can ChatGPT write Excel formulas?
Yes. Describe your software, the columns of your table, where the result should go and an example with the expected answer, and it will propose a formula with an explanation. Always test it on a copy of your file with values you can check by hand.
Does it work with Google Sheets too?
Yes. Say clearly that you're using Google Sheets, because a few functions and menus differ from Excel. If your spreadsheet is set to another language, mention it too: the separator between arguments and sometimes the function names change.
Why does the formula from ChatGPT give an error or a wrong result?
The most common reasons are a wrong column or range, a header row counted as data, numbers or dates stored as text, a function your version doesn't have, or the wrong separator. Tell the assistant the exact result you get and the one you expected, and ask it what's wrong.
Can I paste my spreadsheet into ChatGPT?
Avoid pasting real data, especially names, salaries, customer details or anything confidential. Describe the structure of the table instead, or invent a few sample rows. The assistant needs the shape of your data, not the data itself.