Bulk certificate generation is a no-code process similar to creating just one PDF certificate in Canva. This guide will help you to generate thousands of certificates with different variables and send them out immediately. Keep reading to find out 2 ways to generate certificates for free with Google Sheets and Excel.
But first, how to create certificates in 5 minutes?
Here's the video that shows you how:
Creating a certificate might seem a tedious task at first. But trust us – with Certifier, you can create, send, and manage verifiable certificates in one place. Certifier is your hub for professional and efficient certificate management.
Use Certifier now to drive your learning programs, courses, webinars, and events. With our fully-automated certificates workflow, you can finally focus on what matters most - your business growth.
Two best ways to generate certificates from a spreadsheet
Bulk certificate generation from a spreadsheet is useful to award webinar participants, praise conference attendees, or students upon course completion. Generating a large number of certificates in bulk with only a list of names and then sending them to recipients may seem like a hard task. However, in fact, it is a simple process. There are several proven ways how to do it with a list in a spreadsheet. Let’s learn more about them.
Way 1: Certificate generator from Excel + Certifier
This easy process takes minutes to complete. You need to prepare a list with recipient names, emails, and other values in a spreadsheet (MS Excel or Google Sheets). Once done, use a professional software Certifier for generating certificates in bulk.
Way 2: Bulk certificate generator Excel + Word + some email sender
It’s also a free, but semi-manual, amateur way to generate certificates. These tools are not designed for professional certificate issuing, but you can use them if they meet your certification needs. Read the step-by-step tutorial for this method below.
How to prepare a spreadsheet with your certificate recipients
First things first, it's time to prepare a list in MS Excel, Google Sheets, or another tool.
You'll need to add information like recipients’ names and email addresses. Your spreadsheet can include advanced recipients' information like grade, teacher name, number of hours, and issue date.
1. Download all the necessary data
As a rule, you already have recipients’ information in your LMS, CRM, or video-conference tool like Zoom. Download it in CSV or XML format.
2. Open your draft data in Google Sheets
First, separate the columns (if needed): Data > Split text to columns
3. Delete unnecessary columns, rows, and data
Remove the unnecessary information from the spreadsheet before issuing certificates (as a rule, exported spreadsheets include a lot of information and columns). Prepare as accurate list as possible to simplify the certificate generation process.
4. Download the completed spreadsheet in CSV (comma-separated values) format
That’s it. Your spreadsheet is ready for mass certificate generation! Follow the next instruction to create and send certificates via Certifier or other tools.
How to Generate certificates from a spreadsheet with Certifier (4-STEPS TUTORIAL)
As we mentioned at the beginning of the post, this is a free, easy, and professional way to generate certificates. More than 1000 companies, educators, and organizations use Certifier to generate certificates, diplomas, and credentials for their recipients every month. Follow this instruction and be among them.
Important: Before starting, you need a prepared recipient list to get started with the instruction above in this blog post.
1. Log In To Your Certifier Account
First, log in to your Certifier account. If you don’t have Certifier Account, register for free on a Sign-up Page. A Free plan is available forever for each registration.
2. Customize the certificate’s template, or design it from scratch
Certifier includes 300+ ready-to-go templates. You can use, customize, and edit them for free. Also, you can create a new design from scratch in our online certificate maker. You can upload certificate backgrounds, frames, and elements. Make sure to customize your certificate design to match your brand identity. Choose the elements for your design: upload the logo, signatures, etc.
3. Upload CSV list to generate certificates in bulk
At this step, you’ll need a prepared CSV file with the certificate recipients’ list. If you skipped these steps, get back to the instruction above.
In this step, you need to do the following:
Create a group
Upload Recipients’ List
Review the setup
Finally, save certificates as drafts (prepare them for future sending), or move on to the next step and send generated certificates.
Note: At first, you can be scared about this part of the process, but don’t worry, most users complete it in less than 5 minutes. What is more, you can upload lists automatically using direct integration with your app or software via Zapier.
4. Send bulk emails with certificates to the recipients
You're at the finish line. In this step, you need to send certificates. Click on “Save and Publish Certificates”:
When you click on it, Certifier build-in mail system emails all new certificates to recipients' Inboxes. By default, our system sends emails with a basic template (without your logo, branded colors, and text). However, you can create a custom email template, set up your email as a sender, etc. If you want to customize your email templates, go to the Emails section > Create Email Template.
That’s it. Your certificates are published and sent to all recipients. If you need help with a certificate generation process, simply schedule a call to discuss your questions in person.
Do you want to know how to print certificates from Excel? With Certifier help it's possible. Check out this guide: Certificate Printing – How to Prepare Design for Print [5 Steps].
Generate certificates with Excel + Word, and send them manually
Now it's time to describe an ALTERNATIVE way to send certificates in bulk - using MS Excel, MS Word, and Mail Merge. This method is more like a workaround than a professional way to create certificates. Still, we believe it’s useful to know about the pros and cons of it.
Generating certificates this way requires a bit more time. Follow the instructions below to proceed:
Microsoft Word has a huge library of free DOC templates including certificate templates; you can check them here. If you don't find the template that you need in the Word Templates Library, you can create your own one in MS Word from scratch or create a certificate design with our certificate maker here.
1. Open Excel & Word in Microsoft 365
First, log in to your Microsoft 365 account. Once you're logged in, open Microsoft Excel Spreadsheet and Microsoft Word program.
2. Chose certificate design from MS Word Templates Library
Microsoft Word has a huge library of free DOC templates including certificate templates, you can check them here. If you don't find the template that you need in the Word Templates Library, you can create your own one in MS Word from scratch or create a certificate design with our certificate maker here.
3. Merge the design, recipients spreadsheet, and Mail Merge
When the template is ready, you can add the logo, text, and style that aligns with your branding. After that, add a dynamic element in MS Word, and merge the spreadsheet file in MS Excel with the recipient name. Go to Mailing > Start Mail Marge > Select Recipients > Select the list with your prepared spreadsheet
4. Place variables like name and others into certificate design
Select a place on the design where you want to insert the dynamic element, e.g., the student name. In the Mailing section > select the Insert Mailed Field. You should specify a column for each variable from the recipient's spreadsheet merged before.
5. Generate a certificate and complete the process
Staying in the “Mailing” Section, click on the “Fining & Merge” button > select “All options” to generate separate certificates for the full list of recipients from the prepared spreadsheet.
That’s it. You have generated certificates in one MS World file. Now, you can keep them in one file, split them into multiple ones, prepare them for printing or send them via email.
6. Send Certificates via Outlook
If you use the full version of Office 365 Suite, you can send a simple automated email through MS Word by selecting the button “Send Email.” Make sure to select “Current Records” so that each recipient receives only their certificate.
Finally, how to generate certificates? - Pros, Tips & Sum-Up
The world changes, and so do students' needs!
The optimized certificate-issuing process is crucial for your business’s success. The best way to win engaged students or webinar visitors is to give them a good-looking professional certificate. By doing so, you will increase the value of your learning event, webinar, or masterclass.
Remember to use professional tools like Certifier for convenient certificate generation. Certifier includes such features as LinkedIn certificates, Socia Media shares, Verifiable credentials, and Professional designs.
Pros of using Certifier + Spreadsheet
1. Google Sheets (or similar spreadsheets) are completely free. Certifier Free plan also includes all the main features.
2. Using Certifier and Spreadsheet, you can generate certificates like a professional. This is the method proven by thousands of companies, universities, and organizations.
4. A complete first-time setup takes from 10 to 20 minutes, including design creation and feature adoption. Further certificate generation processes will take up to 10 minutes or can be fully automated.
5. With Certifier, you can analyze all the data about issued certificates: clicks, downloads, social media shares, etc. You can use this data to improve your marketing processes.
6. You have the option to download issued certificates as a spreadsheet with unique IDs or as a .zip archive with PDF files.
7. Using Certifier and Spreadsheet, you can integrate all your current apps and software within one workflow and automate the issuing process. No extra hassle.
Cons of using Certifier + Spreadsheet
1. Certifier + Spreadsheet method is less suitable for a single certificate or a small number of them. If you do not need to generate certificates in bulk, consider using a simple online tool like Canva. You can also create a design manually in Photoshop. After that, create separate files and manually send them to your recipients.
2. If you need to issue a large number of certificates using Certifier + Spreadsheet (250 or more), it is necessary to upgrade to the paid version of Certifier. Package prices start from $39/month, and our customers are happy with the value/cost ratio. Still, it’s fair mentioning as a disadvantage. Read the Certifier review here.
3. If you have a one-time need to issue a small number of certificates, the second method may be perfect for you (use the ready-made Office 365 solution to complete the task). It’s also a good option if you don’t need to develop a professional certification program.
FAQ about generating certificates from Spreadsheet with Certifier
Is there a difference between MS Excel and Google Sheets to create a list of certificate recipients?
If you are going to use this spreadsheet in Certifier, there is no difference. However, we recommend using Google Sheets (you don't need to download an additional desktop app). If you plan to use the “Mail Merge” feature in MS Word, make sure to use MS Excel, not Google Sheets.
Is Certifier really free?
Yes, Certifier has a completely free plan where you can create any certificate design (no “pro” design templates or our watermark on your certificate design). With the free plan, you can automatically generate and send up to 100 certificates via email. If you want to use professional features (like custom email sender, certificate management, and analytics), you can upgrade to a higher plan which starts at $19/month.
In my original spreadsheet, “First Name” and “Last Name” are separate columns. How can I merge them into one column “Full Name”?
To generate certificates with full names, you need a list which has a “full name” column. However, very often the stored list has two separate columns: “first name” and “last name”. In this case, you need to merge these columns into one.
Follow these steps to do that:
1. Create another column on the right side
2. Use the formula =CONCATENATE(A2," ",B2), and drag it down
3. Select the merged columns and use Copy/Paste in the exact same column using the “Paste only value”
4. Delete the original columns “First Name” and “Last Name”.
Share this article
WRITTEN BYSergey Butko
Tech entrepreneur. Forbes 30 Under 30 Europe. Digital Marketer.