How to Use AI for Spreadsheets and Data Cleaning: A Small Business Guide

Every small business has one: the spreadsheet nobody fully trusts. The customer list with three spellings of the same company. The expenses export where half the dates are American and half are British. The product catalogue that someone maintained carefully until 2024 and then stopped. You know the numbers are roughly right, but you would not put them in front of your accountant or your bank without a long afternoon of tidying first.

That afternoon is exactly the kind of work AI is good at — and exactly the kind most owners have not thought to hand over. This guide covers a repeatable five-step cleaning workflow, the prompts that produce usable output, how to get formula help without learning Excel properly, and the hard limits around confidential data.

Why your data is messier than you think

Data quality problems in small businesses are rarely dramatic. They accumulate. A sales rep types "Ltd" one week and "Limited" the next. An integration silently drops a field. Someone opens a CSV in Excel and Excel helpfully converts a product code like 05-2026 into a date. Nobody notices for eight months.

The cost is not the mess itself — it is the decisions you do not make because of it. Owners describe it almost identically: "I know the data is in there somewhere, I just do not trust it enough to act on it." So the quarterly review gets done on gut feel and the stock reorder is a guess. A dataset you do not trust is functionally a dataset you do not have.

Three patterns cause most of the damage in SMB spreadsheets:

• Inconsistent entities. The same customer, supplier, or product appearing under several names, so every count and total is quietly wrong.
• Mixed formats in one column. Dates, currencies, phone numbers, and postcodes entered five different ways, which breaks sorting, filtering, and every formula you build on top.
• Structure that is not really a table. Merged cells, blank spacer rows, subtotals mixed in with data, and notes typed into the margin. Humans read it fine; no tool can.

What AI is good at — and what it is not

Being clear about this up front will save you a lot of wasted effort. AI assistants are genuinely strong at the judgement-heavy parts of data cleaning: spotting that "Acme Ltd", "ACME Limited", and "Acme Ltd." are one company, inferring that a column of 1,200 values is meant to be dates even though it is stored as text, suggesting a standard format, and writing the formula or script that applies the fix.

They are weaker — and more dangerous — at arithmetic performed silently in their own head on a large pasted block. If you paste 800 rows of numbers into a chat window and ask for a total, you may well get a confident, wrong answer. The fix is a rule worth writing on a sticky note: let the AI decide what the rule should be, and let the spreadsheet apply it.

Use AI to design the cleaning rule. Use Excel, Google Sheets, or a script to execute it. Never ask a chat window to be your calculator on a dataset you cannot eyeball.

Most modern assistants — ChatGPT, Claude, Copilot, Gemini — also offer file upload with a code-execution sandbox. When the AI writes and runs Python over your file rather than reasoning about numbers in prose, accuracy rises sharply: the arithmetic is computed, not predicted. Use that mode for anything over a few dozen rows.

The five-step data cleaning workflow

This is the sequence to run on any messy sheet. It takes roughly 30 to 60 minutes the first time and about 10 minutes once you have done it twice.

1. Make a copy and freeze the original

Duplicate the file and name the copy something like customers-working-2026-10-15. Every step below is destructive. If you discover in step four that your date assumption was wrong, you need the untouched original to go back to.

2. Profile the sheet before you change anything

Upload or paste the first 30 rows plus headers and ask the AI to describe what it sees — not to fix it yet. You want an inventory of problems: which columns have mixed formats, which have blanks, which hold two things at once. This step alone usually surfaces two or three issues you did not know about.

3. Agree the target format, column by column

Decide explicitly what "clean" means for each field before touching the data. Dates as YYYY-MM-DD. Currency as a plain number with no symbol, euros only. Phone numbers in international format. Company names with legal suffixes stripped into their own column. Write the list down — this becomes your data standard, and you will reuse it every time.

4. Fix structure first, then values

Unmerge cells, delete spacer rows, move subtotals out, split any column holding two pieces of information, and make sure row one is a single clean header row. Only then start standardising the values inside. Doing it the other way round means redoing half the work.

5. Spot-check 20 rows by hand

Pick 20 rows at random — including a few you know are awkward — and compare them against the frozen original. This is the step people skip, and the one that catches the quiet disasters: a find-and-replace that matched more than you intended, or 40 dates silently shifted by a month. Five minutes here is the difference between a clean dataset and a confidently wrong one.

