LibreOffice – address label merge (from spreadsheet)

Posted on by rcragun

I’ve been using LibreOffice for over a decade and have only had to do an address label merge twice in that time – both of which have been in the last month. The first time, I actually did a form letter and a label merge; the second time I just had to do an address label merge. In the process, I realized that I totally forgot how to do it after the first time. Whenever that happens, I know that means I need to create a tutorial on here so I can remind myself how to do it if I ever need to do it again. So, here is my tutorial on how to merge labels in LibreOffice. (NOTE: I’m using LibreOffice 7.1.6.2.)

The idea behind a label merge, just like a form letter merge, is that you have a bunch of address information in a spreadsheet or database and, rather than having to enter all of that separately into a document to print labels, you’ll just have the software create the labels from the data you already have. LibreOffice is fully capable of doing this, but it’s not what I would call “easy” or straightforward. And, in fact, the first step will seem unrelated to merging labels, but it is necessary.

Part 1: Register Your Database with the Address Book Data Source Wizard

Before creating any labels, the first thing you need to do is create your database. I’m assuming you have a spreadsheet with addresses, like this one:

Make sure that you have column names at the top as those can become the field names in the database. It’s always a good idea to separate out your fields like I have done: first name, last name, street, city, state, zip. Of course, if you’re outside the US, your columns will be different. Adjust accordingly.

Okay, now we get to the weird part that is going to seem unrelated to merging these addresses into labels. A quick explanation may help. Basically, you need to turn this list of addresses into a database that LibreOffice has registered so it can then pull those fields when it generates the labels. This isn’t hard to do. LibreOffice will walk you right through it with a wizard. But it seems counterintuitive that you have to do this before you can start the mail merge. Just think of this as getting everything set up.

To create your database, click on File -> Wizards -> Address Data Source:

When you click on that, you’ll get this window:

This is Step 1 of 5 for the wizard. It’s basically saying, “Hey, let’s use existing data instead of having to enter new data.” Groovy. That will save us time. Unless you have your address data stored in Firefox or Thunderbird, select “Other external data source.” Then click on “Next” and you’ll get this next screen:

This is Step 2 of 5 for the wizard. On this screen, click on “Settings” and you’ll get a completely separate window:

(Did I mention that this is a bit confusing and complicated. It’s actually really smart, when you think about what the programmers did here, but it isn’t straightforward at all that you have to do all of this first. We’re only about halfway done.)

You have a lot of options you can choose from on this screen. I’m using a Spreadsheet, so I’ll leave that selected. But you can see from this screenshot that you choose from a variety of database sources:

Once you’ve selected the source for your data (again, I’m using a spreadsheet), select “Next” and you’ll get this screen:

Here, you need to choose your data source. Click on “Browse” and go find the spreadsheet you are going to use, like this:

Click on “Open” once you’ve selected it. That will take you back to the previous screen. If you want, you can click on “Test Connection.” Assuming everything worked, you should get this nice but relatively uninformative window:

Click on “Finish” and you’ll go back to the wizard:

This is Step 4 of 5 (note, Step 3 was skipped because I only had one field in my spreadsheet). On this screen, you can click on “Field Assignment,” which will let you match the fields in your imported data to the fields that are common for addresses. (NOTE: This is not required. You can skip this step if you want. It just changes the names of the fields later.) Here’s what the screen should look like:

Just to be clear, the Data source field is what LibreOffice is currently calling the newly imported data (in the image above, it just says “Addresses”). You’ll get a chance to change this later. The Table field (mine is called “Sheet1”) is the table in your spreadsheet where the address information was stored. You can then click on the various fields below where it says “Field Assignment” and align your information with the information options given, like this:

Once you’re done, hit OK and you’ll head back to the wizard.

This is Step 5 of 5. Phew. We’re almost done with this first part. You have a couple of options here. The first is a checkbox that says “Embed this address book definition into the current document.” You can unclick that if you want. Alternatively, you can leave it checked and make it part of the spreadsheet you are working on, which is fine. It really kind of depends on what your future plans are with this data. If this is data that you think you will use repeatedly for mail merges and you’ll probably be updating the data in the future, it might be a good idea to leave that checked. The second option is the one you definitely want to leave checked because we’re going to need it accessible for the label merge we’re going to do next. That option is also a checkbox that says “Make this address book available to all modules in LibreOffice.” Finally, you can name this address book. Call it something you’ll remember. I’m going with “folk artist addresses.” This changes the name that LibreOffice had temporarily assigned it (see the screenshot just above this). Once you’ve made your selections and named your Address Data, click “Finish”:

