How to use ChatGPT to write Excel formulas, and test them before you trust them

Step 1: Describe the sheet, not just the goal

The AI can't see your workbook. If you only say what you want ("total sales by region"), it has to guess the layout, and the formula it writes will fit its guess. Tell it:

• the sheet name, which column holds what (by letter), and where the headers are;
• the first and last rows of data, or the name of the Excel table if it's in one;
• what the values look like: real dates or text, signs on amounts, and how blanks appear;
• your version of Excel. XLOOKUP, for example, isn't available in Excel 2016 or 2019 (Microsoft);
• one result you've worked out by hand, so you can check the formula against it.

Sheet "Oct GL" has headers in row 1 and data in rows 2 to 480. Column A is the posting date (real Excel dates), column C is the account name, and column F is the amount (debits positive, credits negative). Some amount cells are blank.

In cell J2 of sheet "Summary", I want the total of column F where column C matches the account name in Summary!I2 and the date is in October 2026. I use Excel for Microsoft 365.

Give me one formula, the simplest that works, and explain each part in a sentence. Don't use VBA or array formulas unless nothing else will do.

Step 2: Ask for the simplest formula that works

A common complaint from accountants is that AI overcomplicates formulas. A formula nobody on the team can read is one nobody can review. Ask for the simplest version and a plain explanation of each part. If you get nested IFs four levels deep, ask whether SUMIFS, XLOOKUP or a helper column would do the same job.

For the prompt above, a good answer is a single SUMIFS:

The date test reads "on or after October 1 and before November 1". That includes everything dated October 31, whatever the time, and nothing from November. "Less than or equal to October 31" can miss entries that carry a time of day.

Step 3: Test it on rows you know the answer to

Don't judge a formula by whether it returns a number. Judge it by whether it returns the right number on rows where you already know the answer. Add test rows on a copy of the sheet, covering:

• an ordinary row that should be counted;
• a row that shouldn't be counted, such as another account or another month;
• a blank amount and a zero;
• a number stored as text, which is common in exports. SUMIFS skips text, so the total comes out short with no error;
• dates on the first and last day of the period, and one just outside it;
• an account name with a trailing space, which won't match the name without one.

Then get the same total another way. Filter the column and read the Sum on the status bar, or add up a small sample by hand. If the two figures differ, the formula is wrong until you can explain why.

To see how Excel is working a formula out, select the cell and use Formulas > Evaluate Formula, which steps through each part in order (Microsoft). In the formula bar you can also select one part of a formula and press F9 to see its value. Press Esc afterwards, or the value replaces that part of the formula.

Where AI-written formulas usually go wrong

• An approximate lookup by accident. If VLOOKUP's last argument is left out, Excel uses an approximate match, which can return the wrong row without an error when the list isn't sorted (VLOOKUP). Ask for FALSE as the last argument, or use XLOOKUP, which matches exactly unless told otherwise.
• References that move when you fill down. A range that should stay fixed needs dollar signs, such as $F$2:$F$480. Without them it shifts on each row. Check the formula in the last row, not just the first.
• Ranges of different sizes. SUMIFS needs every range to be the same size. If one runs to row 480 and another to row 500, you get a #VALUE! error.
• Numbers and dates stored as text. They look right and get skipped. =ISNUMBER(F2) tells you which cells are real numbers.
• Errors hidden by IFERROR. Wrapping a lookup in IFERROR(…,0) turns a broken match into a zero that looks like a real answer. Before you accept it, ask what errors it's hiding.
• "This formula will work." A chat assistant that hasn't seen your data can't know that, and one that has can still be wrong. Treat it as a claim to test, not a result.

Fixing a broken formula with AI

Give it four things: the formula, what it returns, what it should return, and a few rows that show the problem. "This formula doesn't work" gets you a rewrite that may fail the same way.

This formula in Summary!J5 returns $0, but the October total for Rent should be $14,200 (I added the rows up by hand):

=SUMIFS('Oct GL'!F2:F480,'Oct GL'!C2:C480,I5)

Three of the Rent rows, copied from the sheet:
10/03/2026 | Rent | 4,600.00
10/15/2026 | Rent | 4,800.00
10/28/2026 | Rent | 4,800.00

What's the most likely cause? Tell me how to confirm it before you rewrite the formula.

Asking how to confirm the cause keeps the fix honest. Here the likely answers are amounts stored as text or a stray space in the account name, and each takes one formula to confirm. Once you have the fix, run it on your test rows again.

If you use Copilot in Excel

An assistant built into Excel, such as Copilot, can see the workbook, so describing the layout matters less. Testing matters just as much: use the same test rows, get a second total, and check the last row as well as the first.

Whichever tool you use, leave a note next to any formula that matters: what it does, and what you tested it on. The next person to review the workbook will need both.