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.

5 Best Practices for Measuring the ROI of your B2B Email Lists in 2019

5 Best Practices for Measuring the ROI of your B2B Email Lists in 2019We already went over four proven tips to help you accurately and reliably gauge the ROI of B2B email lists. Now, let’s revisit those crucial ideas and explore new ways for determining your list’s impact on overall marketing and sales results.

It’s typically hard to correctly determine the ROI of most targeted email lists since they’re mostly used for top-of-funnel activities. This means that, by the time a lead becomes a customer, the touch points associated with the contact list that contributed to the sale are often difficult to trace since they took place at earlier stages in the process.

To get around this, the following tips can help you reliably measure how much revenue your B2B email lists enabled your campaigns to generate:


1. Determine precisely where contacts come from

In order to accurately gauge ROI, you need to find out where every contact that becomes part of your list originated from. Did a lead come from organic sources? Which paid source did a particular database record pass through before entering your funnel?

For your B2B email list, this means having separate fields that report where and how you got the contact information.


2. Refine your sales funnel stages

There’s a surprising statistic from MarketingSherpa being thrown around that claims 68% of marketers haven’t yet identified their sales funnel. If you happen to be part of this group, you need to define and refine the stages in your sales funnel right now.

What are the steps a prospect goes through before being deemed sales-ready? What actions constitute a conversion in each of these steps?


3. Track and score leads throughout your funnel

Once you’ve established the precise steps that a prospect has to go through in order to turn into an opportunity, you now need to assign points that indicate how sales-qualified that particular lead is.

This is called lead scoring and is a crucial component of accurately measuring marketing ROI. Points are assigned based on the lead’s attributes (demographic and firmographic details) and their actions (interest and intent).


4. Match closed deals with past touch points

Now that you’ve got contact source information and lead scores recorded in your custom targeted database, it’s time to take a look at the data for deal closes. These closes should be tied back to the series of touch points that preceded the deal.

Marketingprofs says there are four categories of closes based on source and nurture history. It’s important that you identify the right classification for a particular deal, so that credit and attribution can be correctly given.


5. Apply a suitable attribution model

An attribution model is a set of rules that tell you how to distribute credit for results among the different channels and tactics in your sales process. Typically, attribution models are used to determine how much a given channel or tactic contributes to revenues.

There are different types of attribution models. You need to find the best one that fits your process and requirements. Bizible provides a helpful overview on various attribution models, which include:

  • Last touch: The touch point immediately before the sale gets 100% of the credit
  • First touch: The first touch in the sales cycle gets all the credit
  • Linear: Credit is evenly distributed among the channels/touch points
  • Time decay: Touch points closer to sale get bigger share of credit
  • U-shaped: First and final touch points get 40% each, while the rest get the remaining share

With these five best practices, you can now reliably and consistently measure the ROI of B2B email lists. The main idea is that your targeted database does contribute to the revenues your marketing and sales processes generate, provided that you’re using it correctly in your campaigns.

This article was originally posted in September 2017 and has been updated to reflect developments in the topic.