Salesforce Data Cleansing in a Spreadsheet – Why is it a best practice

Salesforce data cleansing means looking for and correcting records that are duplicate, incomplete, inconsistent, outdated, or invalid. It includes auditing records, merging or removing duplicate records, standardizing formats, and correcting values. Validation rules, duplicate rules and matching rules help prevent recurring errors. Teams can clean data in Salesforce or use spreadsheets and other data management tools.

Salesforce data cleansing means finding and correcting inaccurate, incomplete, duplicate, inconsistent, or outdated records. It helps teams maintain reliable data for sales, reporting, customer management, and automation.

Common problems include duplicate accounts and contacts, missing email addresses, inconsistent phone numbers, invalid field values, and outdated customer details. Cleansing addresses these issues by correcting values, merging duplicates, and removing records the business no longer needs.

Data cleansing differs from related terms:

Salesforce admins often manage data cleansing because they understand the org’s configuration, validation rules, duplicate rules, and data structure. Revenue operations (RevOps) and sales operations teams help define data standards, identify reporting issues, and decide which records to retain.

Data cleansing should be a regular process, not a one-time task. Teams need to correct existing errors and prevent new ones from entering Salesforce.

There are several reasons why Salesforce data can become unreliable:

Poor data quality can impact routing, reporting, and forecasting. For example, if opportunities are linked to duplicate account records, reports that group or filter by account might show the pipeline split across different records instead of giving a full view of the customer. Missing or wrong values can also stop lead assignment rules from working properly.

These errors can lead to duplicate outreach, inaccurate reports, and poor business decisions. Flows can fail if they rely on missing or incorrect field values.

Before merging or deleting a suspected duplicate, make sure you define what counts as a duplicate for each object. For example, two accounts with the same company name could actually be different offices or legal entities.

Measure data quality before cleaning Salesforce records. Record the results to compare them with post-cleanup results.

Six common data quality dimensions are completeness, uniqueness, timeliness, validity, accuracy, and consistency.

Make sure to record the results of these checks before you change any data.

To find the fill rate for each key field, divide the number of records that have a value by the total number of records, then multiply by 100. Put the results in a spreadsheet and highlight any fields with low fill rates.

Before deciding a field is unused or not well maintained, look at its purpose and how long it has existed. If a field was just added, it might have a low fill rate simply because users have not had time to fill it in. Some fields, like an expedite checkbox, are only used in special cases and are expected to be empty most of the time.

Begin with Leads, Contacts, and Accounts. Focus first on fields that impact routing, ownership, segmentation, and reporting. Once you find the main data quality problems, move on to audit other objects.

Cleaning Salesforce data often means exporting records, correcting them in a spreadsheet, and uploading the changes again. XL-Connector connects Salesforce to Excel, where you can identify duplicates and update or delete records in bulk without a separate export-and-upload cycle. Record merging is available in XL-Connector Enterprise Admin.

Learn how to manage Salesforce duplicate records in Excel.

Salesforce provides tools to prevent duplicates, validate data, identify inconsistencies, and update records in bulk. For large cleanup tasks, admins can also export records to a spreadsheet, standardize values, and import the corrected data.

Salesforce includes tools for preventing data errors and cleaning existing records. The right option depends on the type and volume of data you need to fix.

Salesforce allows users to merge up to three records of the same object at a time. Large duplicate groups may therefore require multiple merges. Review the applicable duplicate rules before importing cleaned data because rules configured to block duplicates can prevent records from saving.
For more details, see the Xappex guide to Salesforce duplicate management.

For large or recurring cleanup tasks, spreadsheets and third-party tools can reduce manual work. Choose an approach based on your data volume, cleanup requirements, and available resources.

Choose a tool based on whether you need to prevent bad data at entry, identify existing duplicates, standardize values, or automate recurring cleanup. Fuzzy matching can identify records with similar values, but review suggested matches before merging them to avoid combining unrelated records.

