How to Scrub an Email List in Excel and Keep It Sparkling Clean

How to Scrub an Email List in Excel and Keep It Sparkling CleanWe’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:

  1. Under the Home tab, go to Conditional Formatting
  2. Choose Highlight Cells Rules, then click Duplicate Values
  3. In the Duplicate Values dialog box, indicate the formatting you want and then hit OK

To remove duplicate values:

  1. Select the data range
  2. Go to the Data tab and then click Remove Duplicates
  3. 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).

  1. Choose a blank cell where the trimmed text will be stored
  2. Enter =TRIM(reference_to_text) and hit enter
  3. 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:

  1. Select the entire dataset by pressing CTRL+A
  2. Open the Go-to dialog box by pressing F5
  3. Click the button labeled ‘Special’
  4. Choose the ‘Blanks’ radio button and click OK
  5. 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.

B2B Email Marketing Benchmarks 2019: What to Watch Out for in Q2

It’s finally the second quarter of the year. As you start implementing your email strategies for Q2, let’s take a look at the latest B2B email marketing benchmarks for 2019, so that you’ll have reliable numbers to measure relative to your campaigns’ performance.

We’ve reviewed top email marketing resources and compiled some of their most crucial findings. The results we’ve gathered cover three main email marketing areas:

  • Benchmarks by Industry
  • Benchmarks by Business Size
  • Benchmarks by Location

With that said, let’s dig into the email marketing benchmarks to keep track of for 2019.


Benchmarks by Industry

MailChimp’s latest industry-specific email marketing benchmarks show some notable results. There continues to be a huge degree of variability in email performance by industry. Still, some of the key results are as follows:

  • Across industries, average values are: 20.9% open rates, 2.5% CTRs, 0.44% hard bounce rates, 0.01% spam complaint rates, 0.26% opt-out rates.
  • Median values are: 20.64% open rates, 2.35% CTRs, 0.39% hard bounce rates.
  • Hobbies had the highest open rates at 27.35%, while Daily Deals/Coupons had the lowest open rates at 14.92%.
  • Hobbies also had the highest CTRs at 4.78%, while Restaurants had the lowest CTRs at 1.06%.

With numbers like these, it’s important for you to set email marketing benchmarks appropriate for your industry. This helps you make a valid, apples-to-apples comparison.


Benchmarks by Business Size

When broken down by business size, email marketing benchmarks also tell an interesting story. Data from MailChimp show that email performance tend to vary across different business sizes, suggesting key differences in strategies and campaigns.

  • Among small businesses, companies with at least 50 employees have the highest open rate at 22.1%, while businesses with 11 to 25 employees have the lowest open rates at 19.7%.
  • In terms of CTRs, businesses with 50+ employees also had the highest click-through rates at 2.66%, while companies with 11 to 25 employees again had the lowest CTRs at 2.25%.
  • Spam complaint rates were 0.01% for all sizes of business.

As you can see, email marketing benchmarks differ from one business size to another. This is isn’t really all that surprising, since business size also influences which specific email tools and tactics to use.


Benchmarks by Location

A study from GetResponse shows how overall email performance vary from location to location. In particular, these numbers suggest that email marketers from various geographic regions adopt different email strategies with wide gaps in outcomes and results.


Continent Open Rate CTR Unsubscribe Spam Rate
Oceania 24.15% 14.53% 0.28% 0.01%
North America 19.49% 3.32% 0.19% 0.02%
Europe 26.91% 4.61% 0.27% 0.03%
Africa 18.69% 2.48% 0.16% 0.01%
Asia 20.95% 2.88% 0.13% 0.01%
South America 24.88% 3.50% 0.15% 0.02%


This table highlights the need to use email marketing benchmarks applicable to your specific campaign characteristics. That includes KPIs related to your location, since geographic area also plays a key role in which metrics to compare your performance against.


Conclusion:  As you carry out your campaigns for Q2, keep the above numbers in mind when choosing B2B email marketing benchmarks to gauge how your program is doing.