And once you hit “Finish,” nothing, right? Well, isn’t that weird.

Something did happen, it’s just “behind the scenes.” You have created a database that is now registered with LibreOffice. It’s just not showing it to you. If you want to see it, here’s how. Click on View -> Data Sources:

You’ll then see this:

You can see that I’ve got four Data Sources registered with LibreOffice. One of those is, of course, the one I just created: folk artist addresses. You can actually work with those data sources here if you’d like. Or, if you want to delete one (say, you made one by mistake or for a tutorial and you’ll never need it again), you can right-click on any of the databases and you’ll see this menu:

If you select “Edit Database File,” the LibreOffice database software will start up. If you select “Registered databases,” you’ll see a list of your databases with an option to select them and delete them, like this:

Okay. That does it for the prep work and part 1 of the tutorial. Now, with our address book registered, we can actually do the label merge.

Part 2. Label Merge In LibreOffice

Honestly, the first part is the hardest part. This next part goes pretty quick.

From any document in LibreOffice (it can be a Calc or Writer document; doesn’t matter), click on File -> New -> Labels:

You’ll then see this window:

Some explanation is in order. The big box is where you’re going to construct your addresses. I’ll walk you through that. The little check box at the top that has “Address” next to it is kind of nifty. If you’re a good LibreOffice user, when you first installed the software, you’ll click on Tools -> Options and fill in your User Data:

If you actually did that, you can click that little check box by “Address” and you’ll see your information populate the field:

This is a really slick way to, for instance, create business cards. But that’s not what we’re trying to do. So, uncheck that box and let’s get to merging some labels. First, in the drop down menu under “Database,” select the database we just created (and now Part 1 makes sense):

In the drop down menu under “Table,” select “Sheet1” (it’s the only sheet we had; we could always have named it something else). So, your window should look like this now:

Next, we start working with the drop down menu under “Database field.” In that menu, you should see all the fields you had in your spreadsheet:

Let’s build our address label. Select “firstname” then click on the little arrow pointing left:

That will insert that field into the label box, so you’ll see this:

Now, build the rest of your label, keeping in mind that you’ll want to add spaces and punctuation in between the fields, so it looks like this:

Once you’ve got it set up how you want it, we have a few other things we need to do. At the bottom, you need to choose between “Continuous” or “Sheet.” I’m assuming the Continuous is for printers that can print on labels that are connected. I typically print on individual sheets, so I select that. On the bottom right, find the “Brand” you want and then the “Type.” (Honest Aside: LibreOffice doesn’t always have every option I need for this, but you can usually find something that will work.)

We’re almost done. At the top of that window you’ll see two other tabs: “Format” and “Options.” If you want to customize the dimensions of your label, click on the Format tab. I rarely use this option, but it might be useful if you have a custom label you want to print. There is one thing on the “Option” tab that you probably want to select: “Synchronize contents”:

This will allow you to synchronize any formatting you do to the labels later. Trust me, you’ll want this option like 99% of the time. Once you’re done and have your label selected, click on “New Document” at the bottom.

You’ll then get an entirely new Writer file with labels spread out across the document and your fields highlighted in gray:

What you won’t see, unfortunately, is the actual data in those fields. That will come later. What you’ll want to do now is customize your label formatting. So, if you want to change the font, the font size, make the text bold or italicized, now is the time to do it. And, do it all in the top-left cell. In my document, I increased the font and made everything bold:

Remember when I said to choose “Synchronize contents.” Yeah, now is when you’ll want that. You don’t have to format every individual label. Just format the first one, as I said. And when you’re ready, click on the “Synchronize Labels” button in the floating window that popped up:

When you do, the formatting from the first cell transfers to all the other labels. Way easier than formatting each cell individually.

Now, to print the labels. This part is also, unfortunately, a little confusing. Click File -> Print or just hit the printer icon on the toolbar and you’ll get this message:

“Your document contains address database fields. Do you want to print a form letter?”

