How to clean a customer list without Excel formulas
Clean a customer list, remove duplicates, fix formats, and catch bad data, without writing a single Excel formula.
By Operelio team · Updated July 2026
On this page5
You do not need formulas to clean a list
Cleaning a customer list is really a handful of jobs: remove duplicates, fix inconsistent formats, clear out blank and placeholder cells, and catch the bad emails and phone numbers. People reach for Excel formulas to do this, but formulas are the slow, fragile way. There is a faster path that needs none, and if you do want the formulas, the full playbook is below so you can judge for yourself.
What a clean list looks like
A list is ready to use when it passes a few basic checks:
| Check | What good looks like |
|---|---|
| Duplicates | One row per person and per company |
| Formats | Dates, phones, and casing consistent down each column |
| Blanks and placeholders | No empty required cells; no N/A, NULL, or TBD left in |
| Emails | Valid addresses, with the obvious bad ones removed |
| Encoding | No garbled characters from a wrong export |
The formula playbook, if you want it
Here is the whole job in Excel. It works; it is just slow to repeat and easy to get subtly wrong:
Fix spaces and stray characters
=TRIM(CLEAN(A2)) removes leading, trailing, and doubled spaces plus most invisible characters. Data pasted from the web often hides non-breaking spaces that TRIM misses, so the full version is =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
Standardize casing
=PROPER(A2) for names and =LOWER(A2) for emails. Watch PROPER on names like McDonald or IBM: it produces Mcdonald and Ibm, so scan the results before trusting it.
Clear placeholders
Open Find & Replace with Ctrl+H (on Mac: Edit, Find, Replace). Click Options and tick Match entire cell contents, then replace N/A, NULL, and TBD with nothing. Without that option, replacing NA also mangles every word that contains those letters.
Flag duplicates
Add a helper column with =COUNTIF($C$2:$C2,C2)>1, pointed at your email column. It marks the second and later occurrences of each value; filter for TRUE and delete those rows. The mixed reference $C$2:$C2 is what makes it flag only the later copies.
Convert formulas to values
Helper columns full of formulas break the moment you delete the columns they point at. Copy each finished helper column, right-click the original, choose Paste Special, Values, then delete the helper. Every cleanup pass ends with this step.
Mind the CSV round-trip
Opening a CSV by double-click lets Excel reinterpret values: leading zeros drop off phone numbers and postal codes, long IDs flip to scientific notation, and dates get re-parsed. Import through Data, Get Data, From Text/CSV and set those columns to Text instead.
Every new export starts this whole sequence again from scratch, and one wrong cell reference throws the result off without an error. The repeat cost is the real argument against the formula route.
Clean the whole list at once
Operelio's Health Check runs your list through more than 40 checks: duplicates and similar rows, blanks, invalid email formats, phone formats, mixed date formats, inconsistent casing, extra whitespace, placeholder values, broken characters, and country and state variants like US versus USA versus United States. You get a 0 to 100 score and a breakdown of every issue with the rows affected. The check itself is free on every plan.
On Pro and up, you pick the fixes you want and Operelio applies them in one job: remove exact duplicates and similar rows, trim whitespace, clear placeholders, standardize dates, phone formats, casing, and country and state names, repair broken characters, and more. It writes a separate cleaned file, re-runs Health Check on it so you can see the new score, and never touches your original. No formulas, no helper columns, no Paste Special.
Score your list, then apply the fixes it finds in a single job, no formulas.
Related
Frequently asked questions
Can I clean a customer list without Excel?
Yes. Upload it to Operelio and run Health Check to get a 0 to 100 score and a list of everything wrong, free on every plan. On Pro and up, pick the fixes you want, duplicates, formats, placeholders, broken characters, and more, and they are applied in one job with no formulas.
What is the fastest way to find what is wrong with my list?
Run it through a checker rather than scanning by eye. Health Check runs more than 40 checks on upload and reports every issue with the rows affected, so within a minute you know whether the list is ready to import or needs work first.
Why did my leading zeros disappear?
Excel reinterprets values when it opens a CSV by double-click: leading zeros on phone numbers and postal codes drop, and long IDs flip to scientific notation. Import through Data, Get Data, From Text/CSV and set those columns to Text. If the zeros are already gone, re-export from the original source.
How do I remove duplicates from a customer list?
In Excel, Data, then Remove Duplicates handles exact repeats, and a COUNTIF helper column lets you review them first. For near-duplicates like Westmarch Ltd versus Westmarch, Ltd., you need similarity matching, which Health Check flags and Operelio's Deduplicate tool removes.
Will cleaning change my original file?
Not in Operelio. It writes a new cleaned file and leaves your original untouched, so you can always compare the two or go back. In Excel, protect yourself the same way by working on a copy, because Remove Duplicates and Paste Special cannot be undone once the file is saved and closed.
Ready to get started?
Upload a file and run your first transformation. Free, no credit card required.