AI for spreadsheets: formulas, tidy data and quick analysis
Get formulas written and explained, fix errors, tidy messy data and analyse a whole file in minutes. Then check the results, because a wrong number looks just like a right one.
Video transcript
Formulas, error messages and messy data: AI can help with all of them, as long as you check its work. Here’s how.
Describe what’s in each column and what you want to work out. Then ask for the formula with a one-line explanation, so you can follow its logic.
It’s handy for the fiddly jobs too. It can explain an error message, split names into two columns, sort out dates in different formats and find duplicate rows.
For bigger questions, export your sheet as a CSV file and upload it to an assistant that can run code. Ask in plain English, and ask for a chart if it helps.
Here’s the catch. A wrong answer doesn’t look wrong. It’s still a tidy number, so check a few rows you can work out by hand.
And take out personal data before you upload anything: names, email addresses, salaries and customer details. The analysis usually works just as well without them.
Copy the prompts from the full guide below, and try one on the next spreadsheet that has you stuck.
In 30 seconds
- Describe your columns in plain English and ask for the formula with a one-line explanation.
- It can also fix errors, tidy messy data and analyse a whole exported file.
- Check results on a few rows by hand, and remove personal data before you upload.
The answer is in the spreadsheet somewhere. You just can’t remember whether you need a VLOOKUP, a SUMIF or something with brackets inside brackets. This is where AI earns its keep.
Get formulas written and explained
Describe what you want in plain English, as you would in any good prompt. Say which columns hold what and whether you use Excel or Google Sheets, then ask for the Jargon busterFormula: An instruction in a spreadsheet cell that works something out, such as a total. It starts with an equals sign, like =SUM(A1:A10). with a short explanation, so you can follow its logic.
You: I use Excel. Column B is the region, column C the product and column D the sales value. I want total sales for the region in G1 and the product in G2.
AI: Put this in G3: =SUMIFS(D:D, B:B, G1, C:C, G2). It adds up column D on every row where column B matches G1 and column C matches G2, so the total updates when you change either cell.
Lookups work the same way, such as pulling a price from another sheet using a product code. You can also paste in a formula you’ve inherited and ask what each part does.
Got an error such as #N/A? Paste the formula and the error, and describe what’s in the cells. In a lookup, #N/A usually means there’s no match, often because of a stray space or a number stored as text.
Tidy messy data
- Split namesTurn “Jane Smith” in one column into first and last names in two.
- Fix datesMake 03/04/2025, 3 April 2025 and 2025-04-03 into one format your sheet can sort.
- Remove duplicatesFind rows that appear twice, including near-misses such as “Ltd” and “Limited”.
- Make text consistentFix spellings, capitals and stray spaces, so “N Ireland” and “Northern Ireland” count as one.
Ask for a formula or step-by-step instructions rather than letting AI retype your data, so you can see exactly what changed. And try it on a copy of the sheet first.
In [Excel or Google Sheets], column A has full names in mixed formats, like these made-up examples: [two or three examples in the same format as your data]. Give me formulas to split them into first names in column B and last names in column C. Then tell me which kinds of name they’ll get wrong, such as middle names or double-barrelled surnames.
Analyse a whole file
For bigger questions, export the sheet as a Jargon busterCSV: A simple spreadsheet file: plain text, with values separated by commas. and upload it to an assistant that can run code. Instead of estimating, it writes and runs a short program on your data, so the totals are calculated rather than guessed.
General assistants such as ChatGPT, Claude and Gemini can read uploaded files, and your spreadsheet may have one built in, such as Microsoft Copilot in Excel.
Then ask in plain English, such as “Which region grew fastest this year?” Ask for a chart too, and say what it’s for: “a bar chart of monthly sales I can paste into a report”.
I’ve uploaded a CSV of [what the data is, such as monthly sales by region]. First, describe the columns and flag any problems, such as blanks, duplicates or odd dates. Then answer this: [your question]. Show your working, make a simple bar chart if it helps, and give me three rows I can check by hand.
Check it before you trust it
A wrong formula still gives you a number, and it looks just as convincing as a right one. Check the results on a few rows you can work out by hand, and compare totals with figures you already know.
Remove personal data before you upload anything: names, email addresses, salaries and customer details. You can often delete those columns, or swap names for ID numbers, and the analysis works just as well. There’s more in using AI at work safely.
Check yourself
3 quick questions nothing is savedTools in this guide
- MMicrosoft CopilotMicrosoft’s assistant across Windows, Edge and Microsoft 365 apps.
- GGeminiGoogle’s assistant, built into Gmail, Docs and Android.
- CChatGPTOpenAI’s general-purpose assistant for writing, questions, analysis and images.
- CClaudeAnthropic’s assistant, strong at long documents, careful writing and code.
Spotted a mistake? Tell us and an editor will check it.
