How to clean a customer list without Excel formulas
Remove duplicates, fix inconsistent formats, clear placeholder cells and catch bad emails and phone numbers, without writing a single Excel formula.
By Operelio team · Updated October 23, 2026
On this page5
You do not need formulas to clean a list
To clean a customer list without formulas, upload it to Operelio and run Data Health Check. It scores the list from 0 to 100 and lists every issue with the rows affected, and you pick the fixes it applies in one job.
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. If you would rather do it with Excel formulas, the full playbook is below.
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 |
How to clean data in Excel
To clean data in Excel, strip extra spaces with TRIM and CLEAN, fix casing with PROPER and LOWER, clear placeholders with Find & Replace, flag duplicates with a COUNTIF helper column, then paste the results back as values.
Here is the whole job, step by step. It works; it is just slow to repeat and easy to get subtly wrong:
Fix spaces and stray characters
TRIM removes leading, trailing and doubled spaces, and CLEAN removes most invisible characters, including line breaks, but with nothing in their place, so words on two lines run together. Swap line breaks and the non-breaking spaces web data hides for ordinary spaces first: =TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, UNICHAR(160), " "), CHAR(10), " "))). UNICHAR(160) is the non-breaking space in Excel on Windows and Mac alike.
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 check 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 Data Health Check checks your list for 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 is on every plan. Each run uses one job.
You also pick the fixes you want and Operelio applies them in one job: remove exact duplicates and similar rows, trim whitespace, clear placeholders, standardize dates, casing, and country and state names, repair broken characters, and more. It writes a separate cleaned file, re-runs Data Health Check on it so you can see the new score, and never touches your original. No formulas, no helper columns, no Paste Special.
If the list is going into a CRM, the CRM Formatter maps the cleaned file to your CRM's fields, and on Starter and up it can push the contacts straight to HubSpot, Salesforce or Pipedrive.
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 Data Health Check to get a 0 to 100 score and a list of everything wrong, on every plan, using one job per check. Then 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. Upload the list and run Data Health Check: it reports every issue with the rows affected, so 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 the same company written two ways, like Westmarch Ltd versus Westmarch, Ltd., you need matching that ignores suffixes and punctuation: Data Health Check flags those rows, and Operelio's Deduplicate tool removes them with smart matching.
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.