What an unfortunate prompt. We’re doing a label merge, so “No.” But don’t choose No! We may not want to print a form letter, but there isn’t a separate prompt for doing a label merge. So choose “Yes.” (Nudge to LibreOffice programmers – fix this.)

When you do, you’ll get this window:

This is a little confusing, but it’s basically that same Data Source window you saw in Part 1, but now we are applying it. You’ll see your Address Data Source on the top left. If you select your sheet (Sheet1 in my screenshot) you’ll see all your addresses. You have a couple of options here. You can choose not to print all of the addresses. This is in the Records option on the bottom left. If you want to test this with a single sheet, for instance, you can choose just the first XX addresses and test this out. On the bottom right, you can choose to save this as a File or send it directly to the Printer. Up to you. If you’re wary and don’t want to waste label sheets, this might be a good option. Or if you need to print these on a different printer, that would be helpful. But if you’re ready to print, select “OK” and you’ll get one more chance to change your mind because you’re finally going to see THE LABEL MERGE! (Woot! Rejoice!):

The screenshot above shows you the final window I get before I actually print the labels. And, assuming everything worked correctly, you’ll finally see the address information merged into the labels. Success!

This is not a simple process but there is, as with all things LibreOffice, a lot of customizability, which is why I prefer LO over Microsoft products.

Anyway, that’s how you do labels merge in LibreOffice. And, bonus, if you ever need to use that same address information in the future, it’s already registered in LibreOffice so you can just do this again. If not, you can delete it as I illustrated above.

 2,067 total views,  14 views today

LibreOffice Calc – VLookup with dates

Posted on by rcragun

In a different post on this blog, I showed how to use Vlookup to match lists. Someone commented on that post and indicated that it didn’t work with dates. It turns out, it does, but… There is a slight tweak required to make it work. So, if you want to learn how to use VLookup, first go to that previous post. Then come back here to see how this works with dates.

The issue with dates has to do with the format of the cells. To illustrate this, I created a spreadsheet and inserted a bunch of dates:

It’s important to make clear at this point that LibreOffice Calc, just like other spreadsheet software, has different “formats” for cells. Cells can be formatted as numbers, as text, as dates, etc. This is important because LibreOffice will treat cells with different formats differently. For instance, it’s pretty challenging to do numerical calculations with text (e.g., “apple” + 65 = ?). LibreOffice needs to know the “format” of a cell. That is the key to making vlookup work with dates.

To check the format of a cell, all you need to do is right-click on it and select “Format Cells” (or Format -> Cells from the menus at the top of the screen):

Obviously, if you’re working with dates, then the format for the cells should be “Date” and whatever specific date format you want:

Okay, back to vlookup. To find a date in a list of dates, the value you are searching for also has to be a date in the Date format – ideally, they should be formatted identically. You can see in this spreadsheet, I inserted a date to search for in my list of dates:

The vlookup formula isn’t any different. In the cell just to the right of where I inserted the date I want to search for, I added my vlookup formula. Here’s what I put into my formula:

=VLOOKUP(E1,A1:A22,1,0)

This formula calls the “vlookup” function. “E1” is the date for which we are going to search in the list of dates. “A1:A22” is the list of dates in which we are searching. The “1” after that says to use the first item in the list of items we are searching for (kind of a weird requirement). And the “0” tells LibreOffice Calc that the list is not alphabetized or sorted.

Once I put that formula in, LibreOffice Calc will search through the list to see if my date is in the list. I know that my date is there. And, typically, vlookup will return the matching date if it is there, so it should return “8/27/2021”. But, here’s what I get when I hit return on my formula:

Rather than getting the target date, I get a weird number: 44435. Knowing a little bit about LibreOffice Calc, I quickly realized what the problem is: LibreOffice Calc doesn’t know that I’m searching for a date. It found the date, but the field where it is returning the value it found is currently formatted as a “Number,” not a “Date.” See:

To show that it found the matching date, the vlookup cell also needs to be formatted as a Date. So, change the formatting for the vlookup cell:

And once you hit OK, you should now see the date it found:

In short, yes, vlookup works with Dates. You just have to make sure that the cells where the vlookup formula is located are formatted as Dates not as Numbers and you’ll see which Dates match.

 1,015 total views,  3 views today

LibreOffice – KDE Integration Package for Skinning/Color Management

Posted on by rcragun

