Addressing envelopes

I have to admit that the Mail Merge is one of my favorite options in MS Word and I often use it when I need to send a group of emails or address a large number of envelopes. It is enough to import the contact list and the addressing process is completely automatic. Can something like this be done in Excel as well? Yes, there is an easy way to do that, and you will find out how to achieve this in the text that follows.

To begin with, we will create a simple data table and rename the worksheet to “ADDRESSBOOK”. The table contains serial number, name, address, postal code, city and country and will be used to form the labels that should be displayed on the envelopes.

Then we should add a new worksheet. It should contain addresses we would like to print. In the first cell (A1) we enter the formula:

=ADDRESSBOOK!B2&CHAR(10)&ADDRESSBOOK!C2&CHAR(10)&ADDRESSBOOK!D2&
” “&ADDRESSBOOK!E2&CHAR(10)&ADDRESSBOOK!F2

Then we copy the formula into the remaining cells (A2, A3, A4, etc.). In each of these cells, the Word Wrap option needs to be turned on and the content aligned within the cell.

In the formula, you can see that we use ampersand to concatenate strings. If we want to move content into the next row we should perform concatenation with expression CHAR (10), which is a function that we use to call a character called non-breaking space.

Then on the Page Layout ribbon we need to change the paper size. In Serbia, “American” envelopes (230mmx110mm) are most often used, so we will create a new type of paper of given dimensions and call it “American”. We will select it and set the view mode to Landscape. We will enlarge the font and move the ident until the address is ideally centered in the middle of the envelope.

Since each cell represents a label on one envelope, each subsequent one represents a print on the next envelope. That is, by choopsing Print option (CTRL + P) we will print envelopes for all given addresses.