A mailing list is a crucial item in any organization. Why? It helps keep in touch with clients and potential clients.

And while business people across all industries rely on mailing lists, things can easily go wrong when it comes to the data. Duplication, misspellings, and incorrect formatting can all detract from your list’s effectiveness.

Excel is a great tool to help combat these common problems, and even though you may already be using it, you probably aren’t taking advantage of its full capabilities. Check out these hacks to clean up your mailing list and get it right the first time:

Removing duplicate rows

You can either highlight or delete duplicate rows. Duplicated data is very common, especially when importing data. Therefore, it’s very important that you go over the data, identify duplicate values, and remove them while keeping the rest of the data intact. If you want to remove duplicate rows first copy and paste the data to another Excel worksheet to preserve the original data, and then remove the duplicated values.

Searching and replacing

This is one of the best hacks to ensure a clean mailing list. The search & replace function in Excel allows one to find a specific word and replace it with a new one. This can be useful, often to replace misspelled words or common words with similar meaning. Using this function, you can select whether to replace the word only on the sheet you have selected or across the whole Excel worksheet. The replace function is another way to delete extra spaces in the sheet.

Related: 8 Campaign-Crushing Direct Mail Blunders & How to Avoid Them

Removing blank cells

Blank cells should be removed as they may cause issues in the mailing list if left untreated. You can either fill cell blanks with the value “0” or “null,” or you can highlight and remove them. If it’s a large data set, doing it manually could cause a lot of work, but there is a method of selecting all the blank cells at once to save you time.

Spell checking

You can use a spell checker to find misspelled words or words not in the custom dictionary, such as a company’s name. There are two ways you can highlight errors in mailing lists while the data is open in an Excel worksheet. The first way is to use conditional formatting after you’ve selected entire data set, and select errors in only the cells that contain it. The second way is to select the entire data set, press F5, click “Go to Special,” and select formulas by unchecking all options except errors. This will select all cells that have errors and you can highlight and delete them. Errors lower the credibility of your mailing list, and will lead a recipient to immediately toss the piece in the trash (if it even gets delivered).


Making the data uniform

When importing data from text files, you might find the data is inconsistent. Sometimes the text in the mailing list could be either lower case, upper case or a mix of both. This can be unified using the formula LOWER, UPPER, PROPER to convert the text into lower case, upper case or proper case respectively. It is a slightly complicated process that needs attention to detail, but it’s much faster than manual correction.

Converting data into numbers

Sometimes while importing data from text files into Excel, numbers get stored as text. This error can obviously create problems, especially for data used in calculations. These numbers stored as text need to be converted back into numbers. You can also avoid this error entirely by selecting the column where the numbers will be placed and changing the formatting to “Text” before importing the data.

6 Excel hacks for mailing lists - Ironmark Annapolis Junction, MD

Final thoughts

A clean mailing list is one of the most important tools business have, as it is an easy vehicle for maintaining contact with existing customers and converting prospects into customers. While it can sometimes be a tedious process to correct your data, the return on this time investment is well worth it.

Oh, One more thing…

Here at Ironmark, we see oodles of direct mail fails because of crappy lists. Want to know the biggest, easily-fixable Excel mistake? De-duping your list only works if 1) You have a standardized convention for addresses, and 2) If you determine whether or not you want your direct mail piece to go to multiple people per household and communicate that to your mail partner. Let me unpick this further.

1) Have a standardized convention for addresses. For example, decide if you are using “123 Main Street” or “123 Main St.” Using different versions of the same address will not flag as a duplicate entry in Excel, even if the contact names are different. Both will be mailed, and if this is a fundraising letter, your donor might be concerned you’re wasting money on postage.

2) Determine if you want one piece mailed per mailing location. For business direct mail, it’s absolutely acceptable for the president and the director of business operations to get an invitation to a chamber event. However, if you are sending an invitation or fundraising letter to someone’s household, you probably only want to send one per location. If you only want one per household, make sure your data is set up that way. Because if you de-dupe your list, and both Mr. Smith and Mrs. Smith are on there, depending on how you de-dupe, they both might get your piece. Talk to your mailing partner about your preferences, and if they’re anything like Ironmark, they will ensure your goals are met and can help you with the process.

Written by Jason Drecchio