Super simple note for future reference and for anyone else running into this issue. I upgraded my version of LibreOffice in Kubuntu 20.10 to a pre-release version to fix a bug. I was running version 7.1.4.2.

When I purged the version of LibreOffice that shipped with Kubuntu 20.10 (6.X – I don’t remember exactly which version), that also purged the KDE integration package that helps LibreOffice interface with the window manager and makes skinning LibreOffice much easier. As a result, when I would change my Global theme in System Settings, only part of the LibreOffice windows would change to reflect that theme. In particular, the sidebar on the right side of LibreOffice wasn’t changing colors with the rest of the window. This was making it impossible for me to see the names of different styles and also looked really weird.

The solution was easy. In Synaptic, search for the “libreoffice-kde” integration package and install it. Now, when I change my Global Theme, the LibreOffice windows change to reflect that.

In short, if you purge the version of LibreOffice in the repositories in KDE and install a newer version, make sure you also install the libreoffice-kde integration package or your LibreOffice windows will behave strangely.

 1,066 total views,  3 views today

LibreOffice – How To Change Icons to a Darker Theme

Posted on by rcragun

I prefer darker themes for my desktop environment (Kubuntu 20.04) and browser (Brave). For the most part, this isn’t a problem, but it does cause an issue with some applications, including LibreOffice (6.4.4.2).

One of the first things I do when I install Kubuntu is switch my desktop environment from the default theme (System Settings -> Global Theme), Breeze, which is a lighter theme, to Breeze Dark. You can see the differences in the screenshots below:

This is the Breeze theme that is the default in Kubuntu 20.04
This is the Breeze Dark theme that I typically use in Kubuntu.

The problem is with the icon set in LibreOffice. With the default Breeze theme, the icons are very visible and work great:

These are the default icons in LibreOffice 6.4.4.2 in Kubuntu 20.04 with the default Breeze theme.

The problem comes when I switch the theme to Breeze Dark. Here is how the default Breeze icons look in LibreOffice when I switch the theme:

The default icon set, Breeze, in LibreOffice when the Kubuntu Global Theme is switched to Breeze Dark.

Perhaps it’s just my aging eyes, but those icons are very difficult for me to see. The solution is quite simple, though finding it is always hard for me to remember (thus this tutorial). All you need to do is switch the icon set in LibreOffice. There are several icon sets for dark themes that come pre-packaged with the standard version of LibreOffice that ships with Kubuntu and is in the repositories. It’s just a matter of knowing where to look.

In LibreOffice, go to Tools -> Options:

You’ll get this window. You want the third option down under “LibreOffice”, “View”:

Right at the top of this window you can see “Icon style.” That’s the setting you want to change. If you click on the drop down arrow, you’ll see six or so options. Two are specifically for dark themes, Breeze (SVG + dark) and Breeze (dark). Either of those will work:

I typically choose Breeze (SVG + dark). Select the dark theme you want, then click on OK and you’ll get a new icon set in LibreOffice that works much better for dark themes:

These icons are much more visible to my aging eyes.

Et voila! I can now see the icons in the LibreOffice toolbars.

 8,456 total views,  7 views today

LibreOffice – exporting high-resolution TIFF/TIF files

Posted on by rcragun

As a scholar who regularly publishes work with charts and graphs, I’m often confronted with varied requirements from publishers for the format in which they want the charts and graphs. Most often, the format is as a TIFF/TIF file, typically with at least 300 dpi and somewhere around 1500×1500 pixels. I make most of my charts in LibreOffice Calc, though occasionally I make some in R as well.

I have recently been editing several volumes in which I had to manage the charts and graphs of other scholars as well. As the editor, I had to make sure the final images met the criteria detailed above. Since scholars often make charts and graphs in Word, it took a little finagling to come up with a quick and easy way to export the images in the format needed by the publisher. Since I did finally figure this out, I figured I’d post it here so I remember how to do this in the future. Luckily, LibreOffice works extremely well with these formats (for the most part), which makes this quite easy.

LibreOffice Calc

Assuming you have created your chart/graph in LibreOffice Calc, exporting it into a TIF format should be fairly easy, though it requires an unfortunate extra step. Here’s a chart I created in LibreOffice Calc:

The LibreOffice programmers make it so you can just right-click on the graph and select “Export as Image.”

