180 Certificates by 5 pm: Rescuing a Mail Merge Under Deadline
Springdale Coaching Centre, Patiala, must hand out 180 certificates at its annual function at 5:00 pm today. The office assistant has 'students.xlsx' - columns Roll No, Name, Class, Marks, Result, with 180 rows on Sheet1 and 3 late admissions typed on Sheet2 - and a Word template 'Certificate.docx' whose body reads 'This is to certify that «Name», Roll No «Roll No», of Class «Class», has passed with «Marks» marks.' She connects the Excel file at 1:20 pm and previews, then sees three faults: the Marks field for several students displays as '14-Feb' or '22-Mar' instead of 45 and 67; the three late-admission students print the literal text '«Name»'; and one entry prints as 'GURPREET KAUR' while all others are title case. She prints ten test copies on the office inkjet: on every one the decorative border is clipped 2 cm on the right and the certificate spills onto a second page. The principal's laser printer is A4 only and has 40 sheets left. No internet; no PDF printer installed; deadline in 3 hours 40 minutes.
1. Diagnose all three merge faults and state the correct fix for each, saying clearly whether the fix belongs in Excel or in Word and why.
Read the worked answer
Fault 1 - Marks showing '14-Feb'. Excel is storing those cells with a Date format, so 45 is displayed as a date serial. Fix in Excel first: select the Marks column, Format Cells > Number, 0 decimals, save, then in Word re-select or Refresh the data source - Word caches the connection and will keep showing the old values until it re-reads the file. If a stray date-stored cell survives, use a field switch in Word to force the format: { MERGEFIELD Marks # "0" }. Forcing the format in Word alone hides bad data rather than repairing it, so Excel first.
Fault 2 - late admissions print '«Name»'. The merge is connected to a fixed range on Sheet1 only; the three records on Sheet2 were never in it, so those fields have no data and the placeholder text is left on the page. The merge does not invent records. Fix in Excel: move all 183 records into one contiguous table on one sheet (or define a named range covering them), then reconnect the data source. Never retype names into Word.
Fault 3 - 'GURPREET KAUR'. The capitals are in the source data; the merge simply copies what is stored. Fix in Excel with a helper column =PROPER(B2) and merge that column - and check the three unusual cases by eye, because PROPER also turns 'Mc' and initials into odd forms. The Word field switch * Caps capitalises the first letter of each word and is an acceptable fallback, but the source should be cleaned.
2. Fix the printing so 180 single-page certificates come out correctly with only 40 laser sheets and the inkjet available. Give the specific settings and explain the reasoning behind the border clipping and the second page.
Read the worked answer
Why it is happening.
- Border clipped 2 cm on the right: the page is probably set to Letter while the printer holds A4, or the border sits inside the printer's non-printable margin (usually 5–6 mm). Content wider than the printable area is silently cut, not scaled.
- Spilling to page 2: almost always a trailing empty paragraph or an oversized table row after the border, not genuinely too much text.
Settings. Page Layout > Size A4, Orientation Portrait, Margins Custom with at least 10 mm on all sides; drag the border inward rather than shrinking the text; delete the empty paragraph after the certificate; if a table is used, untick 'Specify height' and allow rows to break. Insert a page break at the end of the certificate so each record begins on a fresh page. Print 2 test copies before the run.
Production plan. 180 certificates need 180 sheets; the laser has 40. Print 2 test copies on the laser, then run the bulk on the inkjet in batches of 20 with a visual check after each batch - check ink level and whether a spare cartridge exists before starting. Use the laser for the first 40 or for the chief guest's set. Keep the merged output as one Word file and merge to a new document before printing, so a failed run can be reprinted from a defined record number without re-merging.
3. She has just over three hours and is tempted to fix the Marks column by typing the numbers directly on the merged certificate pages. Explain why that is the wrong repair, and give a five-item pre-print checklist that would catch these faults in future.
Read the worked answer
Why typing on the merged pages fails. The merged document is a snapshot; correcting 15 pages by hand does not fix the source, so anyone who re-merges gets the dates back. It also breaks the audit trail - the certificate no longer matches the results register, which is exactly what a parent would query. And hand-typed corrections across 180 pages are guaranteed to introduce new errors at the speed she is working. Any repair must go back to the Excel source, then re-merge.
Pre-print checklist (five items).
- One table, one sheet, contiguous rows, no blank rows or stray totals inside the data; select the whole range and confirm the record count matches the admission register (183, not 180).
- Set column formats before connecting: Marks as Number 0 dp, Roll No as Text if it has leading zeros, names cleaned with PROPER.
- Preview the first, a middle and the last record - never trust the first record alone.
- Print exactly two test pages and hold them against a printed certificate template: check border, margins and that it is one page per record.
- Confirm the data source path and keep the .xlsx and .docx in the same folder, so the link does not break when the file is opened on another machine.