Describe the formula you need in plain words

Plenty of capable people still look things up and add them up by hand every week, because spreadsheet formulas feel like someone else’s skill. The hard part was always the syntax, and syntax is what an AI assistant writes well. You describe your columns and the result you want in plain words, the assistant writes the formula and explains each piece, and you test it on rows where you already know the answer.

The test is what makes this work. An assistant writes a formula that looks right as fluently as one that is right, and it can’t see the blank cells and odd dates in your real data. Five well-chosen rows catch the difference before it reaches a report.

Give it about an hour. By the end of today, one step you now do by hand will be a formula you can explain, tested on five rows, with a note in the cell for the next person who opens the file.

Find the step worth replacing

Look for something you count, look up or flag by eye on a schedule. For example: copying prices from a price list into an order sheet, marking which invoices are past due, counting the sessions each participant attended, checking each person’s remaining days of leave, or spotting products below their reorder level. If you do it every week or month and the rule doesn’t change, a formula can do it.

Pick one and do it by hand once more, noting when you start and finish. That’s your own before figure, to set against the formula afterward.

What to tell the assistant

A formula depends on details the assistant can’t see, so give them up front:

• Program and version. Excel for Microsoft 365, Excel 2019, Google Sheets. Some functions don’t exist in older versions. In Excel for Windows, File > Account shows your version; on a Mac, Excel > About Excel does (checked in October 2026).
• Interface language and country. Excel translates function names into the language of its interface: XLOOKUP is BUSCARX in Spanish Excel. Google Sheets has its own setting for this, “Always use English function names”, under File > Settings. Depending on your regional settings, a formula also separates its arguments with commas or with semicolons. Any formula that already works in the same file shows you which.
• Column letters and what each one holds, the row where the data starts, and roughly how far down it goes.
• Two or three sample rows, made up but shaped like yours. Invent the names and amounts, and keep the formats: how dates are written, whether codes mix letters and digits.

Made-up rows are enough for the assistant to get the structure right, and your real data stays out of the chat. Before you upload a client document covers what happens to what you paste.

A prompt to copy

I use [program and version], with the interface in [language] and regional settings for [country].

My sheet "[sheet name]" has these columns:
A: [what it holds]
B: [what it holds]
C: [what it holds]
The data starts in row 2 and runs to about row [number]. New rows get added at the bottom.

Three made-up rows in the same format as mine:
[paste or type them]

In column [letter] I want: [the result, in plain words].

Please give me:
1. One formula for row 2 that I can copy down, with the function names and argument separator my program uses.
2. A plain explanation of each part, short enough to paste into a cell note.
3. The cases where it would give a wrong answer or an error: blank cells, dates or numbers stored as text, duplicates, rows added later.
4. A corrected version, if any of those cases needs one.
If anything about my data is unclear, ask me before you write the formula.

Three examples, with made-up data

Look up a price from another sheet

An order sheet has product codes in column A. A second sheet, called Prices, lists codes in column A and unit prices in column B. The formula for the unit price:

=IF(A2="","",XLOOKUP(A2,Prices!A:A,Prices!B:B,"Not found"))

XLOOKUP finds the code in the price list and returns the price beside it, or “Not found”. The IF in front leaves the cell empty when there’s no code. XLOOKUP works in Excel for Microsoft 365, Excel 2021 and later, and Google Sheets. Excel 2016 and 2019 don’t have it and show #NAME?; there you need =VLOOKUP(A2,Prices!A:B,2,FALSE). Keep the FALSE. Without it, VLOOKUP settles for an approximate match and can return another product’s price with no error at all.

Flag overdue invoices

An invoice sheet has the due date in column C and the date paid in column D. Ask for “Overdue if it’s unpaid and the due date has passed”, and a typical first answer is:

=IF(AND(D2="",C2<TODAY()),"Overdue","")

It works on ordinary rows. On a row with no due date it says “Overdue”, because an empty cell counts as zero, which the spreadsheet reads as a date long past. A due date stored as text may never be flagged at all. A version that handles both:

=IF(D2<>"","",IF(C2="","No due date",IF(NOT(ISNUMBER(C2)),"Check date",IF(C2<TODAY(),"Overdue",""))))

A six-column tracker for everything you’re waiting on uses the same guard against blank dates to highlight the follow-ups due today.

Count attendance per participant

An Attendance sheet has one row per person per session, with the name in column A. On a summary sheet with each participant’s name in column A:

=COUNTIF(Attendance!A:A,A2)

It counts how many times the name appears, including a person entered twice for the same session, and it misses “Ana Ruiz “ with a stray space at the end. Neither problem shows an error, which is why the next section exists.

The five-row test

Before you trust the formula, pick five rows where you already know the right answer, and type that answer in a spare column. Include:

• two ordinary rows with different results;
• a row with a blank in a cell the formula uses;
• a date or number stored as text (31/12/2026 typed into a sheet set up for the United States stays text, and ISNUMBER on that cell returns FALSE);
• a duplicate.

If your data lacks any of these, add made-up test rows at the bottom and delete them afterward. Then paste the formula, copy it down and compare its results with yours. Each mismatch goes back to the assistant in one line: “Row 5 has no due date and your formula says Overdue. It should say No due date.” When all five match, fill the formula down the whole column.

If the program refuses the formula as soon as you press Enter, check the separator first. #NAME? often means a function name your version or language doesn’t recognize.

Leave a note for the next person

Paste the assistant’s plain explanation into a note on the first cell of the column, so whoever opens the file next, including you in six months, knows what the formula does and assumes. In Excel, right-click the cell and choose New Note (earlier versions call notes comments). In Google Sheets, select the cell and press Shift+F2, which also works in Excel (checked in October 2026). Add the date and the five rows you tested.

When a filter, a sort or a pivot table is simpler

A formula pays off when the same question comes back with new rows. For a one-time question, the tools you already have are quicker and let you see the rows:

• Which invoices are overdue right now? Filter the paid column for blanks and sort by due date.
• Totals by month, region or category. A pivot table does this without a single formula, and A monthly summary you build once and refresh each month walks you through one.
• A column nobody reads. Before automating it, ask whether anyone uses it. Deleting it is the fastest fix there is.

Try it today

Open the spreadsheet behind the step you picked and describe that column to the assistant with the prompt above. Run the five-row test and paste the explanation into a note. You’re done when the formula fills the column, all five test rows match your answers, the note sits on the first cell and the made-up test rows are gone. Then put your before figure next to the time the step takes now.

If the test turned up stray spaces, duplicates or dates stored as text, clean the list next: Clean up a messy contact list with formulas the assistant writes for you fixes every row at once with the same describe-and-test method. Once your numbers are right, Let the spreadsheet do the math and the assistant write the words turns them into the commentary your monthly report needs.