When you do this, you’ll get this pop-up window asking where you want to save the image and, more importantly, the format:

Here’s where you get a problem. If you select TIFF, you’ll get a .tif file, but the resolution will be basically the same as what you see on your screen, like this:

Ideally, LibreOffice would ask you what DPI and resolution you want once you select the TIFF format and would then export the chart in that resolution and you’d be done in one simple step. Alas, that’s not an option when you export from LibreOffice Calc.

What you can do instead is copy your chart, open an empty LibreOffice Writer document, and paste it into the document, like this:

Then, go up to File -> Export, like this:

You’ll get the same prompt as before asking what you want to name the file and format. Name the file and select PNG format then click “Save.” What you’re looking for is the window that pops up next:

In this window, you can change the DPI to 300 (do this first) and then change the width and height (they are typically linked, so, if you change one, the other automatically changes). When you’re done, click “OK.” The file you’ll get will be 300 DPI and whatever pixels you chose:

Now, open that file with any image editing software (I’m using Gwenview on KDE for this example) and simply select File->Save As:

Now select the TIFF format. Once you save it, you’ll have a TIFF file with the proper DPI and resolution per the publisher’s instructions. The resulting TIFF file will be huge, but it will meet the criteria of the publisher:

NOTE:

The other way to do this is to copy your chart into a LibreOffice Draw file that has been modified with a huge area (e.g., 4000×4000 pixels). You can then expand your chart to file the area in the LibreOffice Draw document and then export the image. However, depending on the original format of your chart/graph, you may have to resize the text if you do this, which is a pain. However, this will give you a much larger image file. But the approach above is much easier.

The tutorial above used LibreOffice 6.4.4.2.

 5,114 total views

LibreOffice 6.4.3.2 – Not Showing Greek Letters/Symbols

Posted on by rcragun

I ran into an issue the other day that ended up taking me hours to solve, in part because I couldn’t find any other solutions online, which is pretty unusual these days.

Here was the issue: I was evaluating a paper (I’m an academic and read lots of papers) that had a bunch of Greek letters/symbols in it as part of a regression formula. On my computer running Kubuntu 19.10 with LibreOffice 6.3, the Greek letters showed up perfectly fine. On my laptop, which I had just reformatted and on which was a fresh install of Kubuntu 20.04 with Libreoffice 6.4.3.2, the Greek letters were all showing up as something other than Greek letters – odd symbols or dingbats or something. Here’s the version number from a fresh install of Kubuntu 20.04:

And here’s what was being displayed in LibreOffice with the document:

Those familiar with the Greek alphabet will clearly see that these odd dingbats or symbols are definitely not from the Greek alphabet.

I spent about three hours googling for a solution and trying various suggestions. Google is usually a Linux user’s best friend and it’s common that someone else has had the same issue or something similar. Alas, no luck this time. No one, as far as I could tell, had run into this exact issue before. The closest problems seemed to suggest that the problem wasn’t with LibreOffice but with my Linux installation and that I was missing some language packs. Specifically, these semi-related issues suggested I needed to install a language pack with Cyrillic characters. This suggestion seemed reasonable as this version of LibreOffice didn’t seem to ship with support for Cyrillic characters:

Screenshot from LibreOffice for inserting special characters; Greek is not included by default.

I installed a Cyrillic language package from the repositories and restarted my computer. Nothing. I was still getting dingbats instead of Greek letters. I tried about 10 more Cyrillic language packages thinking that maybe I hadn’t found just the right one, searching through the repositories for anything that mentioned Greek or Cyrillic. Haphazardly adding language packages doesn’t seem like a good approach, but I was getting desperate. Even so, it didn’t help. I still couldn’t display the Greek letters in the document.

Next, I tried uninstalling and reinstalling the same version of LibreOffice – 6.4.3.2, which is the version shipping with Kubuntu 20.04. That didn’t work.

After a couple of hours and no solution, I decided that I’d try a different version of LibreOffice. On their website, LibreOffice makes two additional release candidates or development versions available. I could have gone straight to 7.0.0, which was in Alpha, but I opted instead for version 6.4.4.2. To uninstall LibreOffice, I used the following commands (see here):

sudo apt-get remove --purge libreoffice*
sudo apt-get clean
sudo apt-get autoremove

To install the new version, you have to untar the files you downloaded then navigate to the DEBS folder you just unpacked, then run the following:

