How to Identify and Eliminate Duplicate Salesforce Data

Salesforce          Data Loading           Data Migration  

March 19, 2024


Whether you’re a Salesforce veteran or a newbie, there’s one challenge we’ve all faced: duplicate data. Not only can duplicates lead to inefficiencies and skewed reporting, but they can also undermine the trust in your CRM data. 

Unfortunately, Salesforce doesn’t offer any kind of dupe finder/cleanser tool out of the box. 

May be an image of text that says 'oH NO...'

So what’s an admin/consultant/data migrator to do?

Fear not! As your favorite Salesforce data migration expert, I’m here to guide you through the murky waters of duplicate data with some tried-and-tested methods and a couple of tools that help save the day.

But First, Why Address Duplicate Data?

Before we dive into the solutions, let’s establish the why. Eliminating duplicates in Salesforce isn’t just about tidying up; it’s about ensuring data integrity, improving user adoption, and making sure your marketing, sales, and customer service efforts are as effective as they can be. 

Let’s get rolling!

Step 1: Data Analysis and Cleanup in Excel

May be an image of text that says 'Whata a freakin' mess!'

Before even thinking about third-party tools, let’s start with something familiar—Excel. Excel can be a powerful ally in your quest to combat duplicates. Here are three step-by-step tutorials to get you started:

  1. Using Conditional Formatting to Highlight Duplicates:

   – Open your exported Salesforce data in Excel.

   – Select your data range.

   – Go to ‘Home’ > ‘Conditional Formatting’ > ‘Highlight Cells Rules’ > ‘Duplicate Values’.

   – Choose your highlight color and click ‘OK’. This visual cue helps you easily spot duplicates for review.

  1. Removing Duplicates with Excel’s Built-In Feature:

   – Select your data range or table.

   – Navigate to ‘Data’ > ‘Remove Duplicates’.

   – In the dialog box, choose the columns to check for duplicates and click ‘OK’.

   – Excel will remove duplicates and notify you of the results.

  1. Advanced Filtering for Unique Records Only:

   – Select your range or table.

   – Go to ‘Data’ > ‘Sort & Filter’ > ‘Advanced’.

   – Check ‘Unique records only’ and click ‘OK’.

   – Copy the filtered unique records to a new worksheet for a clean dataset.

These Excel maneuvers are perfect for initial data cleanup phases, especially before major data migration or integration projects. HOWEVER, proceed with caution whenever you’re tasked with cleaning client data before a data migration. 

Special Considerations for Handling Duplicates During Data Migrations

No photo description available.

During a migration, record IDs are L-I-F-E. Example – you have two “John Smith” account records in the source data. Each John Smith account has its own related opportunities, activities, and cases.

Let’s say you identify the original John Smith based on Created Date, and you recommend to not migrate the more recently created John Smith. You still have to keep a list of what records you did NOT migrate, and what record ID you chose to migrate instead. 

THEN, you have to use that record ID key (instead of your success file) to know which record Id to relate the 2nd John Smith’s opportunities, activities and cases to. That becomes a difficult, time-consuming effort, and it opens you up for all sorts of risk.

What should you do in this situation? 

Tell the client they can do any cleanup they want in the source database before the final migration, but when you receive the final data backup you will NOT be de-deduping any records prior to migration. Post-migration, you can provide additional support (if it’s within their budget) to help them learn how to identify and cleanse duplicate records within Salesforce. 

Step 2: Dive Deeper with Third-Party Applications

May be an image of text

Sometimes, Excel’s capabilities might not suffice, especially with large or complex Salesforce datasets. That’s where third-party applications come into play. Here are two recommendations:

  1. DemandTools by Validity:

   – Why Use It? DemandTools is a comprehensive data quality tool designed for Salesforce. It offers powerful deduplication features that can identify, merge, and prevent duplicates across your Salesforce org.

   – How to Use It? After installing DemandTools, use its ‘Find/Report IDs’ module to scan your Salesforce data for duplicates based on your defined criteria. Then, use the ‘Merge/Purge’ module to safely merge duplicates, ensuring no critical data is lost.

  1. DupeCatcher:

   – Why Use It? DupeCatcher is a Salesforce app that flags duplicates as they’re entered or imported into Salesforce, allowing you to intercept them in real-time or batch process existing records.

   – How to Use It? Install DupeCatcher from the Salesforce AppExchange and configure your matching rules. When a potential duplicate is detected, DupeCatcher will alert you so you can take appropriate action, such as merging or deleting duplicates.

Step 3: Ongoing Maintenance and Best Practices

May be an image of text that says 'Me: I'm sure the users will keep the data clean Me tome: You sound stupid as you look me:'

Eliminating duplicates is not a one-and-done task; it requires ongoing vigilance. Here are some tips to keep your Salesforce org clean:

– Regular Audits: Schedule regular data audits to identify and address new duplicates.

– User Training: Educate your users on the importance of data quality and how to avoid creating duplicates.

– Data Entry Standards: Establish and enforce data entry standards to minimize the risk of duplicates.


By following these steps and leveraging both Excel’s capabilities and the power of third-party tools like DemandTools and DupeCatcher, you’ll be well on your way to a cleaner, more efficient Salesforce experience. Remember, the key to success lies in regular maintenance and a proactive approach to data quality. Happy deduplicating!


Table of Contents

Woman with orange fingernails subscribing to Salesforce tutorials
Don't miss out!

weekly tutorials

I’ll send you a new Salesforce tutorial every week, because that’s just the kind of gal I am.