How to Clean a Customer List in Excel With Copilot

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Clean a Customer List in Excel With Copilot.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Clean a Customer List in Excel With Copilot.

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 now

Clean 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:

  1. Save the file in OneDrive or SharePoint. Version history, your safety net, only works there.
  2. Use the .xlsx format. Microsoft's FAQ notes that Copilot reports an "Unsupported file state" for formats such as Strict Open XML Spreadsheet.
  3. Set Formulas, Calculation Options to Automatic. Microsoft says Copilot only edits when calculation is automatic.
  4. 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 nameLast nameEmailPhoneSourceConsentTotal spend
[FIRST NAME]MCALLISTER" c1042@example.com"0555 010 123Online ShopYes142.50
[first name]McAllisterC1042@example.com0555010123till sign-upY"38.00" (text)
[First name]O'Neillc2210@example.com(0555) 010 124online shop.yes96.00
[First name]O'Neillc2210@example.com(0555) 010 124online shop.yes96.00
[First name]van der Bergc3305@example.com555 010 125Christmas fair"22.00" (text)
[First name]Halec4417@example.com 0555 010 126Online shopNo310.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:

MeasureBefore
Rows1,862
Emails with extra spaces231
Spend cells stored as text96
Total spend counted by SUM$184,210
Different spellings in Source7 (for 3 real sources)
Different values in Consent6, 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.

  1. 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.
  2. 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.
  3. Undo point 3, after names, phones and consent. Same again.
  4. 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.

  1. Click any cell in the table.
  2. Select Data, then Clean Data. Excel may also show a banner offering to fix the issues it detected.
  3. Read each suggestion card in the side pane. Each covers one kind of issue.
  4. Apply the cards that are right; skip the rest. Check a few changed cells after each.
  5. Save. That is undo point 2.

The gift shop's illustrative suggestions, and what it did with each:

SuggestionColumnExampleDecision
Extra spacesEmail" c1042@example.com"Accept: 231 cells
Extra spacesFirst and Last name"van der Berg"Accept: 129 cells
Inconsistent textSource"Online Shop", "online shop.", "Online shop"Accept, choosing "Online shop"
Inconsistent textConsent"Yes", "yes", "YES"Accept, choosing "Yes" ("Y" is not a case difference, so it stays for later)
Number formatTotal spend"38.00" stored as textAccept: 96 cells become numbers
Number formatPhone0555010123 stored as textSkip: 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:

StageWhat to look atGift shop result
Clean Data: spacesTen changed emails; the extra-spaces count231 to 0; no emails broken
Clean Data: textDistinct values in Source and ConsentSource 7 to 3; Consent still had Y and N, as expected
Clean Data: numbersSpend column total; any cell still text96 text cells to 0; SUM rose by $7,320
NamesEvery Mc, Mac, van and de row, plus twenty at random3 corrected by hand (a double-barrelled surname)
PhonesThe NEEDS CHECK rows; ten random numbers64 flagged; 51 fixed from order records, 13 left blank
ConsentCount of Yes, No and blankBlanks highlighted, none filled
DuplicatesFive merged groups by hand; the Merged rows sheetSpend 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 nameEmailPhoneSourceConsentTotal spend
McAllisterc1042@example.com0555010123Online shopYes180.50
O'Neillc2210@example.com0555010124Online shopYes96.00
van der Bergc3305@example.comNEEDS CHECKChristmas fair(blank, highlighted)22.00
Halec4417@example.com0555010126Online shopNo310.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.

MeasureFormulaBeforeAfter
Rows=ROWS(Table1)1,8621,750
Emails with extra spaces=SUMPRODUCT(--(LEN(Table1[Email])<>LEN(TRIM(Table1[Email]))))2310
Spend cells stored as text=SUMPRODUCT(--ISTEXT(Table1[Total spend]))960
Spellings in Source=COUNTA(UNIQUE(Table1[Source]))73
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.

StageFirst runLater runs
Backup, table, settings, before counts25 minutes10 minutes
Clean Data suggestions20 minutes10 minutes
Names, phones and consent prompts, with checks45 minutes15 minutes
Three duplicate passes45 minutes15 minutes
After counts and reconciliation20 minutes10 minutes
TotalAbout 2.5 hoursAbout 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

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.

Want a second pair of eyes on your setup?

Bring the process you're trying to fix. On a 1:1 call we'll work out which AI tools should handle it, which parts stay manual, and how to roll it out without disrupting the team.

Book a 1:1 call with me