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.

Loading

Comments

2 responses to “LibreOffice Calc – Creating Charts with Conditional Formatting”

  1. Cathy Avatar
    Cathy

    Thank you for the helpful explanation. I am unclear, however, how you got from the IF statements to the numbers in the cells.
    That is, you say:

    If it is Republican, color 255,0,0 is selected (RED, which is reported in the cell as 16711680)

    How is the number 16711680 determined?

    I am trying to understand conditional coding and would appreciate your help to understand this.

  2. Tom Avatar
    Tom

    Hi Cathy, I know it’s been a few years, but I just ran across this question and thought I should leave answer for others. the (255,0,0) and 16711680 are both representations of 24-bit color. In this case, the 24 bits look like this:
    11111111 00000000 00000000
    Each of the three colors is described using 8 bits, but those bits are ordered from right to left, with the rightmost bit (called least significant, in position 0) representing the number 1, and every bit moving to the left representing the number 2^n, where n is the position. This is the base two or binary number system.
    In every group of 8 bits there are 255 combinations, which is where the 255 for Red comes from, but because it is sitting in the group all the way to the left it is using the 8 bits from position 16 to position 23. Green would use the bits from position 8 to position 15, and Blue from position 0 to position 7. To convert to decimal, you add up all the 2^n numbers that correspond to a “1” setting for each bit. For the Red example, that looks like this:
    2^23 = 8388608
    2^22 = 4194304
    2^21 = 2097152
    2^20 = 1048576
    2^19 = 524288
    2^18 = 262144
    2^17 = 131072
    2^16 = 65536
    For a total of 16711680. This is the conversion that LibreOffice Calc is doing when you type in the formula =Color(255,0,0).

Leave a Reply

Your email address will not be published. Required fields are marked *