How To Make Mailing Labels From A Spreadsheet Using Open/Libre Office
Jim Avera (jim.avera [at] gmail.com)
Let's get this out of the way right now:
The procedure is horrible for non-technical users.
With Open/Libre Office you get fabulously more than you pay for,
but that does not include a specialized application for printing labels
from a mailing list.
Instead, a combination of low-level features give us what we need.
First, Upgrade Libre Office to 4.x
Earlier versions of Libre Office had bugs which positioned labels incorrectly.
In Libre Office,
do to see which version you have.
On Windows or Mac, download Libre Office 4.x
On Ubuntu 12.04 you have to use a special repository to
get Libre Office 4.0 (as of May 2013):
sudo add-apt-repository ppa:libreoffice/libreoffice-4-0 # once only
sudo apt-get update
sudo apt-get dist-upgrade # or sudo apt-get install libreoffice
Can I use Open Office?
Yes. These instructions were tested with OO 3.4.1, but the page size defaulted
incorrectly (set it manually to A4 or Letter etc. at
, Page tab).
Be sure to select "Synchronize contents" when creating
the mail-merge file, and
insert the "next record" token at the end of the label (these things are
Overview of the Mail-Merge Procedure
- Create a spreadsheet with your mailing list (name, address, etc.)
- Create a dummy database to interface the spreadsheet to the mail-merge machinery.
- Create a mail-merge document containing a grid of labels.
- Edit the mail-merge doc to format labels using db field references.
- Process the mail-merge doc to generate another text document
with plugged-in values from the spreadsheet.
- Print the last-mentioned document onto labels.
How-to in Detail
Put your data in a spreadsheet with column titles in the first row
(e.g. "Name", "Address" ...)
We will use the file name "mailinglist" as an example.
Any supported file type is okay (.ods,
Create a dummy database which interfaces the spreadsheet to the label generator:
The "mail-merge" machinery only works with data from a database;
it can not read a spreadsheet directly.
You must manually create a separate "database" file to serve as a conduit.
This "database" won't contain any data, but will obtain data from the
spreadsheet when needed:
- Be sure the spreadsheet has been saved
- Start Open/Libre Office if no document is open.
- Click "Connect to an existing database"
- Select "Spreadsheet" in the drop-down menu.
- Browse to your mailing-list spreadsheet.
- Again browse to the folder containing the spreadsheet and choose
a related name for the database file, for
example "mailinglist_db" (an .odb file
will be created).
Create a mail-merge text doc containing labels.
A special procedure creates a grid of similar labels:
- Start Open/Libre Office if no document is open.
- On the Options tab, select "Synchronize contents".
- On the Labels tab:
Select the label brand/category in the "Brand" menu
(most people in the U.S. will use "Avery Letter Size"),
and a specific product number in the "Type" menu.
You can also specify raw dimensions on the Format tab.
- In the "Database" drop-down menu, select the database you just created.
In the "Table" menu, select "Sheet 1" (or the appropriate sheet).
- Do not enter label fields now, but wait until after the document
is fully created (this avoids a problem with line-breaks
which interferes with suppressing empty lines).
But if db fields already appear, just leave them as-is.
- Click "New Document" to create the label mail-merge document.
- Save the new document (
Once again browse to the folder containing the spreadsheet and choose
a related name, for example "mailinglist_mmerge"
(it will be an .odt file).
The remaining steps can be done after closing and re-opening the
Edit the mail-merge doc to format labels using db field references
Note: Edit only the first label on the page at the upper left,
then click the floating
to copy your changes to the other label positions.
When editing label text, be sure to click inside the label border
(when the cursor has an I-beam shape).
If you click the border itself then the entire frame object will
be selected, indicated by green resize handles. You don't want to change
the frame in any way, so if this happens click outside the page
to de-select the frame.
- Insert "database fields" where data values should be merged in.
Note: The field names which you're about to insert may be too long to display
in the label box. To make editing easier, it may help to
temporarily apply a very small font size such as 6 pt to avoid line-wraps
(try Control-A to select all paragraphs before
changing the font size).
Then use the "zoom" control at the lower-right corner to magnify the display.
- In the left Data Sources pane, navigate to the database created earlier
and appropriate sheet name (might be done automatically).
- Drag column headers ("Name", "Address" etc.) from the
Data Sources area to where you want them in the first label of the document.
At the end of each line, press Enter/Return and a "Paragraph" Pilcrow
symbol (¶) should appear. Don't be confused if long db field names
wrap around - they will be replaced with data from your spreadsheet,
and the line will wrap only when necessary.
Insert appropriate spaces and
comma between fields to form a proper postal address.
The result might look like this:
mailinglist.Sheet1.FIRST NAME mailinglist.Sheet1.LAST NAME¶
mailinglist.Sheet1.CITY, mailinglist.Sheet1.STATE mailinglist.Sheet1.ZIP
Click the floating button
to update other label positions.
Note: If the database fields were previously inserted, just insert
paragraph breaks and spaces etc. between the existing fields as needed.
Any LINE BREAKS (indicated by ↵ left-arrow symbols) must be deleted;
lines should be broken only by starting a new paragraph.
To delete a LINE BREAK, place the cursor immediately after it and
press your Delete key.
While editing, keep the special Next record
token at the end, after the last db field reference.
See "Trouble Shooting"
if this token gets deleted or mis-placed.
- Mark paragraphs to be hidden if their data field(s) are empty:
In the example above "ADDRESS2" might sometimes be empty; in that case we
want to to avoid inserting a blank line in the output. To accomplish this feat, the
paragraph containing mailinglist.Sheet1.ADDRESS2 should be conditionally hidden if
the ADDRESS2 data field is empty for a particular label:
- Click immediately before or after "mailinglist.Sheet1.ADDRESS2" to prepare
to insert. Be careful to not select the db reference itself
or you'll end up replacing it.
(a "Fields" dialog opens)
- Click the Functions tab, then Hidden Paragraph
- Enter an expression in the "Condition" box like this:
Drag the "ADDRESS2" column header from the Data Sources area
to the "Condition" field in the Fields dialog
to display Data Sources).
Then insert an exclamation
point in front of the resulting database reference.
You should end up with something like this:
once only, then
You should see Hidden Paragraph
inserted into the label (visible only with "View->Field Names").
Add margin spacing: The boxes in the grid correspond
to the physical edges of the labels, so the text must be inset from the
edges to avoid registration problems while printing:
Add top margin: Click in the first paragraph (i.e. the top line) in the label,
and set "Spacing Above paragraph" to 0.1";
Add left margin: Select all the paragraphs (lines) in the label,
and set "Indent Before text" to 0.18" or similar; Click .
Finally, adjust the font size so all lines fit, taking into account whether you
expect any data values to be long enough to cause line-wraps.
It's helpful to disable "View->Field Names" at this point to reduce clutter.
Select all paragraphs (all lines) in the label, then use the font-size
drop-down in the tool bar to set an appropriate size.
Note: You can type in fractional sizes which aren't in the menu, for example "11.5".
- Click the floating
- Save your work (Control-S).
Now you have a mail-merge document which contains one page of labels,
with database-field references where the actual data will be plugged in.
Process the mail-merge doc to generate another text document
with the final label images.
Print the final document onto your labels.
- Open the label mail-merge document (mailinglist_mmerge.odt in our example)
if not already open.
A pop-up will ask "Do you want to print a form letter?"
— click .
- A "Mail Merge" window will appear:
Navigate to the same folder as before and enter
a related name, for example "mailinglist_forprint".
- Check "All" records
- Check "File" instead of "Printer"
- Check "Save as a single document"
- Open the new document (mailinglist_forprint.odt) and look it over.
If there are missing, duplicated, or scrambled names, see
If you export to PDF instead, be sure to
un-select "shrink to fit" or other scaling options when using it
to print labels.
- If blank lines appear in the middle of labels:
- Re-visit the procedure
to hide paragraphs which contain empty data fields.
A "Hidden Paragraph" token must be inserted in each paragraph which
might have no data, using an appropriate "Condition" expression.
Note: If a line contains multiple db fields, and all of them might be
empty, then the "Condition" expression should be something like
![mailinglist.Sheet1.FIELD1] && ![mailinglist.Sheet1.FIELD2]
Also make sure there are no hard line breaks; a new line should be started
only by starting a new paragraph. To verify this,
and look for paragraph symbols (¶) and verify that no
line-break symbols (↵) are present.
- If the same address is printed on every label on a page:
- The "next record" token is missing.
In Libre Office this token is provided automatically, but
in Open Office you must explicitly insert it. It is visible only
with "View->Field Names" in effect.
To restore the "next record" token:
- Edit the mail-merge file (mailinglist_mmerge.odt in our example).
- Temporarily reduce the font size to 6 pt as suggested above.
- Place the cursor just after last database field in the first label
Click the Database tab,
Click "Next record",
- Restore the desired font size if you reduced it temporarily.
- Click the floating
- If parts of different addresses are mixed together:
- The "next record" token is in the middle, rather than after the last db field.
This causes the first part of one record to be combined with the last part
of the next record. Remove the token and re-insert it at the end, as described in
the previous item.
- If some records are skipped:
- Probably there are multiple "next record" tokens present.
- I don't see any "floating
It's in a separate small window which can appear anywhere on your screen,
and might be hidden when the document doesn't have input focus.
Note: When the mail-merge file is first created with
It it wasn't, you'll have to re-create the file from scratch.
the "Synchronize contents" option must be selected on the
- I moved my files to another folder or computer and now nothing works!
- The connections from the mail-merge file to the dummy database, and from
there to the spreadsheet use absolute file paths. That means that if the
files are moved to a different location the connections stop working.
To fix the database file (mailinglist_db.odb in our example), open it and use
change the path to the spreadsheet document.
Use to verify the path.
To fix the mail-merge file (mailinglist_mmerge.odt in our example), open it and
and then browse
to find the database file. It should then appear in the "Available Databases"
panel. Click the triangle to view the sheets and select "Sheet1" (or as appropriate).
Feedback / Contact
Please email comments & corrections to jim.avera at gmail.com.
This guide was developed using Libre Office 184.108.40.206 on Ubuntu 12.04 LTS x86_64
Relevant Open Office documentation is here.
Last updated Mon Oct 27 05:12:01 2014 jima.