Many of us use Microsoft Excel to store, manage and clean up mailing lists.
With an accurate and tidy mailing list, you can:
Find information easily
Rest easy that your mailings will arrive with the intended recipients
Achieve better customer satisfaction
Avoid duplication and unnecessary waste
Easily share information
Work more effectively and efficiently
What is the best way to organise a mailing list?
Here are my tips to help you get your mailing list in order, using Excel:
1. All in one place
I’ve often seen different groups of contacts arranged on separate sheets (tabs) of a spreadsheet, but it is much better to place all the contacts in your mailing list onto a single sheet.
Then you can easily, find information, share your mailing list, and avoid duplicates.
2. Get ahead
Your list should have a header row: information on the first row of the sheet that describes the information in each column.
Then all the rows below will contain the information about your contacts.
3. Mind the gap
Make sure there are no blank rows or columns.
When it comes to filtering or sorting your contacts empty rows can mean Excel may miss some of your data.
Empty columns can cause problems when it comes to uploading your mailing list data into other software.
4. Organise into categories
If you need to organise your contacts into categories, use a column.
For example, you may wish to distinguish between your customers and your suppliers; in which case you would add a column named something like “contact type”; then for each contact you simply add the word “customer” or “supplier”.
This will allow you filter and sort contacts of a particular category as the need arises.
Organising your mailing list into categories can be really helpful, but bear in mind that you’re going to need to keep these categories up-to-date.
So don’t overcomplicate your mailing list with too many categories.
5. Swoop on those dupes
More recent versions of Excel allow you to highlight duplicates using the Conditional Formatting tool.
If you have an older version of Excel, sort your contacts, then scan down the page to see if any duplicates appear.
6. We’re all individuals
A good way of avoiding duplicate contacts is to identify something that is unique to each person on your mailing list.
The email address isn’t the best way to uniquely identify someone because a contact could have more than one email address.
Similarly, you could have two contacts of the same name, so it’s best not to use names.
However, your contacts might have a unique customer ID or a membership number you could use.
You could even create a unique ID number specifically for your mailing list.
Here’s a quick way to create a list of ID numbers:
7. Divide and conquer!
You’ll have more control over the information in your mailing list if each element is divided into separate columns.
It is also much easier to upload information to other databases when it is organised in this way.
Organise addresses into separate columns. For example, street address, town/city, county/state, postcode, etc.
This will give you much more flexibility to search for and filter the address data. For example, you could pick out all the people who live in a particular town or postcode area.
If your mailing list has address details bunched together all in one column our Address Spritzer will help you separate the details into columns quick as a flash.
Avoid naming your address columns like this: “address line 1”, “address line 2”, “address line 3”. Addresses vary in length, so you’ll find information of a particular kind (for example the postcodes) will appear in more than one column.
8. Seasoned greetings
I’d also recommend separating the individual parts of each name.
Make separate columns for title, first name and last name.
Knowing the title (Mr, Mrs, Miss, etc.) can be helpful if you can’t work out the gender of your contact from their name.
More importantly, by splitting the elements of the name you can create a salutation for your mailings based on either the first name or the title and last name:
Dear Mr Bloggs
For a speedy way to combine the title and last name to create a salutation, check out Combo Gumbo.
9. Zero Hero
Do you notice how when you type a phone number into Excel the zero at the beginning of the number disappears?
This is because Excel automatically detects what you’ve entered is a number. It, therefore, assumes that the zero at the beginning is not needed.
You can rectify this by reformatting the cells to “text” and then typing the zeros back in by hand. This is a time-consuming process and there is a risk of errors being made.
Help is at hand with our clever little Zero Hero, which will automatically add the zeros to the beginning of the phone number.
10. A clean sweep
Last but certainly not least, make sure your mailing list is free from mistakes.
Incorrectly addressed emails won’t get to your intended recipients
Misspelt names can infuriate your contacts
Errors anywhere can make information difficult to find
If you are not good a proof reading, find a meticulous colleague to go through your mailing list with a fine-tooth comb.
Look out for these common email errors:
Mixing up dash –, hyphen - and underscore _
A comma instead of a dot.
.ocm instead of .com (and similar typos)
Email service provider names typed incorrectly e.g. “Yahooo” or “Googl”