Excel and Google Sheets help you standardize values, find duplicates, and correct Salesforce records in bulk. You can update the cleaned data using Data Loader, the Data Import Wizard, or a spreadsheet connector. Data Loader is the most dependable choice for ID-based updates, because the Data Import Wizard can update existing records only for certain objects.

The process has six steps: export Salesforce data, standardize values, find duplicates, fill missing data, validate changes, and update records in Salesforce.

Always include the Salesforce record ID in every export when you update existing records.

Salesforce has two types of IDs: 15-character and 18-character. The 15-character IDs are case-sensitive, but the 18-character IDs are not. Reports often show 15-character IDs, while tools like Data Loader use the 18-character version.

Excel and many spreadsheet lookup functions do not consider letter casing, so two 15-character IDs that only differ by uppercase or lowercase letters might look the same. To avoid this, use 18-character IDs.

To get an 18-character ID in a Salesforce report, create a formula field on the object with CASESAFEID(Id), and then add that field to your report. 

Import your data as text to avoid unwanted changes. Excel can turn long numbers into scientific notation, remove leading zeros from postal codes, or change phone numbers. If you import columns as Text, you keep their original values. Changing the format after Excel has already changed the data might not bring back lost digits or zeros.

In Excel, go to Data, choose From Text/CSV, then click Transform Data. Set the columns you need to Text before you load the data.

In Google Sheets, import your file with the option to convert text to numbers, dates, and formulas turned off. 

Keep an original copy of your export in a Before tab. Try updating a small batch in a sandbox first before you update your main records.

Differences in spacing, capitalization, abbreviations, and spelling can hide duplicate records. Standardize values before comparing records in spreadsheets or using Salesforce matching rules.

Use helper columns to clean values without changing the original data.

Start by making a two-column lookup table with the original values and their accepted equivalents. Use XLOOKUP to match inconsistent values to the standard ones. If you don’t have XLOOKUP, you can use INDEX/MATCH or VLOOKUP instead. XLOOKUP works in Excel 2021 and later, Microsoft 365, and Google Sheets.

Check any values that didn’t match, make sure the mappings are correct, and then paste the results as values into the final import columns.

Standardizing your data makes it easier to compare spreadsheets and helps Salesforce matching rules find possible duplicates.

Salesforce’s State and Country/Territory Picklists let users select standardized country and state or province values instead of entering free text. Follow Salesforce’s configuration guide to prepare your data and set up the feature.

Before enabling picklists, check for:

Test these dependencies in a sandbox before enabling picklists in production. Follow Salesforce’s instructions for enabling State and Country/Territory Picklists when ready.

Pick a match key for each Salesforce object. For Leads and Contacts, use email addresses. For Accounts, compare company names or website domains, and check phone numbers to spot possible matches.

You can export your records with Salesforce Data Export or Data Loader.

Clean up the match-key values before you check for duplicates. For example, Excel might see John@example.com and john@example.com as different values.

Excel’s Remove Duplicates feature will permanently delete matching rows from the range you select. It checks the columns you pick and keeps only the first match. Before you remove duplicates, copy your original data to another worksheet or workbook, and review the flagged rows first.

Excel only changes your spreadsheet, not your Salesforce data. Use Excel to find possible duplicates, then review and handle those records in Salesforce.

Salesforce uses Matching Rules to spot possible duplicates. Duplicate Rules decide if users can save duplicate records, get a warning, or are blocked from saving them.

To find duplicates across your organization, use Duplicate Jobs. You’ll need the Customize Application and View All Data permissions to run a job. For more details, check Salesforce’s guide to managing duplicate records.

Before you merge, pick the main record and choose which field values you want to keep. Think about data completeness, recent activity, and open opportunities when deciding which record to keep.
In Lightning Experience, Salesforce lets you merge up to three records of the same object at once. The merge combines the selected records into the main record and deletes the others. How related records are handled depends on the object and Salesforce’s merge rules. If you delete a record separately, its field values won’t be merged into another record.
For more tips on finding, preventing, and handling duplicates, check out Xappex’s Salesforce duplicate management guide.

