Ask most people how to use AI in Excel and you get the same story back. They asked a chatbot for a formula, pasted it in, got #VALUE!, and never tried again. Fair enough. The chatbot had never seen their data. It guessed at column letters and made up a sheet name.
The seven workflows below are the ones that survive a real workbook. Not a tidy demo file, but the kind you actually get sent: merged cells in the header, a date column that's secretly text, and a tab named Sheet1 (2) FINAL. For each one you get the prompt shape that works, what to check afterwards, and where it falls apart.
What AI is genuinely good at in a spreadsheet
There's a simple test for whether a task is safe to hand over. Can the sheet itself show you that the answer is wrong? If a mistake turns into a visibly odd number you'd catch on a spot check, you're fine. If it turns into a slightly-too-flattering figure that nobody questions until a board meeting, slow down.
| Task | How it goes | Why |
|---|---|---|
| Writing and fixing formulas | Very well | You find out instantly if it's wrong |
| Cleaning and standardising | Very well | Mechanical rules, visible results |
| Tagging and classifying rows | Well, if you audit it | Fast, but confident when wrong |
| Summarising and spotting patterns | Good first pass | Fine at noticing, you confirm the maths |
| Pricing, forecasts, headcount | Badly | It has no stake in the outcome |
| Facts you never gave it | Badly | It fills the gap rather than leaving one |
1. Write the formula you can never remember
This is where everyone starts, and it works fine once you stop guessing at the function and start describing the data. Two prompts for the same job:
Write a VLOOKUP for meIn Orders!A2:F5000, column B is a customer ID. Pull that customer's region from Customers!A:C, where A is the ID and C is the region. If there's no match, return "Unknown".The second version works because every decision the formula has to make was already made in the prompt. Ranges, key columns, what happens when nothing matches. An assistant that can read your workbook does better still, because it takes the real headers instead of the ones you approximated from memory.
2. Standardise a column five people typed into
Every operational sheet ends up with a column where one thing is written eight ways. USA, U.S.A., United States, us, Usa . The traditional fix is a lookup table you then maintain forever. AI handles the long tail in one pass, which is the whole appeal.
Look at the mess first
Ask for the distinct values before changing anything. Half the time you find categories nobody knew existed, which is worth the five minutes on its own.
Decide the vocabulary yourself
Hand over the exact list of values you'll accept. Skip this and you get a tidy column that still doesn't match what's in your CRM.
Map into a new column
Never overwrite the original. Put the clean value next to it so anyone can see what changed and undo it with one delete.
Flag what it couldn't place
Anything ambiguous should come back marked, not guessed. That short list is where your attention is worth something.
3. Pull apart text fields without touching regex
Free text is where spreadsheets go to die. Full names that need splitting. Addresses crammed into one cell. Product codes with meaning buried in characters four through seven. Perfect AI work, because the rule is obvious to a human and miserable to write out.
- Split
Full Nameinto first and last, keeping surnames like van der Berg together. - Pull the four-digit year out of
Ref, wherever it happens to sit in the string. - Turn a wide monthly layout into one row per product per month.
- Read
Notesinto a numeric column, ignoring currency symbols and thousands separators.
Ask for a formula rather than pasted values if the source data will keep growing. A formula recalculates when someone appends 400 rows next Tuesday. Pasted values quietly go stale and nobody notices for a month.
4. Tag thousands of rows you'd never tag by hand
Sorting 4,000 support tickets by theme. Putting transactions into expense categories. Marking which job titles are actually decision makers. This is the workflow with the biggest time difference, hours down to minutes, and it's also the one people get burned by, because output that's 71% right looks exactly like output that's 98% right.
Two things lift accuracy a lot. Give the categories rather than letting it invent a taxonomy, and allow an Unclear option. A model forced to choose will always choose. A model allowed to shrug hands you a short list of genuinely ambiguous rows instead, which is far more useful.
5. Interrogate the data before you build the chart
Before you make a dashboard, spend ten minutes asking questions in plain English. Which regions grew quarter on quarter. Which customers ordered once and vanished. Which SKUs go negative after discounts. You're not after a finished answer here, just the two or three things worth putting on a slide.
One rule keeps this honest: make it show the calculation. A number with no formula behind it is a claim, not a result. If the pivot or the SUMIFS lands in the sheet, you can trace it back. If all you get is "revenue grew 23%", you can't, and neither can the person who asks you about it on Thursday.
6. Build the monthly report once and re-run it
Most reporting time isn't analysis, it's the same twelve steps every month. Import, clean, join to last month, recalculate, format, chart. Do it once with AI writing the formulas, keep the workbook, and after that you only replace the raw data.
- 1Keep raw imports on their own tab and never edit them by hand.
- 2Put every transformation in formulas that point at that raw tab.
- 3Keep the formatted output on a third tab that reads from the calculation tab.
- 4Next month, paste the new export over the raw tab and let it all recalculate.
Raw, calculated, presented. It's the layout that turns a spreadsheet you rebuild into one you re-run, and AI just makes the middle layer much faster to write. If your imports keep breaking on the way in, the CSV import guide covers why.
7. Get it to explain someone else's workbook
Inheriting a workbook is nobody's favourite Monday. Pointing an assistant at a 200-character nested formula and asking what it does is one of the highest-value things you can do with AI, and it carries almost no risk, because you're reading rather than writing.
- Explain this formula in plain English, step by step.
- Which cells feed this total, and which numbers are hardcoded?
- What input would make this return an error?
- Are there ranges here that stop before the last row of data?
That last question catches the most common silent error in spreadsheets: a SUM covering rows 2 to 500 in a sheet that quietly grew to 800. It's the same mistake that derailed a famous economics paper, and it happens in ordinary companies every week.
Three things to keep away from it
Being straight about the limits matters more than being enthusiastic about the wins, so here they are.
Decisions you're accountable for
Forecast assumptions, pricing, headcount. A model can lay out the trade-offs perfectly well. It can't own the call, and dressing a guess up as a calculation only makes it harder to challenge later.
Facts that aren't in your data
Market sizes, tax rates, a competitor's pricing. If it isn't in the file, treat whatever comes back as a placeholder until you've sourced it properly. This is where confident wrong answers do the most damage.
Anything regulated
Revenue recognition, payroll, statutory reporting. Draft with AI by all means, then have the person who signs their name to it read the logic line by line.
A checklist you can actually use
- 1Describe the data before you describe the task.
- 2Say what should happen to blanks, duplicates, and rows with no match.
- 3Ask for formulas when the data will change, values when it won't.
- 4Write into new columns so every change is one delete away from gone.
- 5Spot-check a random sample of anything produced in bulk.
- 6Ask for the reasoning on anything that leaves your screen.
Try this on your own spreadsheet
SheetAI reads your actual workbook, real headers and real edge cases, writes the formulas into the sheet, and shows every step so you can check it rather than trust it.
Open SheetAI
