LibreOffice Calc – Creating Charts with Conditional Formatting

Posted on by ryan

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.

LibreOffice Calc – Comparing and Aligning Two Lists Using VLOOKUP

Posted on by ryan

One of the tasks I have to do regularly as part of my job is to compare two lists to see which items are missing on one list but not the other. I have been doing this by hand but figured there had to be a way to do this in Excel. I finally figured it out but don’t want to forget how to do it. So, I’m documenting it here so I can draw on this whenever I need to do it again.

Here’s the scenario. I have a list of “items” but every month or so I receive an updated list of “items” from someone else. During that month, some of the items on my list have been taken off and some have been added to it. Likewise, the same has happened to the other list that this other person sends me (I’m being really vague here because I work at a university and there are laws that govern academic records).

My list and the other list have to be kept synchronized but we have two separate databases that are used for this because… (argh, yeah). Anyway, what that means is that I have to compare the two lists and quickly find the items on one list but not the other and vice versa. Here’s how to do it quickly in LibreOffice Calc.

Here are two lists:

(NOTE: You can actually do this in two separate Calc sheets or within the same one.)

LIST 1 is in Column A and List 2 is in Column C.

In Column B I am going to create a function that allows me to search all of Column C to see which of the items in LIST 1 show up in LIST 2. Click in Row 2 of Column B then click on the Function Wizard (or start typing):

In the Function Wizard dialogue box, you’ll see this:

Search for VLOOKUP then double-click it and you’ll see this:

You now need to build the function. This is where I got confused, so I’m going to try to explain this carefully.

The first box is the “Search criterion.” Basically, this is what you want to find. In our example, let’s say that we want to see which items in List 1 are in List 2 (Hint: It’s apples and grapes.). So, we are going to put in the Search criterion that we want the software to search the items in column A or LIST 1. We do this by simply selecting cell A2 (you can do this by typing it in or by selecting it by clicking the button to the right of the box and then selecting A2 like I did below):

Next is the “Array” box. This is the content through which you want the software to search to find the items in LIST 1. Again, you can type this in or select it using the button to the right. However, there is an important change that you need to make here. If you select the array with your mouse and leave it as is, the values will change as you drag this formula down in Column B as it will assume you want to adjust the array as well. Since we don’t want to adjust the array but rather want to search through the same items in LIST 2, we need to put dollar signs before the letters and numbers in the Array which will lock the boundaries of the array into place so they don’t change when we copy the formula to other cells, as shown below:

The next part is the part that threw me off for a long time in figuring this out. The “Index” is the column in the Array you want to compare to the Search Criterion. In this case, all you need to do is specify “1” since there is only one column. But, presumably, you could have an array made up of multiple columns and want to choose just the 4th column (so you would enter 4 in the Index). Here’s how this looks:

Finally, the last box that is part of our function is the “Sort order” box. This tells the software whether the lists are ordered alphabetically or not. If they are not, enter zero (“0”). If they are, enter “1.” If you leave this blank, LibreOffice Calc thinks the lists are ordered. So, make sure you fill this out.

Once you’ve got that all entered, your formula should look like what I have above. Hit “OK” and it will search the Array C2:C7 for “bananas.” If it finds it, it will return “bananas.” If it doesn’t, it will return “#N/A” as shown below:

You can then, of course, drag this formula down. When it finds the item from LIST 1 in LIST 2 it returns that item. When it doesn’t, you get #N/A.

And there you have it. A LibreOffice Calc function for searching a list for a target and returning an indicator.

(NOTE: What I typically do after I have run this function is sort by the column where the function is located so I know which items are missing from LIST 1 and which are missing from LIST 2.)

LibreOffice Calc – splitting contents of cells into multiple columns (e.g., splitting commas)

Posted on by ryan

I periodically have to take a column of text in LibreOffice calc that has names like this “Lastname, Firstname” and split them into two columns. I figure it out every time, but then I forget how I did. So, here’s a quick tutorial on how this is done.

Open your spreadsheet with the cells that need to be split, like this:

Select the column that you want to split:

Then go up to Data -> Text to Columns:

You’ll get the following window:

This window gives you several options for splitting the cells, using commas, spaces, semicolons, tabs, other, etc. I selected just “Tab” and “Comma” but could also select “Space” to get rid of the extra space. However, I’m going to leave the extra space and show you one more function that can be useful in more complex situations. Once you’re done, hit “OK” and you’ll see your single columns split into two:

If you want to get rid of the extra space, there is a LibreOffice Calc function for that. Click in cell C1 and then go up to Insert -> Function. You’ll see this window:

The function you want is in the Text Category (use the dropdown menu) and is called TRIM. Simple TRIM the text in B1 and it will get rid of the extra spaces:

When you’re done with your function, select “OK” and you’ve got your spaces removed. Drag that function down and it will remove all of the spaces:

You can then copy the new column without the spaces and do a “Paste Special” into the old column, overwriting the text with the spaces. Just make sure you turn off the “Formulas” when you do the “Paste Special” and you’ll get just the new text:

Delete the column with formulas and you’re good to go: