(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.
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 duplicate values.
SEARCHING AND REPLACING
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.
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 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.
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 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).
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.
- Find a blank cell, type 1.
- Select the cell where you typed 1, and press Control + C.
- Select the cell/range in which you want to convert your text to numbers.
- Select Paste through your menu.
- In the Paste Special Dialogue Box, select Multiply.
- Click OK.
PARSING DATA USING TEXT TO COLUMN
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:
- Select the data/text you want to parse.
- Go to Data > Text to Column.
- Select Data Type > Next.
- Select Delimiter (the character that separates your data).
- Select the data format and destination cell.
FINAL THOUGHTS
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
OH, ONE MORE THING…
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.
- 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 either 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 that you’re wasting money on postage.
- 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.
