Updated November 14, 2026
Everyone has a spreadsheet they’re slightly afraid of. A nested IF formula someone wrote in 2019, a customer list with names in five different formats, a monthly report that takes a whole afternoon to rebuild.
This is where AI for Excel earns its keep. You can describe what you want in plain English and get a working formula, clean messy data in minutes, and ask questions of a table instead of building pivot tables by hand. You still need to check the numbers, and this guide shows how.
Key takeaways
- AI for Excel is best at writing formulas, explaining old formulas and cleaning messy data.
- Describe your columns and the result you want, and ask for an explanation.
- Test every formula on rows where you already know the answer.
- Keep sensitive spreadsheets inside approved tools.
Three ways to use AI for Excel
You have three options, and you can mix them.
The first is Copilot inside Excel, which comes with Microsoft 365 Copilot licences. It can add formula columns, build PivotTables and charts, and work through multi-step edits in your workbook. Microsoft has added a lot to it during 2026, so check what your licence includes.
The second is Gemini in Google Sheets, included with Google AI Pro and many Workspace plans. It helps with formulas, summaries and organising data inside Sheets.
The third is any general assistant, such as ChatGPT, Claude or Gemini, used alongside your spreadsheet. You describe the problem or upload a copy of the file, and it gives you formulas or analysis. This works with any version of Excel, including old ones.
Write formulas by describing them
This is the easiest win. Instead of searching forums for the right combination of INDEX and MATCH, describe the result:
“Write an Excel formula for cell E2. Column A has order dates, column C has region, column D has sales. I want total sales for the region in C2 for the same month as A2. Use XLOOKUP or SUMIFS, not array tricks. Explain each part.”
Tell it your column layout, your Excel version if it’s old, and ask for an explanation. The explanation is how you catch a formula that works on the example but breaks on row 500.
Understand formulas you inherited
Paste in that scary formula and ask: “Explain what this does, step by step, in plain English. Then suggest a simpler version that gives the same result.” This alone can save hours when you take over someone else’s workbook.
Clean messy data
AI for Excel is very good at the tedious cleanup jobs:
- Splitting full names into first and last name columns.
- Standardising dates written in different formats.
- Fixing inconsistent capitalisation and spelling of the same company name.
- Pulling postcodes or product codes out of free-text fields.
- Flagging likely duplicates that aren’t exact matches.
For a few hundred rows, ask the AI for formulas or Power Query steps so the cleanup is repeatable. Repeatable beats a one-time fix, because next month’s export will be just as messy.
Ask questions of your data
Upload a copy of your table to an assistant that can run code, or use Copilot or Gemini inside the sheet, and ask questions such as “Which regions grew fastest quarter on quarter?” or “Show me the top 10 customers by margin, not revenue.” Ask it to show the calculation or the formula it used. If it can’t show how it got a number, don’t use that number.
How to check AI spreadsheet work
- Test formulas on a few rows where you know the right answer.
- Check the edges: blank cells, zeros, the first and last rows, dates at month end.
- Compare one total the AI produced against a manual SUM or pivot table.
- Read the explanation. If it doesn’t match what you asked for, the formula probably doesn’t either.
AI makes confident maths mistakes, especially with ranges and filters. Our guide on how to check AI output for errors has more checks you can use.
Keep company data safe
Spreadsheets often hold the most sensitive data in a company: salaries, customer lists, financial forecasts. Don’t upload them to a personal AI account. Use Copilot or Gemini under your company licence, or strip out sensitive columns and use fake names before sharing. Our guide to AI privacy at work explains what to look for.
AI for Excel prompts to save
- “Write a formula that [result]. Data is in [columns]. Explain it.”
- “Explain this formula step by step and suggest a simpler version.”
- “Give me Power Query steps to clean this column so it’s repeatable.”
- “What are the three most interesting patterns in this table? Show the numbers.”
- “Build a monthly summary table from this data and tell me which formula each cell uses.”
More prompts like these are in our list of AI prompts for work. If you turn spreadsheet findings into slides, see how to make presentations with AI. And if you’d like a monthly report to build itself, our guide to automating repetitive tasks with AI and our n8n Google Sheets AI workflow show how.
This guide is part of our complete guide to using AI at work.
Quick FAQ
Do I need Copilot to use AI for Excel?
No. Copilot is the most convenient option inside Excel, but any AI assistant can write and explain formulas if you describe your data clearly.
Can AI analyse my whole spreadsheet?
Yes, within limits. Assistants that can run code handle large tables well. Messy layouts with merged cells and several tables on one sheet confuse them, so clean the structure first.
Is AI accurate with numbers?
It’s good at writing formulas and weaker at doing arithmetic in its head. Ask for formulas or code you can check, rather than final numbers alone.
