Cleaning Salesforce Duplicates For Free Using Excel/Google Sheets

Your company purchased as their new CRM. The company implementing Salesforce has built it for your company and now your boss wants you to start loading data. The best part is you have an old system that already has the data and your boss says, “Fire it up and transfer everything over.”

Salesforce best practices everywhere say, “Make sure to clean your data before you load it into Salesforce.” Some people follow this rule, while many throw caution to the wind and ‘fire it up’ leading to a mass transfer of dirty data that someone has to clean. That someone is you, so what do you do now?

There is no budget for cleaning software and you are not planning to load anything into your precious org after previous or lack of experience has taught you the errors of taking the ‘too easy’ road. You need ideas that either incorporate what you have or offer a free option that does not include downloading some app from the appexchange because you already have too much to worry about without another problem.

I got you.

This is how I was introduced to Salesforce. Someone ‘fired it up’ three times into our new org with uncleaned/unfiltered data from our old CRM. We did not have DemandTools and there was no money for an app.

Get to Scrubbing Using Excel or Google Sheets

This is the option you were trying to avoid, but these are the only two programs you have on your computer. Where do you start?

Start with the highest level in the data chain. If you have Accounts, Schools, Companies, Clinics, Groups or wherever you situate your contacts / leads underneath in your database. Start with this first and work your way down to the smaller groups or categories (contacts, products, orders, etc).

So clean your highest level first, then clean your next and so forth. My non-profit higher ed org had territorial areas setup at the highest level followed by districts within each area and schools within each district. We marketed primarily to teachers and educational administrators.

Run a basic report in Salesforce that pulls all of the accounts. Make sure you pull the account id, name, address, city, state and phone number. If you have something other than accounts then you will need to pull all of the data that would give you any indicator that you have a duplicate, BUT make sure you also include the Salesforce id of each record. [FYI Alert – A record in Salesforce is just like a row in a spreadsheet.]

Download/export to .csv and open with Excel or even Google Sheets (it’s free if you don’t have MS Office). After downloading the Salesforce report, make sure you scroll all the way to the bottom and delete the Salesforce report stamp. That’s the information with the name of the report and when it was downloaded, etc.

I learned the hard way back in my early Salesforce days that filtering duplicates with this stamp on your spreadsheet will give you a headache.

We don’t want to remove the duplicates from the sheet, you need to identify them and put them on their own tab. When I say filter, you will use the highlight conditional feature in MS Excel to identify all of the duplicates. The link attached will take you to Microsoft support on Filtering for Unique Values or Duplicate Values. I know what it’s like to read someone’s instructions and not know how to perform a feature unless I search Google.

If you are using Google Sheets, there are several free add-ons available that will perform the same action. Just a matter of preference when it comes to selecting an add-on like Remove Duplicates, Power Tools and Advanced Find and Replace. Just click on Add-ons in the toolbar of Google Sheets and select Get Add-ons … Each add-on has directions either in video or written format listed in their websites as well as in the app details.

Once you have identified your duplicates, you have two options:

1) You can go into Salesforce and manually delete or merge your duplicates using your list. I coordinated six people in my office to perform this action. We merged because we had needed to maintain notes and other related data across multiple duplicates. Yes, it was very ugly.

– OR –

2) Create an extra column on the sheet with your duplicates called ‘Duplicate’. Either have your admin or you as admin, can add a temporary checkbox  on your page layout called Duplicate. Update all of those duplicate records with the new ‘Duplicate’ column with all of the rows marked as TRUE. Go to Setup -> Data Management -> Mass Delete Records.

In my situation we were using ‘Account’ records, so we would go to Mass Delete Accounts. In step 3 there is a filter criteria section where you can select any of the fields on the page layout. Select ‘Duplicate’ and filter it to Equals True. Steps 4 & 5 are only if you have related information attached to these records you want to delete.

Be careful with the Permanently Delete checkbox because you cannot recover these records from the Recycle Bin IF you select this checkbox. Once you are finished removing all of your duplicates, don’t forget to remove the temporary checkbox.

This is not a perfect option to remove duplicates from your org, but it is free and there are no worries about dealing with a managed or unmanaged package in your org. I’ve noticed that most Salesforcians are quick to throw out a free app or paid app, but the time spent searching could have been spent actually doing most of the work and probably finishing by the time you realize you will have to put in some work anyway.

I believe we used this method for two months before my director finally got us an approval to purchase CRMFusion DemandTools. I will highlight some tips and tricks I have discovered in my use of the tool over the years.

If I had known then what I know now, I would have recommended mass deleting the data and starting from scratch since our Salesforce was not yet live for the rest of the company. If you are in this predicament, fight for a clean sweep and organized/structured load of clean data. It will benefit both the company’s ROI and adoption success in the long run.

Good luck with your cleaning and please feel free to reach out to me if you have any questions.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s