How to Identify and Merge Duplicate Leads in Google Sheets Automatically

Q: Does Google Sheets' Remove Duplicates tool merge data from both rows?

No. Data > Data cleanup > Remove duplicates simply deletes the later duplicate row entirely, even if it contains information (like a phone number or note) that the first row is missing. To preserve that data you need to manually copy the missing fields into the surviving row before deleting the duplicate, or configure a tool that keeps the most complete record.

If you use Google Sheets as a CRM or lead tracker, duplicate entries are your worst enemy. A customer might fill out your contact form twice, or a sales rep might accidentally input the same lead from two different sources. When this happens, follow-ups get messy, analytics get skewed, and you look unprofessional.

In this guide, we'll show you exactly how to identify duplicate leads, merge their critical data, and set up automation to keep your pipeline clean.

Why Duplicate Leads Destroy Sales Funnels

Having duplicate rows in a standard spreadsheet is annoying, but in a sales tracker, it actually costs you money:

Double Outreach: Two different sales reps might call the same lead.
Mismatched Context: One row might have the lead's phone number, while another has their budget. You lose the complete picture.
Inflated Metrics: Your pipeline might look full, but it's actually padded with clones.

Step 1: Identifying the Duplicates

Before you can merge anything, you need to find the duplicates. The best unique identifiers for leads are usually Email Addresses or Phone Numbers.

          Pro Tip: Never use "Name" as your only duplicate check. "John Smith" might be two entirely different people from different companies.
        

You can use Conditional Formatting to highlight duplicates visually:

Select your Email column (e.g., Column B).
Go to Format > Conditional formatting.
Under Format rules, choose "Custom formula is".
Enter =COUNTIF(B:B, B1)>1.
Choose a red highlight color.

Step 2: Merging Data Without Losing Details

Google Sheets doesn't have a native "Merge" button that combines data from two rows intelligently. If you just use "Data > Data cleanup > Remove duplicates," Sheets will blindly delete the second row, potentially erasing notes or phone numbers that weren't in the first row.

Here is a manual workaround to merge safely:

Sort your sheet by the Email column so duplicates are adjacent.
Review the duplicate rows. Copy the missing data from the second row into the first row (e.g., moving the phone number up).
Once the first row has all the combined data, delete the second row.

Warning: Doing this for hundreds of leads manually will take hours.

Step 3: The Automated Solution

If you process leads daily, manual merging isn't sustainable. This is where a dedicated tool like CleanSheet comes in.

CleanSheet allows you to set up rules that automatically scan for duplicate emails. More importantly, it lets you choose to Keep the First Duplicate. By ensuring your web forms push the most comprehensive data first, CleanSheet can automatically sweep through your CRM, stripping out the redundant entries without touching your primary lead rows.

Setting Up the Automation:

Install the CleanSheet add-on from the Google Workspace Marketplace.
Open your Lead Tracker sheet.
Go to Extensions > CleanSheet > Remove Duplicates.
Select your Email column as the criteria.
Run the tool to instantly clean your pipeline.

Conclusion

Your sales pipeline is only as good as the data inside it. By actively identifying and managing duplicate leads, you ensure your team operates efficiently. Stop wasting time on manual data entry and start using automated cleanup workflows today.

Frequently Asked Questions

Should I use name or email address to detect duplicate leads?

Use email address or phone number, not name. Two different people can share a name like "John Smith" from completely different companies, so matching on name alone creates false positives. Email is the most reliable unique identifier for lead deduplication.

Does Google Sheets' Remove Duplicates tool merge data from both rows?

No. Data > Data cleanup > Remove duplicates simply deletes the later duplicate row entirely, even if it contains information (like a phone number or note) that the first row is missing. To preserve that data you need to manually copy the missing fields into the surviving row before deleting the duplicate, or configure a tool that keeps the most complete record.

Can I automatically remove duplicate leads every day without manual review?

Yes. CleanSheet lets you set up a rule that scans your Email column for duplicates and keeps the first occurrence automatically, running on a schedule so new duplicate leads from daily form submissions are cleared out without you having to check the sheet manually.