Do you need to know how to manage, build and print out a mailing list in Excel? It’s pretty easy to do but you need to do a little planning ahead of time.
If you’re buying a mailing list, chances are you need a way to open it on your own computer, sort it, and basically see what you have. Building a mailing list using Microsoft Excel is a perfect solution.
In this article on Excel mailing lists we’re calling Excel columns “fields.” That’s because “fields” is the terminology that tends to be used with postal mailing list programs. It’s also the terminology if you manage a mailing list in Excel but do a mail merge with Microsoft Word to create mailing labels.
A basic consumer mailing list will include first and last names in separate fields, a field called “street address”
or something like “address 1” and an optional address field called “address 2,” and fields for city, state and ZIP code.
At least one field for address is required for the street address. Sometimes mailing lists include a field called “address 2” which is for apartment number, suite number, or similar things. It isn’t necessary for a basic consumer mailing list that you’re going to build in Excel. Most of the time apartment numbers are included in one address field.
You’ve probably seen a field used for “address 2” or something similar if you bought a product online. Many online retailers use s shopping cart that asks for this.
Here’s an example of what your mailing list in Excel will look like:
|1||First Name||Last Name||Address||City||State||ZIP|
|2||John||Doe||123 Main St||Rockville||MD||20847|
|3||Jane||Smith||456 Oak St, Apt B||Rockford||IL||61104|
|4||George||Jones||789 Elm St||Rockport||MA||01930|
You will receive your mailing list probably in one of three file formats. One possibility is a .txt file (“text” file). Another is a .csv file (csv stands for “comma separated values” and is commonly called just “csv” or “comma delimited”). The final format is an Excel file, which actually is not the best format for a mailing list as you’ll learn down below.
Mailing lists in all of these formats can open directly in Microsoft Excel. From Excel, open the file by clicking on the file name, or right click on the file and select Excel as your app.
Format your Excel columns correctly
There are a few precautions. First the big one: ZIP codes! This is extremely important if you are using ZIP codes that include the East coast.
In Microsoft Excel you need to format the column for your ZIP code, as a ZIP code. Otherwise leading zeros are removed. Look at the Excel mailing list example above. The last entry is for an address in Rockport, Maine and ZIP code 01930. If the column is not formatted for ZIP codes then it will appear as “1930.” This is a common problem with mailing lists in Excel.
To solve this problem, highlight the entire column, go to the pull down menu for Format and select Cells… then Special. You’ll see an option to format the column as either a ZIP Code or ZIP Code +4.
If your mailing list provider included ZIP codes with the 4-digit extension, that’s a good thing. That means the address has been validated. If not, that’s fine. Either way, format your column.
Excel mailing lists using ALL CAPS
Sometimes a mailing list is provided with the text in ALL CAPITAL LETTERS. This is actually the recommended format for reasons you’ll see below.
Some people want to use Upper and Lower Case Letters. Here’s how to change ALL CAPS to Proper Case.
Note that converting your mailing list to Proper Case creates another set of problems, which we also explain below.
In a blank column, enter the Excel formula for Proper Case, which is =PROPER( ). If you were converting the text in cell A1 the formula would be =PROPER(A1).
|1||First Name||Last Name|
|2||JOHN||=PROPER(A2)||123 MAIN ST||=PROPER(C2)|
|3||JANE||=PROPER(A3)||456 OAK ST, APT B||=PROPER(C2)|
|4||GEORGE||=PROPER(A4)||789 ELM ST||=PROPER(C2)|
In the sample Excel spreadsheet above, we used the formulas to convert our mailing list to Proper Case.
|1||First Name||Last Name|
|2||JOHN||John||123 MAIN ST||123 Main St|
|3||JANE||Jane||456 OAK ST, APT B||456 Oak St, Apt B|
|4||GEORGE||George||789 ELM ST||789 Elm St|
In the Excel spreadsheet above, columns B and D show the results.
Once you’ve converted you can copy and paste as Values into your original First Name and Last Name columns. Make sure you paste as Values or you’ll copy the formulas instead and you’ll get errors like a #REF referral error.
Mailing list formatting for Irish, German, Italian or Dutch surnames
Many Irish, Dutch, English, German, Austrian, Italian and French surnames (last names) have a prefix. This is sometimes connected to the main last name and sometimes it has a space. This makes managing Proper Case difficult. If you have a name “MACDONALD” it will converted to “Macdonald” instead of “MacDonald.” A last name “VAN PELT” with a space would correctly be converted to “Van Pelt” as long as the person doesn’t use the spelling “van Pelt.” “VANPELT” without a space would be incorrectly converted to “Vanpelt” if the user spelled it with a capital P, as in “VanPelt.”
These reasons along with dozens of other are why using ALL CAPS in your Excel mailing list is easier.
How to print your mailing list
Excel makes it easy to do what’s called a “mail merge” and print your mailing list out on an ordinary printer. You can print on envelopes or you can print on labels like Avery labels. To do this you’ll use Microsoft Word, create a mail merge, and it will pull the data from Excel into Word.
First, make sure you have finished all your edits and you have properly saved your Excel file with your mailing list. Make sure your field names (at the top of your Excel columns in row 1) match your Word document.
See the image here, courtesy Microsoft. From Word, select the Excel file that contains your mailing list, here called the “Recipient List.” This is where you will connect the Excel file and select what data will be pulled into your Microsoft Word document in order to print your labels.
Mail merge recipients
In this image, also courtesy of Microsoft, you can see the “Data Source” which is your Excel spreadsheet with your mailing list. The fields are starting to show here, starting with Last Name and First Name. You can click individual records (or rows) or you can click the box at the top to select all records.
The Address Block
This image shows where to add the Address Block on your printed labels or envelopes. Many modern printers have built-in templates for Avery or equivalent labels. You’ll need to check your printer’s documentation for complete directions specific to your printer.
If you’re printing on labels, Avery and other label brands have mailing labels that are designed for your ink jet or laser printer. Mailing labels are commonly 1/2 inch by 2-5/8 inches and 30 on a page. Other sizes are available if you need larger labels to include your return address, for example, or for other shipping methods.