How to Write Excel Formulas Using AI

The formula bar is not an exam

There is a particular kind of dread that arrives when you click into a cell and the formula bar fills up with capital letters, commas and dollar signs that somebody else wrote in 2019. You know what the spreadsheet is supposed to do. You have no idea how it does it, and you are fairly sure that touching anything will set off an alarm.

Here is the good news. You do not need to memorise function names any more. You need to describe, in ordinary words, what you want to happen. That was always the hard part of spreadsheets anyway, and it happens to be the part you are already good at.

Name your columns when you ask

The single biggest upgrade to your prompt costs you ten seconds: tell the AI what your columns are called and which letters they live in. Without that, you get a formula written for an imaginary spreadsheet, and you spend twenty minutes translating it back to yours.

I am using [Excel / Google Sheets]. My headers are in row 1.
Columns: A is [Date], B is [Region], C is [Salesperson], D is [Amount].
My data runs from row 2 to row [5000].
I want [what should happen, in plain words].
Put the formula in cell [G2].
Give me one formula, then explain each part in one line.
If there is a simpler way to do this, say so.

Say what you want the answer to be, not what function you think you need. "Count how many orders came from the North region in March" beats "use COUNTIFS", because guessing the function is the AI's job and knowing the goal is yours. If you name the wrong function, you politely steer it towards a worse answer.

Test on five rows before you trust five thousand

A formula that is wrong in an interesting way looks exactly like a formula that is right. Both produce a number. So before you drag anything down a column of 5000 rows, put it in one cell, beside rows where you already know the answer.

Pick three or four rows you can check in your head. One that should obviously count, one that should obviously not, and an awkward one: a blank, a zero, a refund. If the formula handles those, it will probably handle the rest. If it does not, you have found the problem while it is still one cell wide instead of five thousand.

The formula you inherited

The other half of this skill is reading. Somebody left you a spreadsheet, they have since left the company, and cell M4 contains something with four nested brackets and a stray dollar sign.

Explain this spreadsheet formula to someone who is not technical.
Tell me: what it returns, what each part does, and what would break it.
Then rewrite it in the simplest form that does the same thing.
FORMULA:
[paste it]
WHAT THE COLUMNS ARE:
[A is Date, D is Amount, and so on]

Two formulas are worth recognising on sight. This one looks a value up in another sheet and shows friendly text instead of an ugly error:

=IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not found")

And this one adds up amounts that match two conditions at once:

=SUMIFS(D2:D5000, B2:B5000, "North", C2:C5000, "Priya")

Both work in Excel and in Google Sheets. Newer functions such as XLOOKUP work in Google Sheets and in Microsoft 365, but not in older desktop Excel, so if you are on an inherited work laptop it is worth telling your AI assistant which version you are stuck with.