Last quarter, a client handed me a folder containing spreadsheets, exported CRM dumps, LinkedIn contact exports, and a few business card scans that had been OCR'd into a Word doc. The total? Roughly 3,500 contacts with no consistent format, duplicate entries everywhere, and fields that didn't match across sources. My job was to turn this chaos into a clean, queryable Excel database their sales team could actually use.
I've done contact list cleanups before, but this one taught me a few things worth sharing. Here's exactly how I approached it — mistakes included.
Step 1: Audit What You're Actually Working With
Before touching a single cell, I spent two hours just reading through the source files. This saved me from making assumptions that would have cost me days of rework.
The audit revealed:
- Five separate spreadsheets with different column naming conventions
- A CRM export where phone numbers, emails, and names were sometimes in the same column
- Duplicate contacts with slightly different spellings ("Jon Smith" vs "Jonathan Smith")
- Missing company names on roughly 400 records
- Date formats in at least four different regional styles
The audit gave me a clear picture of what I was dealing with and helped me define a master schema before doing any merging.
Step 2: Define Your Master Schema First
This is the step most people skip, and it's the reason most contact databases stay messy forever. I opened a blank Excel sheet and defined every column I needed — nothing more, nothing less.
My final schema looked like this:
- ContactID — a unique identifier I assigned (C-0001, C-0002, etc.)
- FirstName
- LastName
- FullName — concatenated for display purposes
- Email_Primary
- Email_Secondary
- Phone_Mobile
- Phone_Office
- CompanyName
- JobTitle
- Industry
- Country
- City
- LeadSource — where this contact originally came from
- Tags — pipe-separated labels like "prospect|event-2023"
- LastContactDate
- Notes
- RecordStatus — Active, Inactive, Duplicate, Unverified
Having a fixed schema meant every source file got mapped to these columns — not the other way around.
Step 3: Standardize Before You Merge
I processed each source file individually before combining anything. Here's what standardization looked like in practice:
Names
I used Excel's PROPER() function to fix capitalization, then manually reviewed anything that looked unusual. Names with prefixes ("Dr.", "Prof.") got flagged for review rather than auto-corrected.
Phone Numbers
I used a combination of SUBSTITUTE() and TEXT() formulas to strip all formatting characters — dashes, parentheses, spaces — and store numbers as plain digits with country code. A helper column ran a check: if LEN() of the cleaned number wasn't between 10 and 15 digits, the record got flagged.
Email Addresses
I converted everything to lowercase using LOWER() and ran a basic format check with ISNUMBER(FIND("@",A2)). Anything that failed got a "Needs Review" tag.
Dates
This was the messiest part. I created a reference table mapping common date strings to ISO format (YYYY-MM-DD), then used VLOOKUP and manual correction for anything the formula couldn't catch.
Step 4: Merge Into One Sheet and Run Deduplication
Once all five source files were standardized, I copied them into a single master sheet. At this point I had around 4,100 rows — more than the original 3,500 because some sources overlapped significantly.
For deduplication, I used a two-pass approach:
- Email match: Sorted by Email_Primary and used COUNTIF() to flag any email appearing more than once. These were almost always true duplicates.
- Name + Company match: Used a helper column combining TRIM(LOWER(FirstName&LastName&CompanyName)) and ran another COUNTIF() pass. This caught cases where the same person had two slightly different email addresses in the system.
I didn't auto-delete duplicates. Instead, I marked them with a "Duplicate" RecordStatus and kept the record with the most complete data as the primary. After manual review, I removed 640 duplicates, landing at 3,460 clean, unique records.
Step 5: Enrich and Validate Key Fields
A clean database is only useful if the data is actually accurate. For this project, I focused enrichment efforts on the highest-value fields: company name, job title, and country.
For the 400 records missing company names, I cross-referenced email domains against a manually built domain-to-company lookup table. This recovered about 280 of the 400 records automatically. The remaining 120 got flagged as "Unverified" for the client's sales team to resolve.
I also standardized country names using a VLOOKUP against an ISO 3166 country list — so "US", "USA", "United States", and "U.S.A." all became "United States."
Step 6: Lock the Structure and Document Everything
A database is only as good as its documentation. Before handing this off, I:
- Created a "Schema" tab explaining every column, accepted values, and formatting rules
- Created a "ChangeLog" tab with dates and descriptions of every transformation applied
- Used Excel's Data Validation to lock dropdown fields (Industry, Country, RecordStatus) so future entries stay consistent
- Protected the header row and schema tab with a password
- Created a separate "Working Copy" tab so the master data stayed untouched during analysis
The client's team could now filter by industry, tag, lead source, or record status in seconds. What had been an unusable pile of exports became an actual business asset.
What I'd Do Differently
Honestly? I'd start the schema conversation with the client before touching any data. About halfway through the project, I learned their sales team used a custom "tier" classification (Tier 1, Tier 2, Tier 3) that wasn't in any of the exports. That meant adding a column, re-reviewing 3,400 records, and having the client manually assign tiers to their top prospects. Build the schema together. It saves everyone time.
If you're staring down a similar contact mess — whether it's 500 records or 50,000 — the process scales. Audit first, schema second, standardize per source, merge, deduplicate, enrich, document. In that order. Every time.