AI at WorkAsk AI for an Excel formula, then test it: eight formulas typed into real Excel
AI is good at suggesting a formula and explaining it. It is not good at telling you that the formula is right for your data. Testing takes one minute.
The method
- Describe the data and the goal. "Column A has region names and column B has sales. I want the total for North."
- Ask for the formula and an explanation in plain words.
- Type it into a copy of your sheet (or a small sample).
- Check the answer by hand on a few rows.
- Test the edge cases: an empty cell, a missing name, text where a number should be, a repeated name.
The results below were produced by typing each formula into a real copy of Microsoft Excel (16.0, build 20326) on 13 November 2026. The formulas are the kind that an AI tool typically suggests. We wrote them ourselves; we did not take them from a tool.
Eight formulas and what Excel showed
| Goal | Formula | Data | Excel showed |
|---|---|---|---|
| Total sales for the North region | =SUMIFS(B1:B3,A1:A3,"North") | North 100, South 200, North 50 | 150 |
| Price before 18% GST, from a GST-inclusive price | =A1/1.18 | A1 = 1180 | 1000 |
| The GST part of that price | =A1-A1/1.18 | A1 = 1180 | 180 |
| Age in whole years on 13 Nov 2026 | =DATEDIF(A1,DATE(2026,11,13),"Y") | A1 = 20 May 2000 | 26 |
| First name from a full name | =LEFT(A1,FIND(" ",A1)-1) | A1 = Asha Rao | Asha |
| Percentage change as text | =TEXT((B1-A1)/A1,"0%") | A1 = 80, B1 = 100 | 25% |
| A date written as 13-Nov-2026 | =TEXT(A1,"dd-mmm-yyyy") | A1 = 13 Nov 2026 | 13-Nov-2026 |
| The price of "Book" (exact lookup) | =XLOOKUP("Book",A1:A2,B1:B2,"not found") | Pen 5, Book 40 | 40 |
The same lookup for "Ruler", which is not in the list, showed not found, the text we put as the fourth argument. Without that argument Excel would show #N/A.
Two traps that look fine and are not
The lookup that quietly gives the wrong price. A formula without the last argument, =VLOOKUP("Pen",A1:B5,2), uses an approximate match, which assumes that the list is sorted. Our list (Pen 5, Book 40, Ink 12, Ruler 8, Eraser 3) is not sorted. Excel showed 12 for Pen, which is wrong (Pen costs 5), with no warning, and #N/A for Book. With FALSE as the last argument, =VLOOKUP("Pen",A1:B5,2,FALSE) showed 5. A formula that "works" on a few test rows can still be wrong on the others: this is why you test with several values, including the first and last.
Numbers stored as text. With 10, 20 and a third cell typed as the text "30", =SUM(A1:A3) showed 30. The text was silently ignored. If your totals look low, check whether some numbers are left-aligned or have a small green triangle.
What to tell the AI so that it helps
- Say which Excel version you use (older versions lack
XLOOKUP,UNIQUE,FILTER). - Say whether your list separator is a comma or a semicolon (it depends on your regional settings).
- Give two or three example rows and the answer you expect for each.
- Ask: "What could go wrong with this formula?"
- Ask for a formula that handles empty cells if you have them.
Do not paste real data
Make a small sample with invented names and numbers. You get the same formula without sending your customers' data anywhere.
Does the AI know my spreadsheet?
Only what you paste or upload. If you upload a file, think about what is in it.
Can I trust a formula that gives the right answer on my test?
Test more than one row, include the edge cases, and check the result by hand once.
This is an independent guide. It is not connected with OpenAI, Anthropic, Google, Microsoft or any other AI company; product names belong to their owners. Statements about companies are taken from their own help pages as read on 13 and 14 November 2026. The prompts are our own wording and were not run against every tool. Not legal, financial or professional advice.