AI can write, explain and fix Excel formulas in seconds, if you know how to ask and how to check. This free course shows you how to use Microsoft 365 Copilot, the COPILOT function, ChatGPT, Claude and Gemini to build lookups, conditional logic, text cleaning, date maths, dynamic arrays and LAMBDA functions, then verify every answer before you trust it.
What you will learn
Ask the right way
A five-part prompt template that describes your data, the result you want and the Excel version you use, so the first formula is usually right.
Use every AI tool honestly
Copilot in Excel, the COPILOT worksheet function, Analyze Data, ChatGPT, Claude, Gemini and Python in Excel, with licence and version notes for each.
Build real formulas
XLOOKUP and INDEX MATCH, IFS and SWITCH, TEXTSPLIT and REGEX, date and working-day maths, FILTER, SORT, UNIQUE, LET and LAMBDA, all written with AI and tested.
Check before you trust
Explain and debug any formula, catch hallucinated functions, test edge cases, and keep company data private while you work.
Course curriculum
-
What AI can and cannot do in ExcelCopilot, ChatGPT, Claude, Gemini and Python in Excel compared, with what each needs and where each fails.
-
How to write a prompt for an Excel formulaThe five-part prompt template and how to describe your sheet so AI gets the references right.
-
Copilot in Excel: formula suggestions, column formulas and Analyze DataWhat the Copilot pane can do with a table, and what the free Analyze Data button already does.
-
The COPILOT function and AI inside a worksheet formulaSyntax, licensing, limits and practical uses of =COPILOT() for classifying, summarising and extracting.
-
ChatGPT, Claude and Gemini for Excel formulasA repeatable workflow: describe, paste sample rows safely, test, refine.
-
Lookup formulas with AI: XLOOKUP, INDEX MATCH and multi-criteriaPrompts that produce exact-match lookups, two-way lookups and lookups on several conditions.
-
Conditional logic with AI: IF, IFS, SWITCH, AND and ORTurn business rules written in plain English into tested formulas.
-
Text cleaning with AI: TEXTSPLIT, TEXTBEFORE, TRIM and REGEXSplit names, extract codes, remove junk characters and validate patterns.
-
Date and time formulas with AIAges, deadlines, working days, month ends and fiscal periods, with the traps AI often misses.
-
Dynamic array formulas with AI: FILTER, SORT, UNIQUE and SEQUENCE (coming soon)One formula that spills a whole report, and how to describe it to AI.
-
LET and LAMBDA formulas written by AI (coming soon)Readable, reusable formulas and custom functions generated from a description.
-
Explain, debug and fix a formula with AI (coming soon)Paste a broken formula and get a plain-English explanation and a corrected version, error by error.
-
Verifying AI formulas: test cases, edge cases and hallucinations (coming soon)A checking routine that catches wrong ranges, invented functions, circular logic and version mismatches.
-
Python in Excel and AI for analysis (coming soon)When a formula is the wrong tool, and how Copilot and ChatGPT write PY() code for you.
-
Privacy, data security and best practices with company data (coming soon)What each tool does with your data, how to anonymise, and a policy checklist for teams.
Who this course is for
Analysts, accountants, HR and operations staff, students, and managers who already use Excel and want AI to speed up formula writing without introducing silent errors. No coding is required. If you have a Microsoft 365 Copilot licence you can follow every chapter; if you only have free ChatGPT, Claude or Gemini you can still complete chapters 1, 2 and 5 to 15.
Skip the build: ready-made Excel dashboards and tools
NextGenTemplates has 7,000+ ready-to-use Excel, Power BI and Google Sheets templates with the formulas already written and tested. Instant download, video tutorial with every file.
Frequently asked questions
Do I need a paid Copilot licence?
Only for the Copilot pane and the COPILOT function (chapters 3 and 4), which need a Microsoft 365 Copilot licence or a Microsoft 365 Personal or Family plan that includes Copilot. Everything else works with the free tiers of ChatGPT, Claude or Gemini.
Which Excel version do I need?
Excel 365 gets every function covered here. Excel 2021 and 2024 have XLOOKUP, FILTER, LET and LAMBDA but not the newest text and REGEX functions. Excel 2019 and 2016 users can still use AI for classic functions; each chapter states what works where.
Is it safe to paste company data into ChatGPT?
Not by default. Chapter 15 explains what each service retains, how to anonymise sample rows, and how to use enterprise plans or in-Excel tools that keep data inside your tenant.
Will AI replace learning formulas?
No. AI writes a first draft; you still need to know enough to read it, test it and spot a wrong range or an invented function. That is exactly what chapters 12 and 13 teach.