Generate certificates from spreadsheet data, automatically

How to create certificates from the spreadsheet?

You won't have to go through each credential separately – because, with Certifier you can auto generate certificates from Excel. Each recipient's unique details are automatically added to your certificate. Ready to turn your Excel into certificates? Follow the steps below to create certificates using the spreadsheet (Google Sheets and Excel file included).

Step 1: Prepare the spreadsheet file

Firstly, let's prepare the spreadsheet from which Certfier will take all the data needed to generate certificates. You don't need to create a new file or clean anything in your original spreadsheet. If you’re using Zoom/Microsoft Teams or LMS (Kajabi, Teachable, etc.) you can download recipients’ data directly from the tool without the need to create a spreadsheet from scratch.

Be sure to include all the essential information you want in your certificate. The two must-haves are recipient name and recipient email – these bits are crucial to automatically generate a certificate and guarantee it’s personalized for the right person. When you're choosing which columns to use, think about what's going on in the certificate. Name and email are a given, but if you've got that extra data points, like grades, make sure to include those columns too.

Save your spreadsheet in a format Certifier likes (CSV, XLS, or XLSX are all good to go).

Does your spreadsheet look more like this? With first name and last name being in separate columns?

Don’t worry, you don’t have to do much. The most vital point is to combine first and last names together as they appear in the recipient's name section on the certification. If you need some help here, download our spreadsheet template to be sure that all the data are well-prepared.

You can also read more about how to prepare the spreadsheet here.

Step 2: Prepare a certificate with dynamic attributes

This is how you make your data from the spreadsheet show up looking sharp on the certificate. Certifier provides dynamic attributes – the text box that allows you to insert personalized content to each certificate based on the data from the spreadsheet. Thanks to that, you can create one certificate template to generate multiple certificates with different names, grades, etc.

In Certifier, go to the Designs dashboard, create a new certificate design, choose a template, and go to the Attributes tab. From there, you can manage all your attributes – remove and add the ones you need. If you would like to add content other than the defaults, e.g., grade, you can do that via custom attributes.

Click the Add Custom Attribute button.

Name the attribute and press Create.

Choose by clicking on the Use next to the right attribute or type in the attribute tag. A placed attribute will change to In Use status.

Keep your attribute design consistent – colors, fonts, sizes should all be in harmony with the certificate design. You can upload new elements, change fonts and colors, the sky is the limit here.

You can also generate a QR code, which leads readers to the digital wallet with the option to share, manage, and download the template. Or, if you want to, you can create a QR code that leads to the custom URL, e.g., your website.

Once you’re happy with the design, save the template.

Step 3: Upload recipients’ data

Alright, time to upload the spreadsheet into Certifier. When you click Save, a window with uploading data will pop up.

If it’s not your first certificate design, you can manage your recipients' data from the Credentials dashboard. Name the certificate group and choose Via spreadsheet upload. Select the prepared file and add the recipients.

Pick the columns you want to take data from.

👉 If you’re organizing an event and collecting your recipients' data via forms, it’s possible to generate certificates based Google Form’s responses. Read our article on how to generate certificate from Google form!

Step 4: Map the attributes to the spreadsheet columns 

Certifier is pretty smart – it'll help you map your spreadsheet columns to the right places, so names go with names, emails go with emails, and grades go with grades. Map each column to its attribute. When you see a column that you don't want to include, just click the skip column. Double-check to verify everything is in order.

And if there's a hiccup, like a typo in an email, Certifier's got your back and will help you fix it. Towards the top, you'll see a yellow notification bar that indicates what's wrong. Filter your records using the Show invalid rows option.

Click the edit option in the Actions column. Fix the errors until you see the green notification bar saying: “Great job. You are ready to go!”.

Step 5: Preview and publish

You're almost at the finish line! Before you auto generate certificates from Excel, take a moment to preview your certificates by clicking Preview Before Publishing. You can easily switch between certificates with the dropdown list: Preview for.

This is your chance to make sure everything looks perfect. Check that all the custom attributes are showing up right and that there's no problem with the design in the certificate editing app. Once you're happy with how everything looks, go ahead and publish.

Step 6: Monitor and track the performance

Now you can review and verify how your recipients engage with certificates. Check certificate status (delivered, shared, unopened), track engagement metrics, and download reports.

For example, you can easily notice that somebody didn’t open an email or forgot to share their certificate on LinkedIn and react quickly to reach out to them again.

The Certifier automatic certificate generator also provides a more detailed Analytics dashboard which can be used to your advantage. They will tell you how to plan your future campaigns, how many people download the credentials as PDFs, or how the engagement is spread across particular social media platforms.

Step 7: Engage the recipients again if needed

Once you’ve explored the engagement metrics, you will know whether or not you need to resend your certificates. Luckily, Certifier offers such a function, and you can easily remind your recipients about their certificates with just one click.

Certifier has three types of customizable automatic emails:

• Delivery message with the certificate and a link to a digital wallet

• Expiration date reminder

• Final email about the certificate’s expiry

You can export all the credentials as a ZIP file with PDFs or as a spreadsheet with all the data, digital wallet links, engagement, and more.

Go to the Credentials tab, choose the certificates you want to resend, and click the Resend button. Now, your recipients will no longer be able to resist your credentials.

Good to know: Each certificate email design can be adjusted to your branding. You can add your own company’s logo and colors, change the message, and edit the CTA button. Go to the Emails tab and make the magic happen!