We’re now well into spring. It’s time to get some cleaning done. When it comes to scrubbing lists, we marketers have a huge menu of tools to choose from. But nothing beats good old Excel when doing quick list cleaning tasks. In this post, we’ll learn how to scrub an email list in Excel.
Even with powerful CRM platforms and heavy-duty list management tools, it’s still hard to imagine life without Excel. Excel acts as a scratchpad of sorts for scrubbing email list. It’s the perfect tool to do some initial processing of raw data, as well as for further polishing list entries.
To help us truly leverage the power of Excel, let’s learn four handy Excel tricks for cleaning lists. Under each approach, we’ll take a look at a step-by-step guide to implement a given technique into your email list cleaning process. With that said, let’s dive right in!
Delete duplicate contacts
Duplicate email list records weigh down campaign performance. They lead to additional costs and lost productivity. That’s why duplicate records need to be removed.
There are two ways to handle duplicate records in Excel. First is to highlight them, and second is to remove them altogether.
To highlight duplicate records:
- Under the Home tab, go to Conditional Formatting
- Choose Highlight Cells Rules, then click Duplicate Values
- In the Duplicate Values dialog box, indicate the formatting you want and then hit OK
To remove duplicate values:
- Select the data range
- Go to the Data tab and then click Remove Duplicates
- In the Remove Duplicates dialog, choose the columns that contain duplicates (make sure to properly indicate whether your data has headers or not)
Remove extra spaces
Things like leading and trailing spaces, as well as added spaces in between characters, can wreak havoc on your campaign. It can lead to poor targeting and personalization (since these tactics often rely on exact matching). Extra spaces also cause validation issues that, in turn, produce bottlenecks in your email automation tool.
The most effective way to remove extra spaces (without manually checking each cell) is to use Excel’s TRIM () function.
The TRIM function removes all extra spaces that lead or trail words as well as spaces in between characters (except for single spaces).
- Choose a blank cell where the trimmed text will be stored
- Enter =TRIM(reference_to_text) and hit enter
- Apply the function to other cells in need of trimming
Manage empty cells
Blank or empty cells can also negatively impact the accuracy and precision of your email campaigns. Having blank cells in your email list can result in unexpected sorting, filtering, and segmentation behaviors.
The correct way to deal with blank cells is to fill them with appropriate values (or highlight them accordingly). Here’s how to do this:
- Select the entire dataset by pressing CTRL+A
- Open the Go-to dialog box by pressing F5
- Click the button labeled ‘Special’
- Choose the ‘Blanks’ radio button and click OK
- Once all blank cells have been selected, enter the default value by typing it and then hitting CTRL + Enter
Ensure data consistency
Inconsistent formatting, improper capitalization, and haphazard abbreviation are all symptoms of poor data consistency. Here are some ways to make sure your email list contains uniform, standardized data:
- Apply correct capitalization with Excel’s LOWER(), UPPER(), and PROPER() functions
- Convert numbers written as text into proper numbers using the Multiply option in the Paste Special dialog box
- Expand data into multiple cells using Excel’s Text to Columns feature
- Clear formatting using Home > Clear > Clear Formats
- Use Find and Replace to locate and fix errors
Conclusion: These are just a few steps on how to scrub an email list in Excel. Now, it’s time to put them into action.