A Business Contact Data Cleanup Checklist for Excel

A postal code can lose its leading zero during import, and a duplicate removal can discard the only useful call note. Clean the workbook in an order that protects those details before you standardize its appearance.

Clean a copy of the export. Check business identity, preserve source values, standardize fields, review duplicates, and confirm that notes still match each record. Reconcile merges and exclusions before using the cleaned file.

Begin with identity and recoverability; cosmetic cleanup comes after the original values are protected.

Preserve the source and define the cleanup scope

Save the original export unchanged. Create a separate working copy and record its row count, source, and capture date. Decide whether you are standardizing formatting, verifying contact details, merging duplicates, or doing all three.

Those tasks are different. Formatting a phone number does not verify that it belongs to the company. Converting a URL into a comparison domain does not prove two rows represent the same branch.

Add stable record IDs if they are missing. They give you a way to track corrections and merges after sorting the table.

Check identity before cosmetic consistency

Open uncertain company sources and confirm the intended business. Correct obvious page-title artifacts, but do not invent a company name from an unreadable site or an ambiguous domain.

For locations, preserve the branch address and parent relationship. An apparently inconsistent city may reveal that one row is headquarters and another is a local branch. Standardizing both to one address would make the data less accurate.

Keep a brief note when a correction changes the meaning of the record, such as replacing an outdated branch source with a current location page.

Use a field-by-field cleanup checklist

Do not replace every blank with Unknown or zero in the data field itself. Use a separate research-status field when the distinction matters. Empty email cells should remain different from a literal text value that an importer might mistake for an address.

Distinguish a display problem from lost data

Suppose a fictional postal code originally reads 01234, but the imported cell contains the number 1234. Changing its format to Text afterward will not recover the missing digit. Reimport from the preserved source with the column treated as text, or correct it against a reliable location source.

Similarly, a telephone number shown in scientific notation may be a display problem, or its value may already have been altered. Inspect the full stored value and compare it with the source before deciding which repair is needed.

Keep an Original value, Corrected value, Reason, and Source for material corrections. Trimming a space usually needs little explanation; changing an address or restoring a leading zero needs evidence. Do not pad every short postal code to a fixed length across countries.

Standardize comparison values separately

Create helper columns when you need simplified names or domains for matching. Keep the original fields alongside them until the review is complete.

For example, two website values may differ only by a tracking parameter. A comparison domain helps identify the candidate match, while the original URL preserves where the researcher found the relevant information.

Be careful with location paths and subdomains. They may identify separate branches or services. A rule that simplifies every URL can remove evidence you need for duplicate review.

Review duplicates before deleting rows

Use Excel's duplicate tools to surface candidates, then apply the record rule for your list. Microsoft documents the duplicate review and removal controls.

Combine useful information before retiring a duplicate. Preserve the strongest source for each corrected field and carry forward contact restrictions. A row with fewer populated cells may contain the most important activity note.

Record which ID was retained and why. This lets later imports or activity logs reconnect with the correct record instead of recreating the retired duplicate.

Keep the full row together during sorting

Sort the complete table, not a single column in isolation. After cleanup, compare a few recognizable businesses against the original export and confirm that their emails, phone numbers, notes, and owners still match.

This check is especially important after copying filtered records or moving columns between sheets. A workbook can look orderly even when its values have become attached to the wrong company.

Review first, middle, and last records, plus any rows affected by merging. Choose examples with distinctive source details so mismatches are easy to detect.

Reconcile the final list

Document the original count, excluded rows, merged duplicates, and final accepted count. The arithmetic should agree, but counts alone are not enough: review the exception list as well.

Save the cleaned file with a clear role and date. Keep it separate from the untouched export, and identify which file is now the authoritative working list.

If you repeatedly clean the same problem, change the collection process. A missing source column, inconsistent branch rule, or vague status definition is better fixed at capture than repaired in every monthly export.

Related Questions

Should I replace every blank cell with Unknown?

Use a separate status or note when you need to explain missing information. Keep data fields such as email, phone, and dates clean so a future import does not treat placeholder text as an actual contact value.

Does cleaning a phone format verify the number?

No. Formatting changes how a value is stored or displayed. Verification requires checking the relevant source and business identity. Record the checked date separately from the date you cleaned the workbook.

For matching decisions, use the duplicate-company guide. Before a CRM handoff, see preparing the import spreadsheet.