Here’s a secret from the inside of a 660-person workplace: most people don’t need an app. They need one Excel formula they don’t know how to write.

I build internal tools for my hospital, so people bring me their computer problems. And for every request that becomes a real tool, five requests are actually a spreadsheet that almost works. A leave tracker that can’t count days. A score sheet where someone types grades by hand because nobody knows nested IFs. A monthly report where a person retypes department names five hundred times because looking them up “seemed too complicated.”

I used to fix these myself. Then I realized I was doing it wrong. The fix wasn’t me writing the formula. It was teaching people that AI writes Excel formulas extremely well — if you describe your table honestly. Now I hand out a prompt template instead. This post is that template, plus everything I learned watching non-technical staff actually use it.

Why smart people get stuck on formulas

It’s not intelligence. It’s vocabulary.

To write a formula the old way, you have to already know the function’s name. If you’ve never heard of VLOOKUP, you can’t search for it — you’d have to search “how to bring a value from another sheet when the names match,” and classic documentation was never written for that sentence. Excel help assumes you know what you’re looking for. Most people know what they want, not what it’s called.

AI flips this. Plain sentences in, formula out. The gap that kept a whole generation of office workers dependent on “the Excel person” is mostly gone. What’s left is knowing how to describe your situation — and that’s a learnable, five-minute skill.

The template

This is what I give staff, verbatim. It works in any AI chat.

I need an Excel formula. Here is my table:

- Column A: employee name (e.g. "Kim")
- Column B: hire date (e.g. 2023-03-15)
- Column C: (empty — this is where the formula goes)

What I want, in plain words:
In column C, show how many full years this person has worked as of today.

My environment:
- Excel version: [2016 / 365 / Google Sheets]
- If my version doesn't support a function you want to use, give me an older alternative.

Please give me:
1. The formula for cell C2
2. One sentence explaining how it works
3. What to change if I add more rows

Three parts matter more than people expect.

The example values. “Column B: hire date” is ambiguous — text? A real date? 2023-03-15 or March 15, 2023? One example per column removes the single biggest source of wrong formulas. If your dates are secretly text (they often are, especially pasted data), the example is how the AI catches it.

What you want, in plain words. Not “I need a DATEDIF” — if you knew that, you wouldn’t be asking. Write the sentence you’d say to a coworker: “if the score is 90 or more show A, 80 or more show B, otherwise C.”

The version line. This one I learned from pain, and it’s the part every other guide skips. AI loves modern functions — XLOOKUP, TEXTSPLIT, FILTER. They’re genuinely better. They also don’t exist in the Excel 2016 running on half the office machines in the world, and they fail with an error message (#NAME?) that tells a beginner absolutely nothing. The sentence “if my version doesn’t support it, give me an older alternative” prevents the most demoralizing failure a first-timer can hit.

Three real requests

These are actual asks from our staff, lightly anonymized, in increasing order of “I thought this needed a program.”

Annual leave that expires. “Show the leave expiry date: one year after the grant date, minus one day.” The old workflow was a person calculating this by hand for the whole staff list, once a year, with mistakes. The formula (=EDATE(B2,12)-1) took the AI one attempt. The person’s reaction wasn’t “thanks” — it was “wait, that’s allowed?” That reaction is the whole point.

Grade bands. “90+ is A, 80+ is B, 70+ is C, below that is D.” This is the classic nested-IF wall where most self-taught Excel journeys end. AI produces it instantly, and — if you ask for the one-line explanation — teaches the pattern at the same time. Several people who started here were writing their own IFs a month later.

Pull the department from the master sheet. “Sheet2 has everyone’s name and department. On Sheet1, when I type a name, fill in their department automatically.” This was the request behind the five-hundred-retypes report. It’s a lookup — the exact kind of thing whose name you can’t guess. She described it in one Korean sentence, got a VLOOKUP with an explanation, and her monthly report went from an afternoon to a minute. She did not learn to “use Excel better.” She learned she could ask.

The four quiet failure modes

AI formulas fail in predictable ways. Knowing these four covers nearly everything I’ve seen go wrong:

1. Functions your version doesn’t have. Covered by the version line above. If you see #NAME?, this is almost always what happened — say so to the AI and it will downgrade gracefully.
2. Dates that are secretly text. The formula is correct, the result is nonsense or an error. Fix: include real example values in the prompt, and if things act weird, tell the AI “the date might be stored as text” — there’s a standard trick for that too.
3. Blanks and edge rows. A formula that works on row 2 divides by zero on the intern who started yesterday. My favorite follow-up prompt, always: “What breaks if some cells are empty? Make it safe for that.” One sentence, catches the majority of real-world surprises.
4. Locale details. Non-US Excel sometimes wants ; instead of , between arguments, and date formats differ. If a pasted formula errors immediately without even running, mention your language settings.

And one rule that isn’t about formulas at all: test on a copy of the file. Not because AI formulas are dangerous — because confidence grows faster when a mistake costs nothing.

The actual deliverable

Here’s what changed at our hospital, and it wasn’t the spreadsheets.

The first time someone gets a working formula out of a plain-language sentence, something clicks that has nothing to do with Excel: the wall between “what I want” and “what the computer does” is thinner than they were told. The department clerk who asked about lookups later asked about pivot tables. The one who asked about leave dates started asking me what else AI could do — and that question is the seed every internal tool I’ve built grew from.

You don’t hand someone a formula. You hand them the discovery that they were never actually bad at this. The formula is just the receipt.

I’m a physical therapist, not a developer — I’ve built 20+ internal tools for my hospital with AI. This blog is the honest logbook of how. If a spreadsheet is where your journey starts too, you’re in exactly the right place.

Comments

Loading comments…

Post Comment