Prompts that actually work on spreadsheets

Vague prompts get vague output. These are specific enough to be useful — adapt the field names to your own sheet.

To profile a sheet: "Here are the headers and first 30 rows of a customer export. Do not change anything yet. List every data quality problem you can see, grouped by column: inconsistent formats, likely duplicates, blanks, values that look like they are in the wrong column, and anything that suggests a broken export. Be specific and quote examples."

To find duplicate entities: "This column contains company names. Group them into clusters that are probably the same organisation despite different spellings, punctuation, or legal suffixes. For each cluster, propose one canonical name. Flag any cluster you are less than confident about separately so I can review it myself."

To standardise a column: "This column contains dates entered inconsistently — some DD/MM/YYYY, some MM/DD/YYYY, some as text like '3rd March'. Write me an Excel formula that converts them all to YYYY-MM-DD, and tell me which rows are genuinely ambiguous and need a human decision."

To build a reusable cleaner: "Write a Python script using pandas that takes this CSV and applies the following rules: [your list from step three]. The script should write a new file rather than overwriting the input, and print a summary of how many values it changed per column."

Notice the pattern: a defined input, an explicit output format, and — crucially — an instruction to flag uncertainty rather than guess. That last clause is the highest-value thing you can add to a data prompt, because it converts a confident wrong answer into a short list of rows for you to check. The same discipline applies anywhere AI output feeds a decision, as covered in our guide to preventing AI hallucinations in client work.

Formulas, pivot tables and the "explain this sheet" trick

Cleaning is only half the value. The other half is that AI has effectively removed the barrier to intermediate spreadsheet skills.

Formulas on demand. Describe what you want in plain English — "I need the total revenue for each customer in column B, but only for rows where the status in column F is 'paid' and the date in column C is in the last 90 days" — and you will get a working formula plus an explanation of each argument. XLOOKUP, SUMIFS, and INDEX/MATCH stop being things you need to memorise.

The "explain this sheet" trick. Inherited a workbook nobody understands? Upload it and ask: "Explain what this spreadsheet does, what each tab is for, which cells are inputs versus calculated, and which formulas look fragile or likely to break." Owners regularly find hard-coded numbers buried inside formulas this way — the kind that quietly make a pricing model wrong for a year. It pairs well with the broader stocktake in our piece on auditing your AI tool stack.

Analysis you would not otherwise bother with. Once a sheet is clean, asking "what are the three most interesting patterns here and what would you check next" is a useful ten-minute exercise. Treat the output as hypotheses to verify, not findings to act on. If the data is financial, our guide to AI finance workflows for small teams covers where this fits in a monthly cycle.

What never to paste into an AI tool

Spreadsheets are where a business keeps its most sensitive data, which makes this the step to get right. The rule is simple: before pasting or uploading, ask yourself whether you would be comfortable emailing that file to a supplier you have never met.

• Never: payment card numbers, bank account details, national insurance or tax identification numbers, passwords, health information, or anything covered by a client confidentiality clause.
• Only on a business or enterprise plan with training switched off: customer names and contact details, employee records, supplier pricing, unreleased financials.
• Generally fine: anonymised or synthetic sample rows, aggregate figures, structural questions about a sheet with the values stripped out.

There is a practical trick that solves most of this. You rarely need the real data to get the cleaning rule. Replace the sensitive columns with dummy values, work out the formula or script on the fake data, then run the finished rule on the real file locally. You get the AI's judgement without the real data ever leaving your machine. Our guide to AI data security for small business goes through the consent and GDPR side of this in more detail.

How to make it stick

The one-off clean feels great and then decays, because the processes that created the mess are still running. Two things prevent the backslide.

First, write down the data standard you agreed in step three and put it where data gets entered — as a note on the form, a comment on the column header, a line in the onboarding checklist. Most inconsistency is not carelessness; it is the absence of a stated rule.

Second, schedule the clean. Put 30 minutes in the calendar monthly for your two or three most important sheets, run the five steps, and keep the prompts in a note so you are not rewriting them each time. The second run takes a fraction of the first.

Done consistently, this is one of the highest-return AI habits available to a small business. It needs no new subscription, no integration project and no technical hire — just a copy of the file, half an hour, and the discipline to spot-check twenty rows before you trust the result.