LibreOffice Calc – Conditional Formatting

I haven’t used this feature of spreadsheet software as much as I probably should, but I have a spreadsheet I have been working with a lot lately and conditional formatting has been key to helping me orient myself in the spreadsheet. However, in the process, I realized that I am really a noob when it comes to conditional formatting. To help get myself up to speed, I created the following tutorial so I can reference it again in the future. (NOTE: I’m using LibreOffice 7.2.4.1 for this tutorial.)

Before I begin, what is conditional formatting? The basic idea is that you can change the look of a cell in a spreadsheet (or many cells) based on the contents of those cells (or other cells) without having to manually change the formatting of the cells. Instead, you “program” into the spreadsheet how you want cells to look assuming they meet specific criteria. Then, when you enter information into your spreadsheet, the cell formatting automatically updates to reflect the information in the cell.

Tutorial A – “Formula Is” Conditional Formatting

Let’s start with a very basic illustration. Let’s say I want to use a spreadsheet as a to-do list. Here’s how my spreadsheet looks, to begin with:

What I want to do is pretty simple. If I add an “x” to the Complete column for an item, I want that item to be crossed out. Effectively, I am going to change the font of the tasks to “strikethrough,” which crosses out the text. This way, I can quickly and easily discern which items are completed on my to-do list and which still need to be completed.

How do I set up the conditional formatting?

First, select the first cell (A2) that says “grocery shopping.” Then go up to “Format” on the menu and select “Conditional” -> “Condition.”

You’ll then get this window

An explanation of the window is in order. At the bottom, the range of cells to which the conditional formatting will apply is included. Obviously, this can be set to a range of cells or just a single cell. Right now, I have it applying to just a single cell, A2.

Where it says “Condition 1” is where you will build your condition statement. If you click on the drop-down arrow where it says “Cell value,” you’ll see a couple of additional options:

Those options are “Formula is” and “Date is.” This tutorial will cover two of the three options. For this one, we’re going to go with “Formula is.” The reason why we are going with “Formula is” is because we are going to use the information in a different cell, B2, to change the formatting in the current cell, A2. As the next tutorial will illustrate, you can also use the information in the same cell (e.g., A2) to change the formatting of that cell. So, select “Formula is.” You’ll notice that the window changes and you now just have a single box next to “Formula is” where you can enter your formula:

For our very simple to-do list, our formula will be pretty simple. We’re going to tell Calc to check to see if there is a lower-case “x” in B2. Here’s the formula: B2=”x”. (NOTE: If you want text or a character as part of your formula, it has to be inside quotes.) Here’s how it looks in the window:

Below that, we need to indicate how we want the formatting to change. If you click next to “Apply Style” where it says “Accent,” you’ll see a list of built-in formatting options:

Since we want to have the text crossed out and that isn’t one of the built-in options, we’re going to select “New Style”. We’ll then get this window:

We actually only need to change two things. First, name the style. Since we are going with “Strikethrough,” let’s call this “Strikethrough.”

Next, Strikethrough is a Font Effect, so we click on the Font Effects tab and look halfway down the screen to where it says “Strikethrough.” By default, it says “(Without).” Click on that drop-down and select “Single” (or “Double” if that’s your fancy).

When you’re done, click “OK” and you’ll be taken back to the original Conditional Formatting window.

That’s actually all we need to do for now. Click OK on that window, and let’s test our Conditional Formatting. To test it, type a lower case “x” in B2 and hit “Enter” to see if your formatting works.

Okay, one more thing and the first part of this tutorial is done. We applied that conditional formatting to just one cell, A2. But we want it to apply to all the cells in that list. How do we copy the formatting and apply it to the others instead of having to repeat the process above? Select A2 and right-click. You’ll get a context menu. Look for “Clone Formatting” and select it.

When you do, you’ll notice that the paintbrush icon on your toolbar is selected:

To apply the formatting you just cloned, select the cells you want to apply it to. Your cursor will change to a paint bucket. Click it over the selected cells and the conditional formatting will be applied to those cells. Now, test it. Add an “x” next to one of the other items to see if your conditional formatting is working:

Congratulations! You’ve successfully set up your first conditional formatting in LibreOffice Calc.

(NOTE: You can also apply the conditional formatting by indicating the cells to which the formatting should be applied in the Conditional Formatting window at the bottom. We indicated just A2 above, but you could include a range like A2:A6.)

Tutorial B – Conditional Formatting a Range with Colors

For this next tutorial, we’re going to get a little fancier. Nothing too crazy, but we are going to make the formatting conditional on the values in the cell and then apply background color to the cells. Our sample data are test scores.

First, let’s select the top test score in B2 and go to the “Format” menu, then select “Conditional Formatting” -> “Condition.”

We’ll get the same window as we got in Tutorial A. But things are going to be different from here on. We’re going to set up a color code that reflects test score ranges so we can quickly see which students are doing well and which may need extra help. To do this, in that window, leave “Cell value” as is and select the next dropdown. We’re looking for “is greater than or equal to”:

We want that option because the first condition we are going to enter is our top range of scores – 90 and up. Now, in the box next to that, enter 90. (NOTE: No need for quotes as this is a number, not text.)

Now, the style. We are going to apply a colored background based on these ranges. So, in the dropdown next to “Apply Style” select “New Style”:

Like we did above, we’re going to name our style, something like “grades_best”.

Next, click on the “background” tab then select “Color” and you’ll see this:

You can now pick the color you want to use as the background for that cell. I like green to mean that a student is doing well. So, I’m going with that:

When you’ve picked your color, select “OK.” That will return you to the conditions window. We have our first condition, but we wanted to assign colors based on ranges. That means we need additional conditions. In that window, click on “Add.”

When you click on it, you’ll get “Condition 2.” We’re going to follow what we just did for Condition 1, but we’re going to change a couple of things. First, in the dropdown next to “Cell value,” we don’t want “is greater than or equal to” anymore as our next range will have upper and lower values. So, we want “is between.”

Now, think carefully about how this will work. Our first Condition was equal to or greater than 90. We want this one to range from 70 to 89. Thus, will need to set the two values as 69 and 90 so it includes all the values we want but excludes 69 and 90. Once you have that, follow the same steps above to set the background color you want. I went with blue for doing okay and named that style “grades_good”:

We’re going to set up two more conditions. One is going to be from 60 to 69 and is going to be orange. The last is from 0 to 59 and is red! Like this:

When you have all your conditions in, click “OK.” The background color for B2 should now change to match the conditions you created. You can test it by changing the values in B2 to make sure everything works.

Now, as we did above, we can copy that conditional formatting and paste it to the other cells we want to have that formatting. Right-click B2 and select formatting then select the cells where you want to apply it and click with the paint bucket. All of those cells should now have the same conditions as B2.

There you go. That is how to use multiple conditions to format cells with a range of values.

Some Useful Notes

If you want to change the conditional formatting for a cell, be careful. If you right-click a cell with conditional formatting and select “Conditional Formatting” you’ll get this rather uninformative warning window:

In this version of LibreOffice, whether you select “Yes” or “No” doesn’t seem to matter, it wipes out the current formatting. So, don’t do that. Instead, select the cell you want to modify, then click on the “Format” menu at the top of the window and select “Conditional Formatting,” but don’t select “Condition”! Instead, click “Manage” and that will pull up the existing conditions. You can then modify the existing conditions from that window.

 751 total views,  6 views today

LibreOffice Calc – Creating Charts with Conditional Formatting

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,949 total views