Hello all,
Today I will show you how to create quickly and effectively
multiple labels, name tags, certificates, report... and other documents at once
using information stored in a list, database, or spreadsheet
saving you the time and effort of retyping the same letter over and over
You only need to have the following 2 file types available.
The first is a data source file like a Microsoft Excel spreadsheet or a Google Sheets file
The second is a Template file in Word document format.
I will also briefly show you how to design certificates and report cards like this using Canva and PowerPoint.
However, so that you can practice quickly,
I will attach all the files including the data files and designed template file used in this video
at the description box of this video
Click on the video's timeline to go to the part that you're interested in
The requirement of file types before performing Mail Merge that is,
For Excel data files, the first row should be the header row, make sure that you have labels on the top of every column of data
The convention is to organize the data as one record per row, as shown in the following table
Save your Excel file with your data and note the location where you save your Excel document
Requirements for the template file, you can create it directly in Word,
or find beautiful templates on Google
or design it yourself using other design tools
In the video I will briefly show you how to design with Canva and PowerPoint
However, no matter which tool we use, the final file format we need is the Microsoft Word file format
And don't worry no matter which tool you choose to use, I will show you how to convert it to Word format
First, we try to create a batch of certificates that are personalized for each recipient
First of all, you need to have a certificate template,
which can be searched on Google or Canva using some of the following suggested keywords:
If you want to design your own to ensure copyright issues when using certificates,
you can use Canva
If you've never used Canva before, check out my complete video tutorial first.
And now I will show you how I designe a certificate with Canva quickly
On the homepage, click Create new design,
on search box, type "certificate"
and choose a landscape or portrait design paper size.
Fill the background color
go to Elements, choose Lines and Shapes, find a Cloud Shape,
fill it with white, then create another cloud shape, fill it with white too,
turn on shape border option, reduce border weight and change the border color, use this shape for decoration
go to Elements, type the following keyword to find the title banner
for decoration you can insert stickers from the Canva library or upload from your computer
if you have your own logo, you can also upload it to insert here
Go to Text part, to insert the title text boxes and content,
leave space if you want to sign or insert the signature later.
Remember to leave a space to insert later the person's name on the certificate
After the design is complete, we will now download the file.
However, the problem is that Canva at this time does not support downloading designs in Word format
Then you just download the design page in PDF format
If you have a PDF file, you can convert it from PDF to Word using the online converter
Or if it's fastest and most convenient, use Microsoft Word itself to convert PDF files
Just open Microsoft Word, select Open,
select Search and navigate to the folder where you saved the PDF design file downloaded from Canva.
The window informs that the file conversion process is about to take place,
fast or slow and the result depends on the original PDF file
As is the case with this file, the conversion is quite good,
the layout and images, the text box are almost the same,
if the layout or font you choose in Canva when converting has an error after converting
you can turn on Enable Editing mode to manually fix the layout or replace the font that your computer has
Now that I have the design file in Word format as desired,
I will go to Insert, choose Textbox,
to insert the text box at the place where I need to enter the name here
To make it easier to control the text box, Right-click the text box, select Wrap Text to change it to Square, Tight or In Front of Text...
Double-check the Excel file, make sure that you have labels on the top of every column of data, organize the data as one record per row
If it's ok, go back to Word, click on the Mailings tab, select Start Mail Merge, and click on Normal Word Document
Continue to the Select Recipients tab to select the data source,
because we already have the Excel data file available, so you will choose Use an Existing List
then find the folder where the Excel file was saved earlier.
If the Excel file has multiple sheets, you need to confirm which worksheet in that Excel file you want to link the data
remember to check the selection here to confirm that the first row of the worksheet is the column header
click OK. It looks like nothing has changed, but in fact some Mail Merge buttons do work now.
Click to select the text in the textbox
Then on the Mailing tab, select Insert Merge Field
This is the information you can insert,
it corresponds to the columns of information you have created in the Excel file such as name, date, gender, etc.
We only need the information that is Full Name only, so you click FullName.
Click on the textbox to center, increase the font size, change the font and change the font color to suit the design.
Delete the border of the text box. You can now click here on the Mailing tab
to preview the results of the automatic name filling in the certificate
This is the first person's name in the Excel file.
Click Next to continue to see the certificates of the next people,
how many rows of data in the Excel list will have as many certificates
For example, if you now change your mind, you only want to create certificate for some people in the Excel list,
then you can click Edit Recipient List to adjust the list again
This is the data table from your Excel file.
Click on the names of the people who need to be excluded from the list of creating certificates this time.
After all has been checked , click Finish and Merge to complete
You can choose to export the certificates or print them or email them
When you choose to export file, you can choose to create certificate file for everyone on the list,
or just the current page, or a person's certificate from number to number
Then you see a new file appear.
In this file there will be many pages, each page is a certificate for each person. Remember to save this file.
And if you want to print the certificates, please click here
and also specify whether you want to print all or print individually according to your choice.
In case you want to email to each person with their certificate, then click here and follow the instructions,
if you need further instructions about mail merge and sending multiple mail, I will show you in another video to avoid confusion.
As you can see, making certificate is quite simple because there is only 1 information field to fill is text.
Now I will guide you with examples of making report cards,
because it will have more types of data to insert so you know what to do if you have trouble with common formatting errors
First of all, to have a report card like this, you can edit it yourself on Word
or use the following suggested keywords to find templates available on Google and Canva
If you use Canva, see how to convert from PDF to Word download format as I instructed at the beginning of the video.
As for this report card template, I designed it myself on PowerPoint.
If you want to do the same vertical page format as me
then create a new PowerPoint file,
in the Design tab, select Slide Size, select Custom Slide Size
and choose A4 size and vertical slide format
Continue, fill the slide background color,
insert the school logo or a few decorative stickers in the corners,
add the title text boxes, the information sections of the student's name, teacher's name, class, date of birth, etc.
Below is a table of scores for each subject,
you can add an average score box of all subjects,
and also add a box for conduct and attendance score
adjust the content according to your needs.
To export the finished design file in PowerPoint,
go to File, select Export choose to create a PDF file format, specify and remember where to save the file.
Now open Microsoft Word, select Open, find the PDF file you just created
and Open to perform the process of converting it to Word's format
Now have the Word file as desired.
Continue to Select Insert Textbox to insert text boxes in the places where you will need to fill in information.
You can right-click on the text box, select Wrap Text to choose whether the position type of the text box is Square or In Front of Text...
There are empty text boxes and now you choose the Mailing tab,
select Start Mail Merge, and click on Normal Word Document.
Continue to select Select Recipients to choose the data source,
select the Excel data file available on the computer as the first example has done.
Confirm the sheet with linked data is sheet 1, click OK.
Start filling in the data. Click to select text in the textbox Student's name.
In the Mailing tab, select Insert Merge Field /fi:ld/, select the corresponding information to insert, which is Full Name
Similarly, in the Teacher text box, Insert Merge Field is Teacher,
in the Class text box, then Insert Merge Field is Class.
Enable Preview Result mode to preview the results one by one.
However, when you insert mail merge for the date of birth value,
when the preview is enabled you may find it is displayed incorrectly formatted or reversed.
For example, in the Excel file the format is day/month/year, and here it is converted to month/day/year or the month is written into words.
To fix it, right-click on the inserted value, press the Shift F9 keyboard shortcut or select Toggle Field Codes
drag the text box to see it all, next to the closing bracket of the code, type the following formula
this code is meant to reformat the data to day day, month month, and year displaying 4 characters
Done, right-click again on the code you are entering, select Update Field to update the new format.
So the date format has been corrected, resize the text box like the original.
You only need to do this once, all other records will also sync
To the sections that insert scores for each subject, you continue in turn Insert Merge Field the value of that subject
Also Insert Merge Field for the conduct score, attendance score, the average score of all subjects
for the percentage format of attendance score,
you should leave the integer number format in the Excel spreadsheet and then manually add the % symbol after Insert Merge Field
If you choose the % format in Excel from the beginning, you may get an error when linking data
In addition, for the format of the GPA, you may encounter the problem that the decimals show too many numbers behind
You should use the Round function to round the number in the Excel file first,
if there is still an error, you can fix it by, selecting the inserted value, pressing Shift F9 or Toggle Field Codes
next to the closing parenthesis, add the following formula: left slash, pound sign and 0 if you want to round to an integer
0.0 if you want to round to the tenth, 0.00 if you want to round 2 letters decimal.
Done, right-click on the code, select Update Field to update all data in the new format.
Finally, you adjust the text boxes that have inserted data,
delete the background color of the text boxes, delete the Outline border around the text box
change the font size, align the margins and position, change the font or font color
Turn on the results preview and get a quick glimpse of how the filled out report cards will look like.
If the student list data has changed, click Edit Recipient List
In the data table from the Excel file, click on the names of those who you do not want to export the grade report,
or update again according to the latest changes of Excel data file
To complete, click Finish and Merge. Select here to export to a Word file
Choose whether to export all report card or just those from number to number
You will have a multi-page Word file, each page is 1 report card of 1 person
If you have connected to the printer and want to print the report card, select here,
and also specify whether you want to print them all or print them individually.
That's how I showed you how to design a certificate, report card with Canva, PowerPoint,
how to bulk and automatically fill in the certificate, report card, from Excel to Word with Mail Merge
Mail Merge is a very useful and important feature,
if you are interested, wondering about similar techniques ...
such as making label, name tags, letter covers, etc,
as well as fixing common errors when doing Mail Merge, please leave a comment to let me know. Good bye and see you again