Using AI to Write Excel Formulas — and How to Check Its Work

Using AI to Write Excel Formulas — and How to Check Its Work

📎 This article includes 1 downloadable practice file ↓

⏱ 3 min readUpdated 28 September 2026

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.

In this article
  1. Prompt like you would brief a colleague
  2. The 5-step check
  3. Where AI is most useful
  4. Continue learning

Prompt like you would brief a colleague

Weak: “formula to find price”. Strong:

Advertisement
“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

  1. 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.
  2. Test known answers. Pick three rows you can verify by hand, including an edge case (blank, missing code, zero).
  3. Check the version. Functions like XLOOKUP, FILTER, LET or REGEXEXTRACT do not exist in older Excel — colleagues will see #NAME?.
  4. Watch ranges. AI often invents ranges (A1:A100) — make sure they match your data and are locked with $ where needed.
  5. 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.

⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All AI Tools guides →
Using AI to Write Excel Formulas: A Safe 5-Step ChecklistAI assistants are great at Excel formulas — when you give them the right…AI Meeting Note-Takers: A Privacy Checklist Before You Turn One OnAI tools that join calls and write minutes are useful — and record everything.…AI for Office Work Lesson 8: Safety, Privacy and Company DataUse AI without leaking data: what not to paste, approved vs consumer tools, anonymising,…AI for Office Work Lesson 7: Copilot in Microsoft 365What Microsoft 365 Copilot does in Word, Excel, PowerPoint, Outlook and Teams, practical prompts…

📎 Practice files for this article

  • 📗
    Dynamic arrays scenarios workbookFILTER, SORT, UNIQUE, SEQUENCE and LET with PASS/FAIL checks, a 'Try it' sheet with spilling formulas, and the two FILTER mistakes that break reports.
    ⬇ XLSX · 10 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong