Get AI to Write Excel Formulas You Can Trust

Describe the result you want; get the formula and an explanation.

Level 1 · Chat Free · 30 minutes · Updated Oct 31, 2026

“Write me an Excel formula that…” might be the most useful sentence an office worker can type into AI. You describe what you want in plain words, and a few seconds later there’s a formula that would have taken you twenty minutes of searching. Usually it works. Sometimes it works on the rows you look at and quietly fails on the one with a blank cell or a date exactly on the edge. In the next 30 minutes you’ll get a working formula, understand what it does, and prove it on the tricky rows before you fill it down.

Remember the intern: smart, fast, first day on the job. They know every Excel function, but they can’t see your spreadsheet. They don’t know which column is which, that some cells are blank, or what “overdue” means in your company. Describe the sheet properly, and they’re excellent.

There’s good news here, too: unlike a summary or an email, a formula is easy to check. Excel does the math; you just need to test it on rows where you already know the answer.

What you’ll get

What you need

• Claude, any plan. Just a normal chat. Other tools work too; see the end.
• Excel or Google Sheets.
• A practice table. Copy this fictional invoice list into a new sheet, starting in cell A1. Then type the report date 2026-10-31 in cell H1.

Client,Invoice,Due date,Amount,Paid
Client A,INV-101,2026-09-15,1200,Yes
Client A,INV-102,2026-10-01,800,No
Client B,INV-103,2026-10-20,450,No
Client B,INV-104,2026-11-05,2300,No
Client C,INV-105,2026-09-30,600,No
Client C,INV-106,2026-10-31,950,No
Client D,INV-107,2026-11-15,1500,No
Client D,INV-108,2026-08-31,700,Yes
Client A,INV-109,,300,No
Client B,INV-110,2026-10-01,0,No

It has a few traps on purpose: a missing due date, an invoice exactly 30 days overdue, one due on the report date, and paid invoices that would otherwise look overdue. Real spreadsheets have all of these.

When you move to your own sheet, don’t paste real customer names or confidential numbers into the chat. You only need to describe the columns.

The three rules

Part 1: Three formulas, tested (30 minutes)

Step 1. Describe your sheet and the result you want (4 minutes)

Open a new chat in Claude and paste:

I need Excel formulas. Here's my sheet:
- Row 1 is headers. Data is in rows 2-11.
- A: Client (text). B: Invoice number. C: Due date (a real Excel date; some cells are blank). D: Amount (number). E: Paid ("Yes" or "No").
- H1: the report date. Use H1 instead of TODAY(), so results don't change every day.

What I want:
1. Column F, "Days overdue": how many days past the due date, if not paid. Blank if paid, if no due date, or if not yet due.
2. Column G, "Status", using these exact rules in this order:
   - Paid = "Paid"
   - No due date = "No due date"
   - 30 or more days overdue = "Overdue 30+"
   - 1 to 29 days overdue = "Overdue"
   - Due today or within the next 7 days = "Due soon"
   - Otherwise = "Not due"
3. A small table elsewhere: total unpaid amount per client.

For each formula, explain what each part does in plain English.

The exact rules in item 2 matter. “Overdue” sounds obvious until you ask: does “30 days” mean 30 or 31? Is “due today” overdue? Decide, and write it down. The intern shouldn’t decide your company’s rules.

Step 2. Make sure you understand the formula (4 minutes)

Read the explanation. If a part isn’t clear, ask:

Explain the Status formula again as if to someone who only knows SUM and IF. What happens, step by step, for invoice INV-102?

You don’t need to be able to write it. You do need to be able to tell a colleague what it does, because one day it’ll give a strange result and you’ll need to know where to look.

Step 3. Ask for the edge cases (5 minutes)

What kinds of inputs could make these formulas give a wrong or confusing result? Think about blank cells, text instead of dates, "yes" in lowercase, amounts of 0, and rows added later.
For each, say what the formula does now, and whether I should change it.

Decide on each one. For example: should a lowercase “yes” count as paid? (In Excel, text comparisons with = usually ignore case, but ask the intern to confirm for your exact formula.)

Step 4. Test on rows where you know the answer (7 minutes)

Paste the formulas into F2 and G2 only, not the whole column. Check these rows by hand first. With the report date 2026-10-31:

Copy the formulas to those rows and compare. Then check the unpaid totals: Client A should be 1,100 (INV-102 and INV-109), Client B 2,750, Client C 1,550 and Client D 1,500.

Only when all of these match, fill the formulas down.

Step 5. If something’s wrong, give it the evidence (5 minutes)

Don’t just say “it doesn’t work.” Give the intern what a colleague would need:

For row 10 (INV-109): C10 is blank, E10 is "No". Your Status formula returns "Overdue 30+" but it should return "No due date". Here's the exact formula in G10: [paste it]. What's wrong?

The exact cell values, the result you got, and the result you expected. With those three, the fix is usually quick.

Step 6. Leave a note for the next person (5 minutes)

Write a short note (3-4 lines) I can put in a cell comment, explaining what the Status formula does and the rules it uses.

Add it as a comment on G1. The next person to open this file, maybe you in six months, will thank you.

Why checking matters

There’s an important difference between asking AI to write a formula and asking AI to do the math. When it writes a formula, Excel does the calculation, and you can test it. When AI calculates numbers itself, it’s much less reliable. Even the companies that build these tools say so:

• Microsoft’s own warning. When Microsoft added a COPILOT function that put AI answers directly into Excel cells, it told users not to use it for “any task requiring accuracy or reproducibility,” including numerical calculations, and to use regular formulas such as SUM and IF instead (PC Gamer, Aug 2025). The function was retired on September 14, 2026; Microsoft now points users to the Copilot pane in Excel instead (Microsoft Support).
• Harder calculations are where AI slips. In a 2026 benchmark on financial spreadsheets, leading models answered simple lookups correctly about 89% of the time, but medians and percentiles only about 20% of the time (Ravnik et al., FinSheet-Bench, 2026).

That’s the logic of this tutorial: let the AI write the formula, let Excel do the math, and let your test rows prove it works.

Checklist

Before you fill the formula down or share the file:

• I described the columns, the header row, blank cells and my tool (Excel or Google Sheets)
• I wrote the exact business rules (“30 or more days”), instead of letting the AI choose
• I can explain in one sentence what each formula does
• I asked about edge cases and decided how each should be handled
• I tested on rows where I knew the answer, including at least one tricky row
• Totals match a quick check by hand
• The formula uses a report-date cell instead of TODAY() if results need to be repeatable
• There’s a note explaining the formula for the next person
• No confidential data went into the chat

What you just learned

On the surface you got three formulas. Underneath, you practiced giving context about something the AI can’t see (your sheet and your rules) and checking in its cleanest form: known inputs, known outputs, compare.

There’s no Part 2: at Level 1, the reusable part is the habit, and the Step 1 description. Keep a copy of it next to any spreadsheet you maintain.

The same method works for: looking up prices from another sheet, cleaning up names or dates imported from another system, conditional formatting rules, a pivot table you can’t quite set up, and Google Sheets formulas. Change the sheet description and rules in Step 1; keep “edge cases” and “test before you fill down.”

Using a different tool?

The prompts work anywhere. For formulas, a normal chat is all you need.

Features and plan names change often. This table was checked in October 2026; the Gemini in Sheets and Copilot in Excel rows still need checking against their help pages.

Sources

• Microsoft launches Copilot AI function in Excel, but warns not to use it for tasks requiring accuracy (PC Gamer, Aug 2025)
• COPILOT function (Microsoft Support), retirement notice
• FinSheet-Bench (Ravnik et al., arXiv, Mar 2026)