AI for spreadsheets: formulas, cleaning and analysis.
Spreadsheets are where office workers quietly lose the most hours - and where AI help is at its most concrete. Three jobs it does well, the prompts for each, and the checking habit that keeps the numbers honest.
Nobody is ever taught spreadsheets. You inherit a workbook from someone who left, add columns until it groans, and google "VLOOKUP not working" for the ninth time. AI helps here more than any feature Excel has shipped in a decade, because the hard part was never the maths - it was translating what you want into what the software understands.
How do you use AI for spreadsheets?
Paste your columns into a general assistant and use it for three jobs: writing and debugging formulas, cleaning messy data, and first-pass analysis. Describe the columns in plain English, ask it to explain what it wrote, and test on rows where you already know the answer before trusting the whole sheet.
AI helps with three distinct jobs: writing and untangling formulas, cleaning messy data, and first-pass analysis. You can do all three by pasting into a general assistant like ChatGPT or Claude. In-place help - an assistant that can see your workbook - is a licensing question, and the answer changed in April 2026: Copilot inside Excel, Word and PowerPoint now depends on your tenant's Copilot licence label. Some organisations keep standard access to Copilot in the apps without the add-on, others lost it entirely, and the full Microsoft 365 Copilot add-on runs around £23 per user per month at list. Ask IT which label you have. Without the in-app version you get Copilot Chat, which can analyse a workbook you upload into the chat but cannot reach the files sitting in your OneDrive or SharePoint on its own. Gemini is the opposite: bundled with Google Workspace Business Standard and above, and Enterprise, so Sheets users often do have in-place help.
One condition still catches people out: Copilot's editing features need Calculation Options set to Automatic. Data laid out as a clean table with named headers gets better answers, but is no longer a requirement. The prompts below work in either mode.
Job one: can AI write and debug your formulas?
Plain English to formula.
"I have a sheet where column A is the invoice date, column C is the amount and column E is the customer name. Write an Excel formula for: total amount for [customer] where the invoice date falls in [month]. Explain the formula step by step, and tell me what happens if a date cell is blank or a name is spelled inconsistently."
Three habits make this reliable, and they are the spreadsheet version of the briefing pattern behind every other AI task at work. Name your columns and what they contain - the assistant cannot see your sheet unless you are using an in-sheet tool. Ask for the explanation, so next month you are not back at square one - that is how spreadsheet skill compounds. And ask how it fails: blank cells, text where numbers should be, trailing spaces in names. It works in reverse: paste a monster formula you inherited and ask what it does, step by step, and where it could silently give a wrong answer. A formula that errors gets fixed; a formula that returns a plausible wrong number gets presented.
Job two: how do you clean messy data with AI?
The export from the finance system, the survey results, the supplier list: dates in three formats, "N/A" and blanks meaning the same thing, names in capitals, duplicates from a double upload. By hand it is slow; a worked example:
Before: "SMITH, john - 03/07/26 - £1,200.00" / "Jane Doe | 2026-07-04 | 950" / "smith J. 4 July - £1200 (paid?)"
The cleaning brief.
"Standardise the rows below into a table with columns: Name (Firstname Surname), Date (DD/MM/YYYY), Amount (number only), Notes. Dates are UK format (day first). If a date is ambiguous, flag it. Do not invent values - if part of a row is ambiguous, put it in Notes and flag the row with '[CHECK]'. Then list every assumption you made. Tell me how many rows went in and how many came out. Rows: [paste]"
The output: a clean three-row table, with the third row flagged because "smith J." might not be the same person as "SMITH, john", and "(paid?)" preserved in Notes rather than deleted. The "[CHECK]" flag and the assumptions list are the difference between cleaning and corrupting. Now look at the date. "03/07/26" is 3 July to you and 7 March to an assistant defaulting to American format, and a swapped date looks exactly like a correct one - the spreadsheet error that never announces itself, which is why the format instruction belongs in the brief. Then reconcile: rows in against rows out, minus anything you agreed to remove, and the amount column totalled both ways.
For a short list, this is the honest arithmetic: 40 minutes of hand-tidying becomes ten minutes of review - and the review is the job. Above about fifty rows, ask for the method instead: "give me the sequence of Excel steps or formulas to do this." Three hundred rows through a chat window come back with rows quietly dropped and merged; a method runs in the sheet, where you can audit and repeat it.
Job three: can AI analyse your spreadsheet data?
Interrogate the table.
"Here is a table of [what it is, and what one row represents]. 1) Summarise the overall picture in three sentences. 2) Which categories moved most versus the prior period, in absolute and percentage terms? 3) List outliers or anything that looks like a data error rather than a real change. 4) What three questions would a sceptical manager ask about this data? Table: [paste]"
Question 3 earns its keep - assistants spot the row that is ten times its neighbours - and question 4 prepares you for the meeting where the numbers get challenged. Treat what comes back as hypothesis, not finding: the assistant does not know that April was a short month unless you tell it. Context in, judgement out. For analysis with real stakes, start from a structured build like our 13-week cash-flow forecast guide rather than a free-form chat.
The checking habit that makes all three safe.
- Test formulas on known answers. Before filling a formula down 5,000 rows, run it on five rows where you can verify the result by eye or calculator.
- Reconcile after cleaning. Row counts and totals, in against out, exactly as in the worked example.
- Recompute the headline number. Any figure that will be repeated in a meeting gets recalculated in the sheet itself - a pivot table or a SUMIFS - not taken from the chat window.
And the data rule sits above all of it: spreadsheets are the densest confidential objects in the building - salaries, margins, customer lists. Paste into public tools only what is safe to share, build formulas against dummy rows, and use an approved tool for anything real. The full boundary is drawn in using AI safely at work.
Frequently asked questions
Can AI write Excel formulas for me?
Yes - describe what you want in plain English, name the columns and what they contain, and ask the assistant to explain the formula it writes so you understand it. Always test on a few rows where you already know the right answer before trusting it across the sheet.
What is the best AI for spreadsheets?
For most people, a general assistant working on pasted data: ChatGPT, Claude, Gemini and Copilot Chat all handle formulas, cleaning steps and first-pass analysis. In-place help is not automatic - whether Copilot works inside Excel now depends on your tenant's Microsoft 365 Copilot licence label, so ask IT which one you have, while Gemini comes bundled with Google Workspace Business Standard and above, and Enterprise. The bigger difference comes from how you brief and check, not which tool you pick.
Can AI analyse my spreadsheet data?
Yes, for first-pass analysis: summaries, comparisons, outliers and trends. Treat the output as a starting hypothesis, verify any number that will be repeated to others by checking it in the sheet itself, and remember that AI does not know your business context unless you supply it.
Is it safe to paste spreadsheet data into AI tools?
Only if the data is safe to share outside your organisation. Spreadsheets are dense with names, salaries, prices and customer details, so anonymise columns, use dummy rows to build formulas, or work inside an employer-approved tool such as a business Copilot or Gemini plan.
The spreadsheet rules.
- Describe columns and contents in every formula prompt; ask for the explanation and the failure modes.
- Cleaning briefs end with "do not invent values, flag ambiguity with [CHECK], list your assumptions".
- Reconcile every clean: row counts and totals against the source.
- Analysis output is hypothesis; recompute any number you will repeat, inside the sheet.
- Real data goes only into approved tools; build with dummy rows everywhere else.
Put AI to work in your own job.
AI at Work is Clerq's practical, hype-free guide to getting real work done with AI - written for people with inboxes, deadlines and meetings, not developers. PDF and EPUB, launching soon.