Match an enrichment file, billing export, or event list to Salesforce using email, domain, or record ID. Use XLOOKUP, VLOOKUP, or INDEX/MATCH to retrieve missing values.

XLOOKUP works in Excel for Microsoft 365, Excel 2021 and later, Excel for the web, and Google Sheets. Excel 2016 and 2019 require VLOOKUP or INDEX/MATCH.

Before matching:

• Trim extra spaces and standardize email addresses and domains in both files.
• Use Salesforce’s 18-character record ID because Excel lookups are case-insensitive.
• Check for duplicate keys in the source file.

To fill blanks without overwriting existing values, add a helper column. If A2 contains the key and B2 the existing value, enter this formula in C2:

The formula keeps existing values, retrieves matching values for blanks, and returns a blank if no match exists or the source cell is empty.

For Excel 2016 and 2019, use:

This formula returns text. Use a suitable formula for number and date fields. After checking the results, copy the helper column and paste it as values into the original column. Do not enter the formula in B2, as this creates a circular reference.

Only store data your organization has permission and a legitimate reason to retain.

Check the data in your sheet first. Add a Data Check column that tests required fields, email format, picklist values, text length, date format, and whether lookup IDs exist. It returns “OK” or the first problem it finds, such as “Missing email” or “Invalid picklist value”. Example:

Filter out “OK”, fix the remaining rows.

Next, check Salesforce. Review the active validation and duplicate rules before you update or delete records in bulk. Validation rules can reject updates. Duplicate rules can warn users or block records.

If a validation rule blocks a legitimate cleanup, ask your Salesforce admin to set up a controlled bypass. One option is to use a custom permission in the validation rule formula. Assign the permission to the user running the load, test it in a sandbox, and remove it when the cleanup is complete. 

Keep the load file focused. Include the Salesforce record ID and only the fields you want to change. Formula fields are read-only and do not need to be included. Salesforce recalculates them based on the updated data.

Use Update with Salesforce record IDs to modify existing records. Insert creates new records and can introduce duplicates.

If you do not have record IDs, use Upsert with a field set as an External ID. Salesforce will update records that match or add new ones. Make sure each external ID matches only one record, and check for duplicates before you import.

You can use Data Loader, Workbench, or compatible Excel connectors for these tasks. The Data Import Wizard does not let you upsert with a custom external ID field.

Start by testing a small batch and fix any errors before importing everything else. Keep your original data in a Before tab and your cleaned data in an After tab. Write down the changes you make.

Merge duplicates you have confirmed when needed. Salesforce lets you merge up to three records at a time for accounts, contacts, leads, and cases. You cannot use standard merging for opportunities or most custom objects.

Only delete records after you check their relationships and back up your data. Deleted records usually stay in the Recycle Bin for up to 15 days unless you delete them permanently or remove them sooner. If you delete an account, related contacts and opportunities might also be deleted, depending on configuration.

Excel supports up to 1,048,576 rows per worksheet. Google Sheets supports up to 10 million cells per spreadsheet. Large datasets may still cause performance issues within these limits.

Spreadsheets are also unsuitable for matching names with typos or preventing invalid data during entry. For large data volumes, fuzzy matching, or ongoing data quality checks, use the Salesforce data cleansing tools discussed above.

One-time cleanup is not enough. Prevent new errors with these practices.

Schedule recurring data quality checks and report refreshes where supported. Use saved update procedures to standardize records or fill verified gaps. Notify the responsible team when a check fails.

Clean Salesforce data in this order: audit records, standardize values, find duplicates, fill verified gaps, validate changes, and test a small batch before updating the full dataset. Use native Salesforce tools to prevent and flag errors, spreadsheets for bulk corrections, and specialist apps for large datasets or complex duplicate matching.

Check data quality monthly, perform a deeper cleanup quarterly, and use validation and duplicate rules to prevent recurring errors.

Start this week by checking one Salesforce object for completeness, accuracy, consistency, duplicates, and validity. Record the results to track improvement.