How To Make Mailing Labels From A Spreadsheet Using Open/Libre Office
Jim Avera (jim.avera [at] gmail.com)
Last updated Tue Mar 17 00:03:16 2015
Have you suffered from
Open/Libre Office Label-Maker's Madness Syndrome?
This might provide some relief.
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 if necessary
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
Recent Linux distributions come with Libre Office 4.x. On an older Ubuntu 12.04 you can use a special repository like this:
sudo apt-get remove 'libreoffice*'
sudo add-apt-repository ppa:libreoffice/ppa # once only
sudo apt-get update
sudo apt-get install libreoffice
Can I use Apache 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.
- Check "Yes, register the database for me".
Un-check "Open the database for editing".
- Again browse to the folder containing the spreadsheet and choose
a name for the database (.odb)
file. You can use a related name such "mailinglist_db", but
using a very short name makes it less cumbersome to edit the
label layout later. We will use "db" in this example.
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.
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 Synchronize Labels button out of the way.
to copy your changes to the other label positions.
You may need to move the
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.
If you unintentionally change something,
Control-Z will un-do the most recent change.
- 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 (¶) 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:
db.Sheet1.FIRST NAME db.Sheet1.LAST NAME¶
db.Sheet1.CITY, db.Sheet1.STATE db.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.
Don't be confused by wrapped long lines -- if you don't see a ↵ symbol then there is no line-break present.
To delete a LINE BREAK, place the cursor immediately after it and
press your Delete key.
While editing, a special Next record
token may have been automatically inserted after the last db field reference; be
sure to not insert anything after it.
See "Trouble Shooting"
if this token is missing or 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 db.Sheet1.ADDRESS2 should be conditionally hidden if
the ADDRESS2 data field is empty for a particular label:
You should see Hidden Paragraph
inserted into the label
(visible only with ).
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.10";
Add left margin: Control-A to select all the paragraphs in the label, then
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.
- On a Mac, check that a Java JRE is enabled under
(if Java is not already installed, downloaded from java.com).
- 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
![db.Sheet1.FIELD1] && ![db.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 Pilcrows (¶) 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).
- Place the cursor just after last database field in the first label.
If line-wraps make it difficult to put the cursor where you want, temporarily reduce the font size and increase magnification as suggested above.
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 (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 220.127.116.11 on Ubuntu 12.04 LTS x86_64, and re-tested using Libre Office 18.104.22.168 on Ubuntu 14.10.
Relevant Open Office documentation is here.