How to Clean a Business Lead List in Google Sheets

A messy lead list quietly kills a cold-outreach campaign before the first email goes out. If a third of your rows have a disconnected number or a business that closed last year, your reply rate drops and your sender reputation takes the hit instead of your pipeline. A clean business lead list is one where every row has a working phone number, a current address, and no duplicate entries pulled in from more than one source, and getting there in Google Sheets is mostly a matter of running the right passes in the right order.

What does it mean to clean a business lead list?

Cleaning a lead list means running a fixed set of checks on every row before you trust it enough to call or email. It is not a single action, it is a short sequence, and skipping a step in the middle usually shows up later as a bounced email or a call to a number that was disconnected two years ago.

1. Remove duplicate rows pulled from more than one source or search.
2. Standardize phone number format so every row matches the same pattern.
3. Standardize address format, including suite numbers and abbreviations.
4. Flag or remove businesses that have closed or relocated.
5. Align every column header to one consistent field mapping.

Do these in that order. Deduplicating first means you only standardize and verify each business once instead of doing the work twice on the same row under two different names. Skip the order and you end up standardizing a phone number on a row you delete five minutes later, which is wasted effort at any list size.

The five steps also map to five different kinds of failure downstream. A missed duplicate means two reps calling the same business in the same week. A bad phone format breaks a click-to-dial integration. A stale address bounces a physical mailer. A closed business wastes a call entirely. A broken field mapping breaks the import itself, which is the one failure that stops you before you even start.

How do I remove duplicate rows in Google Sheets?

The built-in tool is under Data, then Data cleanup, then Remove duplicates, which lets you pick which columns count as a match instead of forcing an exact match across every column. For a lead list, match on phone number first, since business names are typed inconsistently: "Smith Plumbing LLC" and "Smith Plumbing" are the same business to a phone carrier and different strings to a spreadsheet.

If you want to flag duplicates before deleting anything, a formula does the job without touching your data: =COUNTIF(B:B,B2)>1 in a helper column marks any row whose phone number appears more than once. Review the flagged rows, decide which copy has the most complete data, and only then run Remove duplicates or delete manually.

Watch for near-duplicates that neither method catches on its own: the same business with a landline in one row and a mobile number pulled from a different listing in another. A secondary pass matching on business name plus zip code, run after the phone-number pass, catches most of what's left.

How do I standardize phone numbers and addresses?

Phone numbers arrive in at least four formats from different sources: with dashes, with dots, with parentheses, or as ten bare digits. According to the Google Sheets support documentation, REGEXREPLACE can strip everything but digits and reformat them in one pass, for example =REGEXREPLACE(A2,"[^0-9]","") to get bare digits, then a second formula to reinsert dashes in the pattern you want.

Addresses are harder to fully automate because abbreviations vary ("St" vs "Street", "Ste" vs "Suite"), but a find-and-replace pass on the most common abbreviations before import catches most of the mismatch that would otherwise break a mail-merge or a CRM's address-matching logic. Build the find-and-replace list once as a reference tab in the same spreadsheet, since the same handful of abbreviations repeats on every list you pull afterward.

Keep the original, unformatted column next to the cleaned one until you've spot-checked at least a sample of rows. A formula that works on 95 percent of your data can still mangle the remaining rows silently, and you want a way to catch that before the list goes out the door.

How do I catch closed businesses before I import a list?

A phone number can be technically valid and still ring at a business that closed eight months ago. Sheets has no built-in way to check business status, so this step means checking each questionable row against a second source, most often cross-referencing Google and Yelp listings for a "permanently closed" label or a long gap in recent reviews.

Doing that by hand for a few dozen rows is realistic. Doing it for a few thousand rows in a spreadsheet is where manual cleaning stops scaling, which is the exact problem ExtractData's search results solve: every result is cross-referenced against Yelp automatically at the point of extraction, not as a manual follow-up step, on every plan including the free tier. You can compare what each plan includes before deciding whether that verification pass is worth building yourself.

How do I keep a CSV field mapping consistent between exports?

Field mapping is matching each column name to one canonical field before the data goes anywhere else. The problem shows up when you pull lists from more than one tool over time and the same data point lands in a different column each time.

Pick your canonical names once, write them down, and rename headers to match every time you import a new export. This step is what makes importing a lead list into a CRM without duplicates possible in the first place, since most CRMs match on exact field names, and a mismatched header is often the real reason an import silently drops rows instead of merging them.

When does manual cleaning in Sheets stop being worth it?

The honest answer is that Sheets is genuinely fine for a one-time list of a few hundred rows, and paying for a tool to do what a free spreadsheet already does is a real objection worth taking seriously. The formulas above cost nothing and work well at that size.

The math changes once you are pulling lists weekly, working more than one zip code or trade at a time, or need the closed-business check to run automatically instead of by hand. At that point the time spent re-running the same five steps every week is worth pricing against a paid plan: ExtractData's Pro tier runs $29 a month, or $23 a month billed annually, and the Agency tier is $79 a month, or $63 a month annually, both with the duplicate and closed-business checks already built into the export.

A middle path exists too: keep using Sheets for the final review and any custom sorting, but let the extraction step hand you a list that's already deduplicated and cross-referenced, so your manual work starts from a cleaner baseline instead of raw search results.

The bottom line

A business lead list is only as useful as the five minutes you spend on it before the first call or email: dedupe by phone number, standardize the format, check for closures, and lock down one field mapping. Do that consistently and a spreadsheet will carry you a long way.

When that weekly cleanup starts taking longer than the outreach itself, create a free ExtractData account and pull one search with the verification built in, then compare the time it saves against your current process before you decide anything. You can also browse more lead-list guides for the steps that come after the list is clean.

Frequently asked questions

Reviewed by the ExtractData Team — lead-generation and business-data guidance, updated September 2026.