AI + Excel: Generate Formulas Instead of Memorising Them
There are two kinds of Excel users in every office. The one who knows the formulas, and the one who knows which colleague knows the formulas. For most of my career I was the second kind with ambitions. I could do a VLOOKUP if I had an example open in another tab. Anything with IF inside IF, I quietly rebuilt by hand with a calculator.
AI changed this completely — not by teaching me formulas, but by removing the need to memorise them. What you need instead is the skill of describing what you want precisely. That skill you already have; it is the same one you use when explaining a task to a colleague.
The basic pattern
Describe your columns, describe the result you want, ask for the formula. That is it. The precision is what matters:
Two habits make this work every time. First, always name the exact columns and cells — "column B has start dates" beats "I have some dates" by a mile. Second, always ask for the one-line explanation. You are not just getting an answer; you are slowly learning to read formulas, which pays off the day you need to fix one at 4:55pm.
Real examples from my week
These are actual asks from my own tracking sheets, generalised so you can adapt them:
- Leave balances: "Formula to subtract total leave taken (column E) from entitlement (column D), but show 'CHECK' instead of a number if the result is negative."
- Expiry warnings: "Formula that shows 'RENEW SOON' if the date in C2 is within 60 days of today, 'EXPIRED' if it is past, otherwise blank." Perfect for document and visa trackers.
- Cleaning imported data: "Formula to extract just the invoice number from text like 'INV-2026-0347 - Pelican Cove' in cell A2."
- Monthly summaries: "Formula to sum column D only where column B says 'Advertising & Marketing' and the date in column A is in July 2026."
Each of these is a SUMIFS or nested IF or date function that I would once have spent twenty minutes searching for. Now the formula arrives in seconds and I spend my time checking it instead.
The debugging trick
The less-known use is the reverse direction. When you inherit a spreadsheet from a colleague and click a cell containing something like a four-line nested formula, paste it in and ask:
That second clause — "when would it go wrong" — has caught real problems for me, like a lookup that silently returns the wrong row when there are duplicate names. AI is often better at spotting the edge case than at celebrating the happy path.
Verify before you trust — here is how
An AI-generated formula is a draft, not an answer, and spreadsheets fail silently. My rule: test on rows where I already know the correct result. Before I drag a new formula down five hundred rows, I apply it to three rows I can check by hand — one normal case, one edge case (empty cell, negative number, a date at year-end), one deliberately wrong input. If all three behave, it goes into the sheet. Three minutes of testing versus a leave tracker quietly miscounting for six months: easy trade.
What to stop memorising and what to keep
Stop memorising syntax — bracket order, argument order, whether it is comma or semicolon. That is now the machine's job. Keep — and build — your understanding of what is possible: that Excel can count conditionally, look values up across sheets, do date arithmetic, split text. You cannot ask for what you do not know exists. Skim a list of function categories once; that map in your head is worth more than knowing any single formula by heart.
This rules-and-verify approach is the same one I use for monthly expense reports — describe the rules precisely once, let the machine repeat them, and keep the checking for yourself.