sudo dpkg -i *.deb

After installing LibreOffice 6.4.4.2, I opened the file that was having issues and, lo and behold, it worked just fine:

There are my lovely alphas, betas, sigmas, epsilons, and omegas!

I’m assuming this is a bug in LibreOffice 6.4.3.2 or, at a minimum, the folks who packaged that version left something out of it. Either way, I was frustrated enough at the end that I realized I needed to post a solution for others who may run into this. Since Ubuntu/Kubuntu 20.04 is an LTS (long-term support) release, having a serious bug shipping in the included version of LibreOffice is, no doubt, going to frustrate many users.

I spent a solid three hours on something that was working perfectly fine in LibreOffice 6.3 but broken in 6.4.3.2. That’s annoying. I’m a huge fan of LibreOffice and prefer it far and above MS Office. It’s mature enough software now that little regressions like this really shouldn’t happen.

 5,725 total views

LibreOffice Calc: Graphs with Two y-axes with Different Scales

Posted on by rcragun

While a bit technical, it’s occasionally useful to plot multiple data series that have very different scales in the same chart. Let me give an example to illustrate. Let’s say I want to see whether the number of Mormon temples being built aligns with the number of Mormon stakes (akin to a Catholic diocese) that are organized over time. (I’m a sociologist who studies religion; you’ll just have to go with my examples.)

However, the number of Mormon temples is in the hundreds while the number of Mormon stakes is in the thousands. If I plot them both on the same chart with the same y-axis (that’s the vertical axis), the number of Mormon temples is going to look really small and I won’t be able to see the variation over time in the number of temples, like this:

The chart shows that stakes have increased, but it looks like the number of temples has barely moved. LibreOffice Calc automatically creates the scale used for the y-axis based on the scale of the larger of the two data series, in this case, the number of stakes. Thus, the maximum value is 4,000 and the minimum is 0. What I want to do in this tutorial is to illustrate how to add a second y-axis on the right side of the chart that uses a different scale that is more appropriate for the number of temples.

To begin with, go ahead and create your chart with at least two data series, as I have shown in other tutorials, like this one. Once you have your chart with two data series complete, now it’s time to add a second y-axis with a different scale.

First, click on your chart then double-click it to open chart editing. Then, select the chart area by clicking on one of the axes (left or right doesn’t matter) and then right-click it. You’ll get a context menu with the option “Insert/Delete axes…” Select that:

In the window that pops up, you’ll see a second column labeled “Secondary Axes.” You want to select “Y axis.”

Click “OK” and you’ll see that a second y-axis has been added to your chart on the right side using the same metric as the left side:

The next steps are pretty straightforward, but before you do them, you should pause and think for a second so you don’t have to go back and undo what you’re about to do. You’re going to change the scale of one of the two y-axes, but which axis do you want to change? There isn’t a right or wrong answer here. Generally speaking, I typically see charts like this with the smaller of the two ranges assigned to the left axis and the larger assigned to the right axis, but, again, it is entirely up to you which way you choose to go. At this point, though, you need to make a decision. Then you can move to the next step.

I’m going to follow my suggestion above and change the y-axis on the left to a scale that fits with the number of temples (so, a smaller range of values) and keep the y-axis on the right with the larger range for the number of stakes. But before I change the scales of the axes, I need to tell LibreOffice which data series is going to align with which axis. Here’s how. Click on one of your data series lines, then right-click it and select “Format Data Series.”

In the window that pops up, you’ll see on the “Options” tab right at the top an option that says, “Align Data Series to” and then “Primary Y axis” (this is the one on the left of the chart) or “Secondary Y axis” (this is the one on the right of the chart). Since I selected the number of temples first, I’m going to leave that one aligned to the Primary Y axis:

Hit OK. Then select the other line (in my case, the number of stakes), right-click it, and select “Format Data Series.” On the “Options” tab, I’m going to select to align this line with the “Secondary Y axis”:

Once you do that, you’ll see that LibreOffice automatically adjusts the scale of the other axis. Here’s how my chart now looks:

You can see that it changed the scale of the Primary Y-axis (the one on the left) to a maximum of 250 to reflect the smaller range of that data series. If you want to customize the scale used, you can always click on the axis you want to modify and then right-click it and select “Format Axis”:

