With specific reference to U3A membership
This is a worked set of instructions for creating labels. It has a number of steps, and may vary according to the label stock used (but only as far as layout and printing are concerned); the label stock identified here is Avery L7160. Note that all images used in this procedure are originally taken from live data, and have been edited to obliterate personal details.
Step 1 – Excel output
The assumption is that an extract of membership details is provided from a third party.
Step 2 – selecting targets for labels
Note that the export may – or may not – include column headers. In this set of examples, the headers have not been included. Column B in this set contains an x to indicate that the member requires a print newsletter:
If consistent identification of members for another purpose is required, then an additional column can be defined and set in a similar way.
To filter members to select only those with the x setting, place the cursor in the relevant column (B) and then select the Excel Data ribbon, and the Filter button:
The result will be a down arrow in that column. Clicking the arrow will show the values held in the column (either x or blank):
And by default both are selected. To select all with x, simply click the tick for Blanks and the results will be filtered (note that the filtered column has a funnel symbol showing):
Now, for ease of operation and to prevent accidents, I select all of the filtered member details and copy and paste them into a new separate worksheet, because I need to do a little manipulation of data. This is not strictly necessary – but I do recommend it.
Step 3 – Tidying the data
In the copied worksheet then, we need to do three separate actions
- Add some column headers
- Remove unwanted columns (optional)
- Merge the two columns for the first address line
1 Add column headers
Insert a new row as Row 1 and enter column headers to identify each of the address elements required. Microsoft Word will require this in the label formatting stage.
When done, ‘Freeze’ the top row using the View ribbon and the Freeze Panes button:
2 Remove unwanted columns
To focus on the required data elements, you can delete from the copy worksheet all other columns. This is not strictly necessary and is optional
3 Merge columns for street address
The Excel output holds the street address in two columns:
G has the house number or name
H has the street name
To obtain correct formatting, the two columns need to be combined. In the following example, we’ll use =D2&”, “&E2 to join the two address elements. Note that the quotes contain comma followed by space.
- Click the cell where you want to put the combined text. This will normally be at the end, but you can insert a new column F for the purpose
- Type =, then click the cell D2 that contains the first text you want to combine.
- Type &”, “& (comma space enclosed in quotation marks).
- Click the next cell E2 with the text that you want to combine, then press Enter.
To copy the action to all remaining instances, go to the resulting merged cell F2 (or whichever) and use Ctrl+C to copy it and then select all of the remaining blank cells in column F for the extent of the data, and use Ctrl+V to paste the same action. All addresses should now show a correct Post Office style. I have then renamed the old columns D and E and given F a name, so it now looks:
Step 4 Save the working!
Make sure the data is saved now. The Excel part is done, and we need to move to Word to format and print the labels.
Step 5 Open Word
Step 6 Create a New blank document. Go to the Mailings ribbon bar and click Start Mail Merge. The bottom option is ‘Step by Step Mail Merge Wizard’ – and this will guide you through 6 Word steps for label production. Click this option therefore:
The wizard details will appear on the right of the screen.
Step 6.1 Tell Word that you want Labels, by selecting the document type
Step 6.2 Start the label document – select ‘Change document layout’ and click ‘Label options’. This will bring up a list of available label templates [Note that your system may not have these, but options should be available on the Web]. The example uses Avery L7160:
Step 6.3 Move to the next element – Select recipients. Ignore the selection ‘Select recipients’ and select the ‘Browse’ option under ‘Use an existing list’:
Word will then ask you to identify the source Excel data file. Note that the file browser will drop you somewhere in /Program Files, and you will need to browse to locate your file. Once located, you will need to identify the worksheet to be addressed:
And this will then show the contents of the worksheet for label production:
On OK, the Word template will show the layout:
Step 6.4 Move to the next point – ‘Arrange your labels’.
All that needs to be dealt with here is the Address block, so click that link and you will be given an example, which is not entirely correct, e.g.:
To fix this, click ‘Match fields’. This will last all of the elements that Word thins will make an address, coupled with a drop-down selection to identify the column in the Excel worksheet. Here, ‘First name’ is not matched, and clicking the drop-down shows all the possible column headers in Excel, and the appropriate one can be selected:
And then shows the correct selection. The following image shows the selections as they should be for the required output:
And the preview address is also suitably corrected:
Step 6.5 Preview the labels