How do you set up a spreadsheet of names for printed certificates?
020 3375 6674 info@silverimagelondon.com Open 24 hours, 7 days a week — call or email and we will tell you what is possible today.
A spreadsheet of names for printed certificates needs one row for each certificate, one piece of information in each column, and a first row of column names such as Name, Date, Grade and Serial. Silver Image Print & Signs merges that list into one master design, supplied as one PDF at A4 or A3, so every certificate prints with its own details in a single run. Enter each value the way it should print, then check the proof for spelling, accents, capital letters and the way the date is written. The rest of this page covers the columns, whether the name goes in one column or two, and the spreadsheet habits that most often spoil a merge.
Written by Maciej Antosz, Production Manager at Silver Image London ·
Personalised certificate printing works from two files: the master design, which is the same on every copy, and the list, which is different on every copy. The design is artwork; the list is data. Keeping the two apart is what lets eighty certificates be set from one layout instead of eighty files, and it is why the list is sent as a spreadsheet and not typed into the artwork.
Which columns does a certificate spreadsheet need?
A certificate spreadsheet needs one column for each detail that changes from one certificate to the next, plus any clearly named column needed for sorting or for identifying a row. On our certificate specification the usual columns are the name, the date, the grade and a serial number; a course title or a job title is added only where the design prints it. Every column has a name in the first row, because the column name is what links the column to its place in the design.
| Column name | What goes in it | How to type it |
|---|---|---|
| Name | The recipient’s name, as one entry | In full, with the spelling, accents and capital letters it should have on the certificate |
| Date | The date printed on the certificate | As text, the way it should read, for example 17 November 2026 |
| Grade | The result or level, where the design shows one | The same wording on every row that shares the grade |
| Serial | A number unique to each certificate | As text, so that 000123 keeps its zeros |
| Position or Course | A job title or course title, only if the design prints it | Exactly as it should print |
| Name | Date | Grade | Serial |
|---|---|---|---|
| Amelia Hartley-Ross | 17 November 2026 | Distinction | 000123 |
| Zoë Ray | 17 November 2026 | Merit | 000124 |
Should the name go in one column or two?
The name can go in one column or be split into first name and surname; it depends on how the design uses it. One Name column is the simplest choice when the whole name prints on a single line. Separate First name and Surname columns are worth having when the design sets the two parts differently, or when the stack has to be sorted by surname. Either way, each column carries a name in the first row.
After printing, Silver Image Print & Signs checks the stack against the list and sorts it alphabetically. Where the order matters, say which column it should follow: sorting a full-name column goes by the beginning of each entry, not by a separately identified surname, so for surname order the surname needs a column of its own. Names should not be split automatically at the last space, because that can misidentify a compound surname; where surname order is needed, the list should carry the surname in its own column from the start.
Which spreadsheet habits spoil a mail merge?
The way a spreadsheet stores, formats and exports an entry can cause merge problems that have nothing to do with the names themselves. The points in this table are general technical advice about spreadsheets; what actually happens to a given file depends on the software it is opened in. What they have in common is that the fix is made in the spreadsheet, before the list is sent. Changing a column to text after zeros or digits have been lost does not bring them back, so text columns are set up before the numbers are typed.
| In the spreadsheet | What can go wrong | What to do |
|---|---|---|
| Dates | Excel stores a date as a number with a display format, so a merge can receive the number or a different date format | Type the date as text, written the way it should print |
| Serial numbers with leading zeros | Entered as a numeric value, 000123 is stored as 123 | Format the column as text before typing the numbers |
| Long numbers | Excel preserves at most 15 significant digits in a numeric value; longer identifiers can lose precision | Keep long reference numbers as text |
| Merged cells | The entry sits in the top-left cell only, and the other rows can arrive empty | Repeat the entry on every row; do not merge |
| Blank rows, headings and totals | A row that is not a certificate can become an empty or wrong certificate | Keep one plain table: column names, then one row for each certificate |
| Spaces before or after an entry | Stray spaces can shift the alignment or the sort order | Remove them, without changing the spacing inside a name |
| Capital letters | Capitalisation normally follows the list, unless the design changes it | Type names as they should print; automatic capitalisation can damage a correctly spelled surname |
| Several sheets in one file | The wrong sheet can be picked up | Put the final list on one sheet and say which one it is |
How should accents and full names be entered?
Names go into the list in full, spelled and accented exactly as they should print. Two lessons come from a job recorded in our mail merge place cards case study. One name in that list carried an accented character that did not survive the export, and the card built from it came back empty; it was caught because the proof showed every name and not a sample. Another guest was listed as a first name and a single initial, which merged perfectly and was still not what was wanted on the card. The merge prints what the file contains, and it cannot tell an initial from a whole name.
The same care applies to certificates, where the name is the point of the document. On our certificate specification, names too long for the layout are flagged at proof stage, and the design should leave a clear band for the name that fits the longest one on the list.
Can the same list be used for badges and place cards?
The same layout works for other personalised print from Silver Image Print & Signs: one row for each printed item, one piece of information in each column. For ID badges that is one row per badge, with any barcode or QR data in a column of its own; for place cards one row per guest; for table plans one row per table. A certificate list can be reused for badges at the same event once the fields the badge needs are checked and there is one row per badge; a table plan needs a different structure, one row per table. The card that suits a lanyard badge is covered in which card for a lanyard name badge.
What file should the list be sent as?
Silver Image Print & Signs takes name lists as an Excel spreadsheet or as a CSV file. For a CSV file, say which encoding and separator it uses, and check accents, dates and serial numbers on the proof. Send the list as its own file alongside the master design, which on our certificate specification is one PDF at the final size, A4 or A3, with 3 mm bleed. Artwork formats are covered in what file format to send for printing, and Silver Image Print & Signs prints certificates on 300 to 350 gsm uncoated and textured boards; our stocks are set out in the paper stocks we hold.
Checklist before you send a certificate name list
- One row for each certificate — and no rows that are not certificates.
- A name on every column — in the first row, each one different.
- Names in full — spelled, accented and capitalised as they should print.
- Dates and serial numbers stored as text from the start — then checked on the proof.
- No merged cells — every row carries its own entries.
- The column to sort by — where the order of the stack matters.
- Draft or final — say which, so a list still changing is not printed as the approved one.
- The longest name — checked against the space in the design.
Q&A
Can each certificate carry its own serial number or QR code?
Yes. Silver Image Print & Signs prints per-copy serial numbers and QR codes on certificates. In the list, give each one a column of its own, and store serial numbers as text so that leading zeros are kept.
What happens if a name is too long for the certificate?
On our certificate specification, names too long for the layout are flagged at proof stage, before the run is printed. The design should allow for the longest name on the list from the start.
In what order do personalised certificates arrive?
Silver Image Print & Signs checks the printed stack against the list and sorts it alphabetically. Where a different order is needed, or the sort should follow the surname, say so and give the surname a column of its own.
Does a correct spreadsheet replace the proof?
No. A clean list removes the usual causes of error, and the proof is still where a wrong spelling, an initial in place of a name or a name that does not fit is seen before printing.
Quantity, board and finish all move the figure. Certificates are quoted on their specification on the certificate printing page, printed at our London print shop, and what drives print prices generally is in how much printing costs in London.
Send the design and the list
Send the master design and the name list with the quantity and the date of the ceremony. We check the list against the layout and flag at proof stage any name that does not fit.
SAP Business Network member registered member