In the window that pops up, you can modify the scale of the axis by clicking on the “Scale” tab. If you want to change the values, click on the box next to “Automatic” to unselect it so you can put in your own values, then customize the value you add, like this:

When you have modified the scale to your satisfaction, select “OK” and your graph will be updated with the scale you want, like this:

The resulting chart now has two axes with different scales. It would be a good idea at this point to label the axes to reflect the differences. Simply right-click on the chart and select “Insert Titles.” In that window, add appropriate titles. The left y-axis is simply the Axes while the right y-axis is considered the “Secondary Axes”:

And your final graph will look something like this:

That’s how you can create a chart with two axes in LibreOffice Calc.

NOTE: This example was done in LibreOffice Calc version: 6.4.2.2 on a Linux-based operating system (Kubuntu 19.10).

 21,196 total views,  20 views today

LibreOffice Calc: Interpolating Missing Values in Graphs

Posted on by rcragun

Here’s my situation. I have some data over time but I’m missing values in specific years. I want to graph that data but would rather not have to estimate all of the missing values. It turns out, LibreOffice Calc can do that for you in your chart. Here’s how…

Imagine I’m plotting the number of congregations in the LDS Church over time (weird example, I’m sure you’re thinking, but I’m a sociologist who studies religion, so, yeah, that’s what I do). I have the number of congregations in 1841, 1849, 1901, etc. Basically, I have the number in certain years, but I’m missing the number of congregations in lots of other years. I could interpolate the missing values (Excel has this function built in; LibreOffice Calc does not, but you can do it following the approach I have detailed here). But, I don’t really need to do that for my project. I just need a chart that shows the growth of congregations over time.

My data are organized into two columns. Column A is years and ranges from 1841 through 2019. Column B is the number of congregations with the values I have and lots of blank cells:

Select the cells you want to plot (A1:B176 in my case) then click on “Insert Chart”:

You’ll get this window:

Since I want a Line chart, I’m going to select “Line” and because I want “points and lines,” I’m going to select that option as well. I also want “Smooth” lines rather than “Straight” lines, so I select that option, too:

Click “Next >” at the bottom. Since you already selected your Data range, you shouldn’t have to change that. However, we do want the “First column as label” for the x-axis of the chart. So, select that option:

Then select “Next >”. You shouldn’t have to change anything on the Data Series tab, so you can hit “Next >” again. On the Chart Elements tab, you’ll want to describe your chart elements. Add a Title and label your x-axis and y-axis. I also didn’t need a legend since I’m only plotting one data series, so I turned that off:

Then click “Finish.” You’ll have a chart, but it only has the points for the years when you have data, like this:

To add a line connecting the points and interpolating the missing data, click on the chart, then double-click it to modify the chart. Once you’re inside the chart, click on one of the points to select the data series, then right-click and select “Format data series”:

On the “Options” tab you’ll see “Plot Options” and just below that, “Plot missing values.” The default is “Leave gap.” Select “Continue line” and it will interpolate the missing values for you:

Select “Ok” and your line chart will now actually have a line, like this:

There you have it. A line chart with interpolated missing values in LibreOffice Calc without you having to calculate all of the missing values.

NOTE: This example was done in LibreOffice Calc version: 6.4.2.2 on a Linux-based operating system (Kubuntu 19.10).

 7,609 total views,  2 views today

LibreOffice Calc – Creating Charts with Conditional Formatting

Posted on by rcragun

I was working on creating a chart in LibreOffice Calc that was kind of weird. Basically, I wanted to show change over time in a dichotomous variable (e.g., political party affiliation in the US – Democrat or Republican). I could, theoretically, make a chart where presence is indicated by “1” and absence is indicated by “0” or “2,” but I didn’t want the chart to suggest that one value was better than the other, which is what such a chart would indicate:

This chart format seems to suggest that Democrats are something and Republicans are not.

I realized, then, that what I needed was a way to tell the software which color to code each bar in the bar chart. Of course, that is possible by clicking on every single bar and customizing the color. But, with my data already indicating whether it should be one color or another and with over 100 cases to code, I was hoping to find a better approach.

I ended up finding a couple of websites (here, here, and here) that discussed a feature in LibreOffice Calc that was introduced in version 4.5 called “property mapping.” That seemed to hold the answer. However, as of LibreOffice 6.0.3 (the version I happened to be using), the “Property Mapping” option in the Data Range window of a chart was gone. Even so, the general process still works. So, here’s how to create the kind of chart I wanted.

