Quick answer
A good mailing list file has one record per row, one piece of information per column, and a header row naming each column. Keep title, first name and surname in separate columns, give each address line its own column, put the postcode in a column of its own, and include a unique ID for every record. Save it as CSV UTF-8 so names with accents survive. Download our free CSV template below, which has the columns most mailings need and three sample rows. Then send the file to your mailing house by secure transfer, never as an email attachment.
Key takeaways
- Use one row per person or household and one column per field. Never put a whole address in one cell or merge cells.
- Give every record a unique reference (URN) so duplicates, returns and responses can be traced back to your system.
- Royal Mail's Mailmark letter specification puts the postcode on the last line, in upper case, and allows at most 2 addressee lines above the address.
- Excel strips leading zeros and turns long numbers into scientific notation. Format ID and code columns as text before you save.
- Only send the fields the mailing needs. UK GDPR's data minimisation principle says personal data must be limited to what is necessary.
Download the mailing data template
Free download · CSV · 1 KB
Mailing data template (CSV)
A ready-made header row with the columns most mailings need, plus three clearly fictitious sample rows. Opens in Excel, Google Sheets or Numbers.
Delete the sample rows, paste or export your own data under the headers and remove any columns you do not need. The optional columns at the end (salutation, version code and last gift amount) are examples of variable fields used for personalisation. Rename or add to them to suit your campaign.
The columns and what goes in each
| Column | Example | Notes |
|---|---|---|
| urn | SAMPLE0001 | Unique reference for each record. Essential for de-duplication, suppressing returns and matching responses |
| title | Ms | Optional. Leave blank rather than guessing |
| first_name | Zoë | Separate from surname so the letter can say "Dear Zoë" or "Dear Ms Example" |
| surname | Example | As the person writes it, including apostrophes and hyphens |
| job_title / company | Finance Director / Example Trading Ltd | Business mailings only. Counts towards the 2 addressee lines allowed above the address |
| address_1 to address_3 | Flat 2 / 14 Sample Street | One element per column, with house number and street on the same line |
| town | SAMPLETOWN | The post town |
| county | (blank) | Optional. Royal Mail's Mailmark specification says counties should not be printed in the address, so we use it only for selections |
| postcode | AA1 1AA | Its own column, upper case, with a space between the two parts |
| country | United Kingdom | Needed if the file mixes UK, Ireland and overseas records |
| salutation | Dear Zoë | Optional. Lets you control the greeting rather than building it from title and name |
| version_code | A | Optional. Tells us which version of the pack each record receives |
| last_gift_amount | 15.00 | Optional example for charities, used to set personalised ask amounts |
Variable fields such as the version code and last gift amount drive variable data printing. For charity appeals, a donor whose last gift was £15 might be asked for £15, £25 or £50 instead of a generic £5, £10 or £25, and that logic runs from a column like this.
Formatting rules for mailing data
Before printing, the file is checked against Royal Mail's Postcode Address File (PAF), de-duplicated and screened for gone-aways and Mailing Preference Service registrations. Our mailing data cleansing guide explains each step and why it saves postage.
We prepare your data for print
PAF validation, de-duplication, gone-away and MPS suppression, then variable data set up for personalised print, all handled by our in-house data team.
Explore the serviceHow Royal Mail expects the address to look
Your file does not have to match the printed layout, because we format the address block during data processing. But it helps to know the rules the printed address must meet for Royal Mail Mailmark letters, as some data layouts make them hard to achieve.
| Rule (Mailmark letters) | What it means for your file |
|---|---|
| Postcode in upper case on the last line | Keep the postcode in its own column so it can always be placed last |
| At most 2 lines of addressee information above the address | Name plus company is fine; name, job title, department and company will not all fit |
| No more than 64 characters per line, including spaces | Very long address lines or company names may be abbreviated |
| House number and street on the same line | Do not split "14" and "Sample Street" into different columns |
| No counties or UK countries in the address block | A county column is fine for selections, but it will not be printed |
| Only PAF punctuation, or none | Do not add commas or full stops at the ends of lines |
Getting this right matters for cost. Letters that fail Royal Mail's mandatory requirements are treated as unmachineable and are very likely to attract surcharges. See Royal Mail Mailmark explained for how the address and barcode work together.
Excel pitfalls and saving the file
- Leading zeros disappear. Excel removes leading zeros from numbers, so an ID such as 000123 becomes 123. Format ID and code columns as Text before pasting data in.
- Long numbers get mangled. Excel converts large numbers to scientific notation (1.23E+15) and has a maximum precision of 15 significant digits, so longer reference numbers are rounded. Store them as text.
- Dates change format. Excel may silently convert values that look like dates. Avoid date fields unless the mailing needs them, and agree a format (for example 2026-10-16) if it does.
- Accents turn into symbols. Save as CSV UTF-8 (Comma delimited) from the File Format menu, not plain CSV, so names like Zoë and Siobhán survive. Our template is saved as UTF-8 with a byte order mark so Excel opens it correctly.
- Formulas and hidden data. Paste values only, and remove hidden rows, columns and sheets before exporting.
Never email personal data
Send mailing files by secure transfer. We provide secure SFTP, with login credentials supplied on request, and files are encrypted in transit and at rest. Our guide to data security for mailings covers what to ask any mailing house.
Only include the columns the mailing needs. UK GDPR's data minimisation principle requires personal data to be "adequate, relevant and limited to what is necessary" for the purpose. If a field such as date of birth or phone number is not used in the pack, leave it out. Our GDPR and direct mail guide covers the rest of the rules.
