(Updated for 2022)
A mailing list is a crucial item in any organization. Why? It helps the organization keep in touch with clients and potential clients.
And while 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.
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 duplicate values.
The Search and Replace function is one of the best hacks for ensuring a clean mailing list as it allows one to find a specific word and replace it with a new one. This is often used to replace misspelled words or common words with a 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.
Blank cells should be removed as they may cause issues in the mailing list if left untreated. You can fill cell blanks with the value “0” or “null.” Alternatively, you can highlight and remove them. If it’s a large data set, doing it manually could create a lot of work, but there is a method of selecting all of the blank cells at once to save time.
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 the 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).
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.
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.
When you're exporting your data, receiving data from a database, or importing it from a text file, you may come across the issue of all text being crammed into one cell. To parse the data from one cell to multiple, follow these steps:
A clean mailing list is one of the most important tools a business can 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.
Related: Direct Mail: Stronger Than Ever In the Digital Age
Here at Ironmark, we see oodles of direct mail fail because of unorganized and dirty-data within lists. Want to know the biggest, easily-fixable Excel mistake? De-duping your list only works if you have a standardized convention for addresses and 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.