First, make sure you’ve got your data entered (obviously). Second, select the data you want to chart:

Select “Insert -> Chart” and then choose “Column” chart:

On the Next page of the Chart Wizard, make sure you’ve got your data selected correctly (I had to select “First column as label”):

The problem with this, of course, is that I have coded everyone in the dataset with a “1” even though, in reality, some of the Governors in this dataset were Republicans and some were Democrats. Right now, it looks like everyone was a Democrat, but this is where the conditional formatting comes on.

In your spreadsheet, you need to set up your conditional coding. This is done with “IF” statements. Here’s the code I used:

=IF(D2="Republican",COLOR(255,0,0),IF(D2="Democrat",COLOR(0,0,255),COLOR(255,255,0)))

Basically, what this code does is checks to see whether a cell has “Republican” in it or “Democrat.” If it is Republican, color 255,0,0 is selected (RED, which is reported in the cell as 16711680). If it is Democrat, color 0,0,255 is selected (BLUE, which is reported in the cell as 255). The last part of the formula indicates that everyone else should have a different color (YELLOW = 255,255,0). Dragging that down, I get a color code for every one of the values in my chart. Once I have this in place (which should really be done before you start the chart wizard, but can be done after the fact), now I can use this to adjust the bar fill color in my chart.

Adding this to the chart is done in the “Data Series” step in the Chart Wizard. Alternatively, if you’ve already created your chart, select it, and right-click. Then click on “Data Ranges” and you can adjust this there. In the Data Series window, under “Data ranges:”, select “Fill Color”:

You’ll note that the “Range for Fill Color” box is empty. We’re going to fill that with the values we just generated using our conditional code. You can do this by clicking on the button next to that empty box, then select the corresponding values:

Once you have selected your fill color, your chart will now have the corresponding conditional fill colors:

Of course, this chart still looks pretty crappy. The y-axis needs to be adjusted, the columns shouldn’t have any space between them, and it needs a title. Here’s my finished chart:

The chart provides a graphical representation of a dichotomous variable using color to illustrate shifts between political parties without suggesting that one party is better than the other. Et voila – conditional color formatting in LibreOffice charts.

 4,490 total views,  1 views today

LibreOffice – Impress Templates and Object Styles

Posted on by rcragun

I just upgraded one of my computers to Kubuntu 18.10. With that upgrade came the latest version of LibreOffice (6.1.2.1).


With this new version, when I try to create a new Impress document, I’m now forced to pick a template. The options aren’t terrible, but I am of the opinion that presentations should focus on the content, not the pretty colors, shapes, or designs around the content.

I wanted a plain as can be, black and white template. There wasn’t one to be found. So, I created one.

Plain TemplateDownload

I’m not going to go into the process of how to create a template in this post, but I do want to note one thing that I learned while creating my template. There is a quick way to change the characteristics of drawing objects in LibreOffice as well. I have always hated the default color when I create a box or circle in Impress (usually it’s some ugly blue color). It is possible to change the defaults for those, but doing so also seems to affect the defaults for outline boxes (not sure why). But it’s also possible to simply create a new style for objects and then apply that style once you’ve created your object, just like can be done with text. Here’s how you do that.

First, make sure you have your right side panel open in Impress and click on the Styles tab.

I initially tried changing the Default option in the list of styles, but that ended up changing the characteristics of the outline boxes, which I didn’t want to do. So, instead, what you need to do is create a new style (or modify one of the existing ones). You can create a new style by right-clicking on one of the existing styles and selecting “New” (my mouse was on “Modify” in the screenshot below). 

In the window that pops up, you can change the fill color under the “Area” tab and the line color under the “Line” tab. 

Since I like super plain, I changed mine to a grey fill with a black outline. Now, when I create a new drawing object, I can simply select it and double-click the style and it is how I like it:

I also saved this as part of my template. So, now, when I create a new Impress presentation, I load my template, and all of the settings I like are already in place.

(NOTE: How to save this as a template. Click on “File -> Templates -> Save as Template.” You’ll get a new window. Name your template and pick your folder. It will then be saved as a template for you that you can use when you create a new Impress file.)

 2,481 total views