We all know that it’s the little things in life that make people feel special. One of the most thoughtful gestures you can give to someone, such as your customers, is a gift certificate or gift voucher. Instead of using store-bought generic ones, we’ve created a tutorial to show how to make custom gift certificates with Microsoft Word and Excel.
The process I’ll outline can be separated into 5 tasks:
- Define what you want on your gift certificates
- Create a custom label size
- Create an Excel worksheet with your gift certificate data
- Do a mail merge with the certificate info
- Final styling
Define Your Gift Certificate Elements
There is no set protocol of what goes on a gift certificate. You’re pretty much free to do what you wish. One caveat is many regions do not allow you to use an expiration date. If you’re considering that field, check your local laws.
In the gift certificates I’ve created, I tend to use the same data variables. The first set is constant on each certificate. There is nothing sacred about these and you can add other elements like web address, phone number, etc.
- Business or your name
- Business address
- Logo or picture
- Gift certificate title
The second set varies based on the certificate type. For example, sometimes you may want to give a dollar amount. Other times, you want to give a specific item or service. These are also items that differ between recipients.
- Recipient’s name
- Gift Certificate number
Create Custom Word Label
Surprisingly, I was appalled that there wasn’t any predefined gift certificate paper available that I liked. This meant I needed to create my own. But, again, you’re free to experiment. I decided to have each sheet of 8 ½” x 11″ paper have 3 gift certificates. This way, they could fit in matching #10 envelopes.
I also wanted to put some white space between each certificate to make it easier to cut the gift certificates. A sharp paper cutter is nice for these situations, but scissors work.
For this tutorial, we’ll create the custom labels for 8.5 x 11 paper. These instructions also show the menu names for Microsoft 365. Your Word version may be different, but the process is the same.
To create a custom gift certificate label,
- From the Word ribbon, select Mailings and then Labels.
- On the Envelopes and Labels dialog box, click the Labels tab.
- Click the label graphic in the lower right.
- Click the New Label… button.
- In the Paper Size: field, select Letter.
- Fill in the Label Details dialog with the size information for your certificates. In the diagram below, I selected Letter for Page Size. Each page will have 3 certificates that are 3″ in height and 6.5″ wide.
- Enter a descriptive Label name.
- Click OK. You should now see your custom label show under the Product number list box. The Label vendors value will show Other/Custom.
- Click OK.
- You can hit Cancel to close the dialog.
Create the Excel Source File
Next, we’ll use Excel as the repository for our gift certificate items. The spreadsheet contains details for each certificate and recipient that we built using an Excel Data Form. If you wanted to get fancy, you could define your own custom drop-down list in Excel for Gifts to ensure consistency.
In our tutorial, we’re adding several columns. Each gift certificate will show:
- Gift certificate number
- The recipient’s name
- Their gift
Again, there’s an opportunity for you to get creative here. While I’ve used items found in a coffee shop, you could use anything from a “ski weekend” to a “1-hour babysitting” to $50.
The only requirement is you want to label your Excel columns. The header information will display when we get to Word’s mail merge.
We did an earlier Word mail merge tutorial using Excel to create personalized letters that share some of the same concepts.
Merge Excel Records with Word Label
In this stage, we’ll setup a mail merge to pull in the Excel records and display them in our custom gift certificate label.
Start the Mail Merge
- Select Mailings from the ribbon.
- Click the small triangle next to the Start Mail Merge button.
- From the drop-down menu, select Step-by-Step Mail Merge Wizard…
- From the right Mail Merge pane, select Labels.
- Click Next Starting document from the bottom. This advances you to Step 2 of the wizard.
- Click Label options…
- The Label Options dialog opens. Change your Label Vendors to Other/Custom.
- Highlight your custom label from the Product number list box.
- Click OK.
Add Border to Define Certificates
At this stage, I add a border to each gift certificate as it helps me see my boundaries. This is an area where you can customize with color and line types. I used a 6pt dark green border.
- Click the squarish icon outside the first certificate. A black shaded area will appear.
- From the Home menu, select Borders from the Paragraph group.
- Select Borders and Shading from the drop-down menu.
- Select All and apply your color and line thickness.
- Click OK.
You should see your 3 certificate areas with a border and a gap between each one.
Create the First Certificate Label
Now, we need to create the contents for the first certificate. Our simple example will use a Word table to lay out our content. I find it easier to tweak spacing by having items in different table cells.
- Insert your cursor in the first certificate.
- From the Insert menu, select Table. I like to use 2 columns and 3 rows for layout.
- Highlight the first table row and right-click and select Merge Cells.
- Enter your certificate title in this first table row. For example, “Rosetta Roasters Gift Certificate.”
- In the first column on row 3, enter the business address. You can use 2 lines.
Add the Picture or Logo
- With your cursor in column 1 on the second row, select the Insert menu and Picture. Navigate to wherever your logo or graphic file resides.
- If your artwork doesn’t fit, you may need to resize it by clicking the picture and using the sizing handles.
Add Certificate Text and Placeholders
- In column 2 of row 2, add your certificate text and placeholders for the data. For example, you might type “This gift certificate entitles [name].” The [name] is a placeholder to make it easier to see where the data will go.
- In column 2 of row 3, I enter the certificate number such as Gift Certificate #:
Your certificate looks similar to the one below. You’ll notice we have not done any refinements such as changing fonts or positioning. We also have some table lines we might want to delete.
Apply Your Styling
This is where you get to be creative based on your needs. First, however, you want to apply your styling on the first certificate so you can copy it down to the other records. This is also a good time to spellcheck your Word document.
Also, since this is a table, you can easily adjust the column widths. For example, you might want less white space to the right of the image.
In the example below, I adjusted the column width, so the logo column is shorter. I also applied a different font and adjusted the table to go full width because I wanted a bottom border under the business name.
Add the Data Variables
This where we will pull in the data from our Excel worksheet and remove the placeholder name. The goal is to get the first certificate correct.
- Click the Next link on the Mail Merge pane’s bottom to go to Step 3 of 6.
- Select the radio button for Use an existing list.
- Click the Browse.. link in the middle of the pane.
- In the Select Data Source dialog, point to your Excel spreadsheet you created earlier.
- The Select Table dialog opens. Click OK.
- The Mail Merge recipients dialog opens. You should see the contents from your Excel spreadsheet. Click OK.
You should see the certificates #2 and #3 show <<Next Record>> at the top.
Remove the Table Lines
The other item I like to do at this time is to remove the table lines so they don’t show on the final gift certificate.
- Click anywhere in the first certificate.
- From the Table menu, Select | Table. Your table contents will highlight.
- From the Format menu, select Borders and shading.
- Click the None box.
- Click OK. You will still see table grid lines, but these will not print.
Add the Data Fields
- In the Mail Merge pane, click Next to move to Step 4.
- Place your cursor to the right of the first placeholder you used, such as [name].
- On the task pane, click More items…
- In the Insert Merge Field dialog, highlight your field from Excel and click Insert. You should see your field name with chevrons on either side.
- Click Close.
- Remove your placeholder text for that field name, such as [name].
- Repeat this process for each data variable on Gift certificate 1.
Your certificate will look something like the one below.
Merge the Gift Certificate Data
If the first certificate looks fine, we want copy the codes, image, and formatting to the other records on the sheet.
- Click in the second certificate under the <<Next Record>> code.
- Click the link Update all labels. Your 2nd and 3rd certificates should look similar to the first.
- Click the Next link to preview your labels. You should see the first three certificates populate with data from your source file.
- If everything things correct on the screen, click Complete the merge.
The last pane also has a link to edit individual labels. I always use this feature. I would’ve edited some of the certificates that had 2lb to 2 lb in this example. The other advantage is this link opens your certificates up in a new document you can save.
As many times as I’ve created gift certificates for friends, I still make final adjustments. I like to print my first version to a PDF file and confirm everything is correct, and the dotted table lines don’t show. The good news is the layout is easy to adjust.
After talking to friends who have used this tutorial, some have also added a column to the Excel spreadsheet for “Redeemed.” Others opted to wrap text around their picture. And some have told me they use Google Sheets as the source.
Making those types of changes is easy. The hard part is finding someone who will cut the gift certificates for you. Some printers will do this for you if you’re not precise with scissors or a paper cutter. I can even send them the completed gift certificate file and specify the color and paper weight.