How to make certificates with mail merge (free)

What is mail merge?

Mail merge combines one design with a list. You design the certificate once, with a placeholder where the name goes, and the software makes a copy for each row of your spreadsheet. It's the same idea as Mail Merge in Publisher or Word, and it's handy for:

• End-of-term or prize-giving certificates for a whole class
• Sports club awards with the name and the award, such as "Most Improved"
• Course completion certificates with names and dates
• Name badges for an event

Step 1: Set up your list

Put one person per row, with headings in the first row. Keep headings short and plain:

Check spelling now. It's much easier to fix a name in the spreadsheet than on thirty printed certificates.

Step 2: Save the list as a CSV file

A CSV ("comma-separated values") file is a plain-text version of a spreadsheet. Free Pub Editor reads CSV rather than .xlsx files.

Microsoft's instructions are in Import or export text (.txt or .csv) files, and they note that "only the current worksheet will be saved". Google's are in Download a copy of a file.

Names with macrons or accents? If your Excel offers CSV UTF-8 (Comma delimited), choose that. If names like Māori or José come out garbled in the preview, save the list again as CSV UTF-8, or download it from Google Sheets.

Step 3: Start with the Certificate template

In Free Pub Editor, choose File → New → Certificate. You get a landscape A4 "Certificate of Achievement" with a double gold border, a sample name, a line for what it's for, a seal and two signature lines.

Change the wording, colours (Page Design → Colours) and fonts to suit. Add your school or club logo with Insert → Pictures.

Step 4: Connect the list and insert merge fields

1. Choose Mailings → Select Data and pick your CSV file. Edit Data then shows the rows it found, so you can check the count.
2. Click into the sample name and delete it, leaving the cursor in the text box.
3. Choose Mailings → Insert Merge Field and pick Name. A placeholder like «Name» appears.
4. Do the same for any other fields, such as «Award» or «Date», in the text where they belong.

Merge fields take on the font and size of the text around them, so style the line first.

Step 5: Preview the results

Click Preview Results. The placeholders fill with the first person's details. Use the arrows next to it to step through the records. Check:

• The longest name fits on the line without wrapping.
• Short names still look centred.
• Special characters such as macrons and apostrophes show correctly.

If a field is blank, the heading in your CSV may be spelt differently from the field you inserted, or that cell may be empty.

Step 6: Merge, then print or save as PDF

To make a PDF for emailing or a print shop, choose Merge to New Publication, then Export PDF and pick Save as PDF, with margins set to None and Background graphics on.

Printing tips: print one test copy on plain paper first. Card or certificate paper is often thicker than a home printer likes, so check your printer's paper settings, and feed a few sheets at a time.

How does this compare with other free options?

Already have certificates made in Publisher? Open the old .pub file in Free Pub Editor and its layout comes across. You can then add a CSV list and merge it in the same way. See how to open a .pub file online.

Common questions