AI chat assistants (ChatGPT, Microsoft Copilot, Gemini, Claude and others) are genuinely useful for Excel: describe what you want and you get a formula or macro in seconds. They are also sometimes wrong in ways that look right. Used with a checking habit, they save a lot of time.
Prompt like you would brief a colleague
Weak: “formula to find price”. Strong:
“I use Excel for Microsoft 365. Sheet
Prices has product codes in A2:A500 and prices in D2:D500. On sheet Orders, B2 has a product code. Write a formula for C2 that returns the price, or ‘Not found’ if the code is missing. Codes may have trailing spaces.”Say your Excel version, the exact ranges, what should happen with errors and blanks, and any data quirks. You will get something like =XLOOKUP(TRIM(B2), TRIM(Prices!A2:A500), Prices!D2:D500, "Not found").
The 5-step check
- Read it. Ask the AI to explain the formula piece by piece. If you cannot follow the explanation, do not use it in a report.
- Test known answers. Pick three rows you can verify by hand, including an edge case (blank, missing code, zero).
- Check the version. Functions like XLOOKUP, FILTER, LET or REGEXEXTRACT do not exist in older Excel — colleagues will see
#NAME?. - Watch ranges. AI often invents ranges (A1:A100) — make sure they match your data and are locked with
$where needed. - Run macros on a copy. VBA from an AI can delete rows or overwrite sheets. Save first; Undo does not work after a macro.
⚠️ Do not paste confidential data — customer lists, salaries, unreleased financials — into public AI tools unless your company explicitly allows it. Describe the structure instead of pasting the data.
Where AI is most useful
- Explaining an inherited monster formula line by line.
- Converting a formula to a newer function (nested IFs → IFS or SWITCH).
- Writing regular expressions and text-cleaning formulas.
- Drafting VBA you then step through with F8.
Build the checking skill yourself with the Formula Lab and VBA Bug Hunt.