To clean a customer list in Excel with Copilot, save a backup, format the list as a table, run Data, Clean Data to fix extra spaces, inconsistent text and numbers stored as text, then ask Copilot to standardise names and phone numbers and flag duplicates. Compare before-and-after counts of rows, spaces and totals before deleting anything.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowClean Data does less than its name suggests. Microsoft lists three kinds of suggestion: inconsistent text (case, punctuation, accents), numbers stored as text, and extra spaces. It does not find duplicate customers, fix name capitalisation as a rule, or decide which of two records to keep. Those jobs fall to Excel's own tools and to prompts in the Copilot pane, and the order matters: clean first, because stray spaces and case differences hide duplicates.
The example is an illustrative gift shop whose customer list was stitched together from its online shop export, in-store sign-up cards and a list from a Christmas fair: 1,862 rows before cleaning. For cleaning a whole CRM rather than one list, see how to clean up a messy CRM with AI.
What do you need before cleaning a customer list with Copilot?
Cleaning a customer list with Copilot needs an eligible Microsoft 365 licence, Excel on Windows or the web for Clean Data, a .xlsx file saved in OneDrive or SharePoint, and calculation set to Automatic.
Microsoft's Copilot in Excel FAQ lists the licences that include Copilot in Excel:
- A Microsoft 365 Personal or Family subscription with an AI credits plan.
- A Microsoft 365 Premium subscription.
- A commercial Microsoft Copilot subscription, such as Microsoft 365 Copilot Business: $21 a user a month on an annual commitment, or $18 for the first year on offers taken between 1 July and 31 December 2026, according to Microsoft's pricing page.
- A Copilot Chat-eligible Microsoft 365 or Office 365 business or enterprise subscription, which gives standard rather than priority access (the Copilot pane labels it M365 Copilot (Basic) rather than Premium).
Clean Data itself has narrower availability. Microsoft says it is in Excel for the web and Windows, available to Copilot customers, and arriving gradually, so it may not be in every region yet. If the button is missing from the Data tab on Windows, try the same file in Excel for the web.
Four settings save a lot of confusion later:
- Save the file in OneDrive or SharePoint. Version history, your safety net, only works there.
- Use the .xlsx format. Microsoft's FAQ notes that Copilot reports an "Unsupported file state" for formats such as Strict Open XML Spreadsheet.
- Set Formulas, Calculation Options to Automatic. Microsoft says Copilot only edits when calculation is automatic.
- Format the list as a table (click inside it and press Ctrl+T, or Home, Format as Table). Microsoft says Clean Data works best on tables and on data up to 100 columns and 50,000 rows.
One premise to clear up: older guides clean text with the =COPILOT worksheet function. Microsoft retired that function on 14 September 2026 (message centre notice MC1454373) and points to the Copilot side pane instead, which is what this page uses. The background is in what to use now the Excel COPILOT function is retired.
What does a messy customer list look like before cleaning?
A messy customer list typically has stray spaces, mixed capitals, numbers stored as text, several spellings of the same category, inconsistent phone formats and the same customer more than once under slightly different details.
Six rows from the gift shop's list, as they arrived. First names and contact details are placeholders; the surnames show common capitalisation patterns:
| First name | Last name | Phone | Source | Consent | Total spend | |
|---|---|---|---|---|---|---|
| [FIRST NAME] | MCALLISTER | " c1042@example.com" | 0555 010 123 | Online Shop | Yes | 142.50 |
| [first name] | McAllister | C1042@example.com | 0555010123 | till sign-up | Y | "38.00" (text) |
| [First name] | O'Neill | c2210@example.com | (0555) 010 124 | online shop. | yes | 96.00 |
| [First name] | O'Neill | c2210@example.com | (0555) 010 124 | online shop. | yes | 96.00 |
| [First name] | van der Berg | c3305@example.com | 555 010 125 | Christmas fair | "22.00" (text) | |
| [First name] | Hale | c4417@example.com | 0555 010 126 | Online shop | No | 310.00 |
Every problem in the list is there in miniature: a leading space in one email and a trailing space in another, customer C1042 twice with different capitals, C2210 twice exactly, a double space inside "van der Berg", four ways of writing "Online shop", three ways of writing yes, a blank consent, and spend figures stored as text that a SUM would ignore. Before touching anything, the shop counted what it had, so it could prove the clean-up worked:
| Measure | Before |
|---|---|
| Rows | 1,862 |
| Emails with extra spaces | 231 |
| Spend cells stored as text | 96 |
| Total spend counted by SUM | $184,210 |
| Different spellings in Source | 7 (for 3 real sources) |
| Different values in Consent | 6, plus blanks |
The formulas behind those counts are in the section on proving the list is cleaner.
How do you set undo points before Copilot changes anything?
Set undo points by copying the raw list to its own sheet, saving the file in OneDrive or SharePoint, and saving a version after each stage, so any step can be reversed.
- Undo point 1, the raw copy. Right-click the sheet tab, Move or Copy, tick Create a copy, and rename the copy "Raw 22 Nov". Nobody edits it. Microsoft's own advice for Remove Duplicates is to copy the original data to another worksheet first, because that tool deletes permanently.
- Undo point 2, after Clean Data. Save, then note the time. In version history (select the file's title, then Version history) you can open and restore that version later.
- Undo point 3, after names, phones and consent. Same again.
- Undo point 4, after duplicates. The last restore point before the list goes back into use.
Between undo points, Ctrl+Z still works for the last few actions; Microsoft's Copilot FAQ says you can undo Copilot's changes or view prior versions. Two more habits help. Put transformed values in a helper column first (Name clean next to Name), check them, then replace the originals. And remember that saved Copilot changes are visible to anyone with access to the file, including people editing it at the same time, so clean a copy rather than the list the shop's till exports into.
How do you run Clean Data, and which suggestions should you accept?
Run Clean Data from the Data tab with a cell in the table selected, then accept each suggestion card only where the change is clearly right for that column.
- Click any cell in the table.
- Select Data, then Clean Data. Excel may also show a banner offering to fix the issues it detected.
- Read each suggestion card in the side pane. Each covers one kind of issue.
- Apply the cards that are right; skip the rest. Check a few changed cells after each.
- Save. That is undo point 2.
The gift shop's illustrative suggestions, and what it did with each:
| Suggestion | Column | Example | Decision |
|---|---|---|---|
| Extra spaces | " c1042@example.com" | Accept: 231 cells | |
| Extra spaces | First and Last name | "van der Berg" | Accept: 129 cells |
| Inconsistent text | Source | "Online Shop", "online shop.", "Online shop" | Accept, choosing "Online shop" |
| Inconsistent text | Consent | "Yes", "yes", "YES" | Accept, choosing "Yes" ("Y" is not a case difference, so it stays for later) |
| Number format | Total spend | "38.00" stored as text | Accept: 96 cells become numbers |
| Number format | Phone | 0555010123 stored as text | Skip: phone numbers must stay text |
The last row is the trap. A phone column usually mixes cells typed as numbers with cells stored as text, so it looks like a number-format problem. Converting phone numbers to numbers strips the leading zero (0555010123 becomes 555010123), and the damage is silent until someone tries to call. Skip any number-format suggestion on phone numbers, account numbers, codes or IDs.
Microsoft says Clean Data performs best in English for now and that the first release "might not behave as you anticipate", so expect to skip some suggestions and to miss others. Anything it misses is handled in the next step.
Which Copilot prompts handle names, phone numbers and consent?
Copilot prompts handle what Clean Data leaves: capitalising names with exceptions, putting phone numbers in one pattern, mapping consent values, and listing anything it could not fix. Use chat mode to diagnose first, then edit mode.
Open Copilot from its icon at the lower right of Excel. The pane offers three modes: allow editing (the default), plan, and chat only, which analyses without changing the workbook. Start in chat only:
Without changing anything, check Table1 and list:
1. Names not in normal capitalisation (all capitals or all lower case)
2. The different phone number patterns, with a count of each
3. Every distinct value in Consent, with counts
4. Anything else that looks inconsistent
Give counts, not just examples.
An illustrative reply, after Clean Data had run:
304 names are all capitals or all lower case. Phone numbers follow 4 patterns: 0555 010 123 (1,188), 0555010123 (402), (0555) 010 124 (208), 555 010 125 (64; leading zero missing). Consent: Yes 1,204; No 509; Y 57; N 28; blank 64. Two rows have an email with no @.
Useful, with two things to fix before acting. The 64 phone numbers with no leading zero need checking against the source, not repairing by formula, because the missing digit might not be a zero for every row. And the two emails without an @ are typos only a person can correct. Then switch to edit mode, one job per prompt:
In a new column next to Last name called "Last name clean", put each last name
in normal capitalisation, with two exceptions: names starting Mc or Mac keep a
capital after the prefix (McAllister, MacLeod), and van, von, de and der stay
lower case as separate words. Don't change First name yet.
The exceptions matter. Excel's PROPER function capitalises the first letter and any letter that follows a non-letter, and lower-cases the rest, so on its own it turns "MCALLISTER" into "Mcallister" and "van der Berg" into "Van Der Berg" (it does handle "O'NEILL" correctly, because the apostrophe counts as a non-letter). Check twenty rows of the new column, especially the exceptions, before replacing the originals. Then the phones and consent:
In a new text column "Phone clean", write each phone number as digits only,
keeping it as text so leading zeros stay. Don't fill in missing digits; mark those
rows NEEDS CHECK. In Consent, change Y to Yes and N to No. Leave blanks blank
and highlight them yellow.
Blank consent deserves its own rule. The shop treats a blank as no permission to send marketing until the customer confirms, which is the cautious reading; it does not let a formula or Copilot fill blanks with Yes. If you are unsure what your consent records need to show, ask whoever advises you on data protection rather than deciding in a spreadsheet.
How do you find and remove duplicate customers safely?
Remove duplicate customers in three passes: Data, Remove Duplicates for exact copies, a Copilot plan for rows sharing an email, and a reviewed list for near matches. Merge spend into the kept row rather than deleting it.
Pass 1: exact duplicates
Before deleting, look: Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values shows what you are about to lose. Then select the table and use Data, Remove Duplicates with every column ticked, so only rows identical in every field go. The gift shop lost 46 rows here, all double imports from the online shop, like C2210's. Microsoft notes that the counts Excel reports after removal may include empty cells and spaces, which is another reason Clean Data runs first.
Pass 2: same email, different details
Remove Duplicates cannot handle customer C1042, whose two rows differ in capitals, phone format and source. Use plan mode so you can read what Copilot intends before it edits:
Find rows in Table1 whose Email matches once trimmed and lower-cased.
For each group: keep the row with the latest Last order date; add the Total spend
of the other rows to the kept row; copy a phone number into the kept row only if
it has none. Move the other rows to a new sheet called "Merged rows" with a note
of which row they merged into. Show me the plan and the number of groups first.
The illustrative plan found 57 groups, each of two rows, and listed the steps; the shop approved it and checked five groups by hand afterwards. Moving rows to a "Merged rows" sheet instead of deleting them keeps a record of what happened. Adding spend matters too: customer C1042's $38.00 at the till and $142.50 online were different purchases, and deleting the till row would have understated that customer's spend by a fifth.
Pass 3: near matches for a person to decide
Some duplicates share a phone number and name but have different emails (a work address and a personal one). Ask Copilot, in chat only mode, to "list pairs of rows with the same Phone clean and Last name clean but different emails". The shop got 14 pairs, confirmed 9 as the same person and merged those by hand, and left 5 (family members sharing a landline) alone. For combining two lists before this step, merging two Excel files with Copilot covers the lookup side.
What should you review after each Copilot step?
After each Copilot step, review a sample of changed cells, the rows Copilot says it could not fix, and one count that should have moved, before saving the next undo point.
The gift shop's review sheet, filled in from its first run:
| Stage | What to look at | Gift shop result |
|---|---|---|
| Clean Data: spaces | Ten changed emails; the extra-spaces count | 231 to 0; no emails broken |
| Clean Data: text | Distinct values in Source and Consent | Source 7 to 3; Consent still had Y and N, as expected |
| Clean Data: numbers | Spend column total; any cell still text | 96 text cells to 0; SUM rose by $7,320 |
| Names | Every Mc, Mac, van and de row, plus twenty at random | 3 corrected by hand (a double-barrelled surname) |
| Phones | The NEEDS CHECK rows; ten random numbers | 64 flagged; 51 fixed from order records, 13 left blank |
| Consent | Count of Yes, No and blank | Blanks highlighted, none filled |
| Duplicates | Five merged groups by hand; the Merged rows sheet | Spend added correctly in all five |
Here is what the six sample rows from earlier became. Four rows remain: C1042's two records merged into one with the latest order date and combined spend, C2210's exact copy gone, and the rest tidied:
| Last name | Phone | Source | Consent | Total spend | |
|---|---|---|---|---|---|
| McAllister | c1042@example.com | 0555010123 | Online shop | Yes | 180.50 |
| O'Neill | c2210@example.com | 0555010124 | Online shop | Yes | 96.00 |
| van der Berg | c3305@example.com | NEEDS CHECK | Christmas fair | (blank, highlighted) | 22.00 |
| Hale | c4417@example.com | 0555010126 | Online shop | No | 310.00 |
The merged row keeps "Online shop" as its source because the online record had the latest order. If where a customer first came from matters to your marketing, add a First source column before merging rather than losing it.
How do you prove the customer list is cleaner?
Prove a customer list is cleaner with before-and-after counts built from formulas (rows, extra spaces, numbers stored as text, distinct spellings and the spend total) and a reason for every change.
| Measure | Formula | Before | After |
|---|---|---|---|
| Rows | =ROWS(Table1) | 1,862 | 1,750 |
| Emails with extra spaces | =SUMPRODUCT(--(LEN(Table1[Email])<>LEN(TRIM(Table1[Email])))) | 231 | 0 |
| Spend cells stored as text | =SUMPRODUCT(--ISTEXT(Table1[Total spend])) | 96 | 0 |
| Spellings in Source | =COUNTA(UNIQUE(Table1[Source])) | 7 | 3 |
| Total spend counted by SUM | =SUM(Table1[Total spend]) | $184,210 | $187,650 |
Every difference needs an explanation, and here each one has it. Rows: 1,862 minus 46 exact duplicates, 57 email merges and 9 near-match merges is 1,750, a 6.0% reduction. Spend: converting the 96 text cells added $7,320 that SUM had been ignoring ($191,530), and removing the 46 exact duplicates took out $3,880 counted twice, giving $187,650. The merges moved spend between rows without changing the total, which is how you know nothing was lost. If the after-total had come out at, say, $185,710, the missing $1,940 would point straight at merged rows whose spend was deleted instead of added.
If writing count formulas like these is the slow part, try The Complete Excel Course with AI Integration on Coding Liquids.
Keep the table on its own sheet in the workbook, dated. Next time the list is cleaned, the "before" figures tell you whether the mess is coming back from one source, which is usually the cheaper fix. A sign-up card with separate boxes for each phone digit, for example, stops one of the four phone patterns at source.
How do you stop the customer list getting messy again?
Stop a customer list getting messy again by fixing the sources: drop-down lists for categories, one phone format on every sign-up form, and a monthly re-run of the same checks.
Copilot in Excel can add data validation drop-downs, which removes the "four ways to write Online shop" problem at the point of entry. In edit mode:
Add drop-down lists to Table1: Source allows only Online shop, Till sign-up
and Christmas fair; Consent allows only Yes and No. Show an error message
"Pick from the list" if someone types anything else.
An illustrative reply: "Added data validation lists to Source (3 values) and Consent (2 values) for all 1,750 rows and new rows in the table, with the error message you specified. 64 existing Consent cells are blank and are not affected." That last sentence is the one to check: validation stops new bad entries but does not touch existing values, so the highlighted blanks still need a person.
The rest of the prevention is outside Excel. The till sign-up card got a single box for the phone number with the format printed beside it, and the online shop export was set to include the customer ID, which makes the email-match pass almost unnecessary next time. A monthly run of the count table takes ten minutes and shows which source the mess comes from.
Where customer-list cleaning with Copilot goes wrong
These are the failures that turn a clean-up into a data loss, each with how it showed up:
- Phone numbers converted to numbers. Accepting the number-format card on Phone dropped 1,600 leading zeros at once. It showed up as phone numbers one digit short. Fix: undo immediately, or restore undo point 2; skip that card in future.
- Proper case without exceptions. "Mcallister" and "Van Der Berg" on 31 rows. It showed up in a mail merge. Fix: the exceptions in the prompt, and a check of every Mc, Mac, van and de row.
- Deleting instead of merging. A first attempt asked Copilot to "remove duplicate emails", and spend from the deleted rows vanished. It showed up as the SUM falling by more than the exact duplicates explained. Fix: merge rules that add spend, and a Merged rows sheet.
- Blanks filled with a guess. A prompt to "standardise Consent" filled blanks with No on one run and Yes on another. Fix: say explicitly that blanks stay blank and are highlighted.
- Filtered rows left out. A filter left on from earlier hid 300 rows during one check, so counts looked better than they were. Fix: clear all filters (Data, Clear) before every count and every Copilot request.
For what Copilot can and cannot do with the numbers once the list is clean, see what Copilot in Excel can and can't do with your numbers.
How long does cleaning a customer list with Copilot take?
Cleaning a 2,000-row customer list with Copilot takes two to three hours the first time, including undo points and checks, and about an hour once the prompts and count sheet exist.
| Stage | First run | Later runs |
|---|---|---|
| Backup, table, settings, before counts | 25 minutes | 10 minutes |
| Clean Data suggestions | 20 minutes | 10 minutes |
| Names, phones and consent prompts, with checks | 45 minutes | 15 minutes |
| Three duplicate passes | 45 minutes | 15 minutes |
| After counts and reconciliation | 20 minutes | 10 minutes |
| Total | About 2.5 hours | About 1 hour |
The licence is the main cost, and many businesses already have one of the eligible plans listed at the top. Without Copilot, the same steps are possible with TRIM (which, Microsoft notes, removes all spaces except single spaces between words, though not the non-breaking spaces web forms sometimes add), PROPER, Find and Replace and Remove Duplicates; it simply takes longer and needs more formula confidence.
If you want help fitting a customer-list clean-up into how your shop collects records, I offer one-to-one AI consultations for small businesses.
Cleaning customer data with Copilot in Excel: FAQ
Does Excel's Clean Data feature remove duplicates?
No. Microsoft describes three Clean Data suggestion types: inconsistent text, inconsistent number formats and extra spaces. Duplicates need Data, Remove Duplicates for exact matches, or a Copilot prompt for rows that share an email or phone after cleaning. Run Clean Data first, because extra spaces and case differences hide duplicates from an exact match.
Why can't I see Clean Data on my Data tab?
Microsoft says Clean Data is in Excel for the web and for Windows, is available to Copilot customers, and is reaching people gradually, so it may not be in every region yet. Excel for Mac is not listed. Check your licence in your Microsoft 365 account settings, and try Excel for the web if the Windows app lacks it.
How many rows can Clean Data handle?
Microsoft says Clean Data works best with tabular data of up to 100 columns and 50,000 rows, formatted as an Excel table. A larger customer list still loads, but split it by source or by year and clean each part, then combine them, so each run stays inside the range where suggestions are reliable.
Can I undo changes Copilot makes in Excel?
Yes. Microsoft's Copilot in Excel FAQ says you can undo changes or view prior versions of the file. Version history only works for files saved in OneDrive or SharePoint, so save the workbook there first. Remove Duplicates permanently deletes rows, which is why Microsoft suggests copying the original data to another sheet before using it.
Can I still use the COPILOT function to clean text in cells?
No. Microsoft's message centre notice MC1454373 retired the =COPILOT worksheet function on 14 September 2026 and points users to the Copilot side pane instead. For cleaning, use Clean Data on the Data tab, ordinary functions such as TRIM and PROPER, and prompts in the Copilot pane.
Further reads
- How to Use AI in a Recruitment CRM: Duplicates and Follow-Up — Duplicate handling in a recruitment CRM, where records are people too.
- How to Clean a Supplier Spreadsheet Into a Shopify Import File — The same cleaning habits applied to a supplier import file.
- How to Use ChatGPT for Excel to Clean a Monthly Sales Export — The ChatGPT add-in route for cleaning a monthly export.
- How to Clean Messy Data in Excel: Spaces, Duplicates, Text — Cleaning messy data in Excel beyond what Copilot suggests.
- Power BI vs Excel With Copilot for Small Business Dashboards — Power BI or Excel with Copilot for a small business dashboard: monthly costs side by side, refresh and sharing limits, and the point where Power BI wins.
- How to Analyse Survey Responses in Excel With Copilot — Copilot in Excel themes 312 open-text survey answers, with a codebook, a 40-row accuracy check, PivotTable counts, charts and a Julius comparison.
- Can Copilot Build a Sales Dashboard in Excel? What It Gets Wrong — Build a one-page sales dashboard with Copilot in Excel, then find and fix the double counts, wrong averages and refresh gaps before anyone relies on it.
Browse all AI-for-business tutorials
Sources: Microsoft Support pages Clean data in Excel, Get started with Copilot in Excel, Frequently asked questions about Copilot in Excel, Find and remove duplicates, TRIM function and View previous versions of Office files; the Excel team's Tech Community announcement of Clean Data; Microsoft 365 message centre notice MC1454373; the Microsoft 365 Copilot Business pricing page. All read 22 November 2026. Customer figures are illustrative.
