The short answer
AI is genuinely useful in Excel, but not in the way most people expect. It is very good at writing a formula you describe in plain English, at explaining a formula somebody else built years ago, at cleaning up messy data, and at suggesting what is worth looking at in a table you have been staring at for an hour. Those four things cover most of what slows people down in spreadsheets.
What it is bad at is arithmetic. That sounds absurd for a computer, but it is the single most important thing to understand here, and it changes how you should use it. The safe pattern, the one that never bites you, is this: get the formula from the AI, and let Excel do the sum.
Why a chatbot cannot be trusted to add up
A language model does not calculate. It predicts text. When you paste a column of ninety numbers into a chat window and ask for the total, it is not running a calculator, it is producing the most plausible-looking answer based on patterns in text it has seen. Very often that answer is right. Sometimes it is slightly wrong. And it will be delivered with exactly the same calm confidence either way, which is what makes it dangerous.
Never take a number that came out of a chat window and put it in a document that matters. If a figure was produced by the AI rather than by a formula in your spreadsheet, treat it as an unverified guess. Ask for the formula instead, paste it into Excel, and let the spreadsheet do the maths.
Some AI features are now built directly into spreadsheet tools, and those can be a different story, because the tool may genuinely be running your formula rather than predicting text. But you cannot tell by looking, and it changes from product to product and month to month. The rule that survives every update is the same one: if you can see the formula and the cells it refers to, you can check it. If you can only see the answer, you cannot.
In plain English
- Language model:
- The technology behind AI chatbots. It generates text by predicting what should come next, which is why it can write a perfect formula and still get a total wrong.
- Hallucination:
- When AI states something false with complete confidence. In a spreadsheet this usually shows up as a plausible but incorrect number, or a function that does not actually exist.
Describing a formula instead of remembering it
This is where most people get the biggest immediate win. You no longer need to remember whether the lookup argument comes before or after the range, or which function handles the case where nothing is found. You describe the outcome you want, in ordinary words, and ask for the formula.
The trick is to describe your actual sheet, not a generic one. Say which columns hold what, and say what should happen in the awkward cases.
Write me an Excel formula.
My sheet: column A is order date, column B is customer name, column C is order value in pounds, column D is region. Data runs from row 2 to row 840. There is a header row.
I want, in cell G2: the total value of orders from the North region placed in the last 90 days.
Give me the formula, then explain in one sentence what each part does. If there is a simpler alternative, show that too. Tell me what happens if a cell in column C is blank.
That last line is worth keeping in every formula prompt. Blank cells, text where a number should be, and dates stored as text are the three things that quietly break spreadsheets, and asking about them up front saves you an afternoon.
Explaining a formula somebody else wrote
Most of us have inherited a workbook from a colleague who left. Somewhere in it is a formula three lines long, wrapped in four layers of brackets, that nobody dares touch. This is one of the safest and most useful things you can hand to an AI, because you are asking it to read rather than to calculate.
Paste the formula and ask for it in plain English, working from the inside out. Then ask the follow-up question that actually matters: what would make this break, and what is it silently assuming? A good answer will point out things like an assumption that the data is sorted, or a range that stops at row 500 when the sheet now runs to row 900.
Cleaning up messy data
Real spreadsheets are messy in predictable ways, and AI handles all of them well because they are pattern problems rather than sums.
| The mess | What to ask for |
|---|---|
| Dates in five different formats | A formula or a set of steps to convert everything to one consistent date format |
| Names as "SMITH, JANE" and "Jane Smith" | A formula to split into first and last name, then rebuild in one consistent style |
| Trailing spaces you cannot see | A formula to strip leading, trailing and double spaces |
| Duplicates that are not quite identical | A method to flag likely duplicates, plus what counts as a match |
| Numbers stored as text | How to spot them and convert them safely |
Do the cleaning on a copy, in new columns, next to the original data. Never overwrite the raw column. If the logic turns out to be wrong, you want the original still sitting there.
Pivot tables are the other classic sticking point. Rather than asking for the clicks, which change between versions and platforms, describe the question you are trying to answer and ask which fields belong in rows, which in columns, and what should be summarised. That gives you the logic, which is the part that is actually hard, and it stays true whichever version you are running.
Being specific is most of the skill
โ Weak prompt
Prompt
Help me with this spreadsheet, it is a mess and I need to analyse the sales data.
Output
A general explanation of sales analysis, a suggestion to use a pivot table, and three formulas that refer to columns you do not have.
The AI cannot see your file. It does not know your column names, your row count, your date format or what question you are trying to answer, so it answers the average version of your question.
โ Good prompt
Prompt
My sheet has these columns: A order date, B salesperson, C product, D units, E value in pounds. Rows 2 to 1,200. I need to know which salesperson has the highest average order value, ignoring any row where value is blank or zero. Give me a formula, and tell me how to check the result is right.
Output
A working formula that uses your actual column letters, a note on how blanks are handled, and a suggested spot check you can do manually on a few rows.
Column names, row range, the exact question and the awkward cases are all stated. There is almost nothing left to guess.
The extra thirty seconds of typing is the whole difference. If you find yourself doing this often, keep a short description of your standard sheet layout somewhere you can paste it in each time.
Drafting the sentence that explains the chart
Once you have a number, you usually have to explain it to somebody. This is text, not arithmetic, so it plays to the tool's actual strengths. Give it the figures you have verified and ask for the sentence.
Here are the figures from my report, which I have already checked: [paste the small summary table, not the raw data].
Write three sentences a non-financial manager could understand, explaining what changed and why it might matter.
Do not add any numbers I have not given you. Do not speculate about causes. If something looks unusual, say so as a question rather than a conclusion.
The instruction not to speculate matters. Left to itself, the AI will supply a confident explanation for your dip in March, and it has no idea what happened in March.
Before you paste, think about what is in the file
Spreadsheets are where organisations keep their most sensitive material: customer lists, staff salaries, contact details, unreleased results. Pasting a chunk of that into a general public chatbot may breach your workplace policy, your client contracts or data protection rules, and it is not the kind of mistake you can take back.
Check what your organisation allows before it becomes a habit. Where possible, work with a small anonymised sample instead of the real file, or describe the structure of the data rather than pasting the data itself. Most of the prompts above work perfectly well with fake names and rounded numbers, because you are asking for the formula, not the answer.
Where to go next
If you want to work through this properly rather than picking it up in fragments, our free AI for Spreadsheets and Data course covers formulas, cleaning, analysis and the checking habits that keep you out of trouble. It is the natural next step from this post.
Two others pair well with it. Prompt Engineering is the underlying skill of writing a brief the tool can follow, which is most of what separates a useful formula from a wrong one. And AI Productivity at Work covers the rest of the week: the email, the reports and the meeting notes that surround the spreadsheet. All free, no account needed.