Creating awesome interactive checklist in LibreOffice / OpenOffice Calc

In today post we’re going to create a checklist. Why creating a checklist deserves separate article? Couple of aspects played their roles. Firstly the article will be related to free software package LibreOffice (former OpenOffice). In modern world, a lot of people choose that one instead of MS Excel (which still have great popularity though). Secondly I would like to demonstrate that creating pretty looking and interactive checklists is not that hard deal. Spending some reasonable time you will manage to make your checklist not just fulfill your functional demand, but also impress people who will see it from aside. Ideally we should eventually get something like this:

libre office checklist

Here I have to apologize for my video didn’t capture drop-down list in Step result column. Due to some reason my screen capturing software wasn’t able to "see" that

Before we begin a few more reservations must be made. First - in the article I’m using version LibreOffice I believe it should not be very important since the required functionality seems to be very unlikely changed across versions. The second one - you might be using localized package version so that you will have to map my actions and references (to menu items, etc) to your language on your own.

Now we can start. I have prepared small checklist which I’m going to use in my example. It will be presented a moment later.

Prepare the basics

Open LibreOffice Calc, copy the checklist from snippet below to the clipboard, place the cursor to some cell (better not right in the corner so that there is some padding left for better reading) and paste what you have copied. Paste data dialog should appear. What is configured by default should be okay for proper pasting so just accept it.

Step No	Step Description	Step result
1	Read all the posts marked with #selenium hashtag on site
2	Download and set up Java SDK
3	Download and set up Maven
4	Download and set up Intellij Idea
5	Download appropriate web driver
6	Create maven project
7	Add selenium library to your dependencies
8	Write HelloWorld program that opens some web page and perform couple of interactions
9	Step by step increase the complexity of your code
10	Add JUnit or TestNg to your code

Increase the width of central Step Description column so that it is now takes approximately 2/3 of the total width. Select that entire column, right click the selection and choose Format Cells.. item. In the dialog go to Alignment tab, check the checkbox against Wrap text automatically and click [Ok].

Let’s work with styling a bit. This is quite a subjective matter and your system might not have particular fonts installed, so that you should just use your personal sense of beauty. Bold the text in checklist header and in the column holding the step number (select the area and press Ctrl+B). Select the cells holding the step descriptions. Assign a different font to them so that it is more easy to distinguish different pieces of your checklist. Finally, rename the tab you have placed your checklist at to something like FrontEnd. Also add one more tab and name it BackEnd. You should get something like this:

libre office checklist step 1

Pick cell values from drop-down checklist

Switch to BackEnd tab. Copy data from the below snippet to clipboard and paste it to some cell on that tab. Color the cell containing word Statuses. Actually we’re not going to use that cell any more. We need it just to make data more readable.


Get back to FrontEnd tab. Select all the cells in column Step result (of course without the column header). In top menu choose Data ⇒ Vality…​. In validity dialog go to Criteria tab, in Allow list choose Cell range. Click the range selection button against Source field, go to BackEnd tab, select those three our statuses (not touching the header with our selection) and confirm the selection with button on the dialog that is to be still displayed somewhere on your screen.

So we’re taken back to the initial Validity dialog. Click [Ok] button there. Now we can see that in each cell in Step result column when click inside that cell the small arrow appears expanding the list with the values which are acceptable for the column.

libre office checklist step 2

Colorizing the steps interactively

On this step we’re going to add colorizing the step depending on what option we choose for Step result column. Such kind of colorizing is achieved with the help of conditional formatting feature (i.e. assigning particular style to a cell depending on if some condition is met).

Select all the area containing our steps (including numbers, descriptions and results) except of the header and click Conditional formatting button and choose Condition.. option. You should see a picture like this:

libre office checklist step 3

What we can see here? First - is the range to which we’d like to apply our formatting. If we’ll add more steps we’ll have to extend that range. Second - the conditions. On the screenshot we can see one condition. It appears there by default and we’ll not be using it. Instead, we’re going to define three other conditions. Yet, we have to make one more reservation.

You probably noticed that sometimes in formulas when they refer to cells, they use $ symbol (but sometimes not). What does it mean? The fact is that for the sake of convenience, the formula that is set for certain cell can be "extended" to other cells by dragging the corner of the selected cells in either vertical or horizontal direction. Wherein the formula that is copied to those new cells will have it’s own column references amended using the direction you chose. For example we have numbers in columns A and B. We also have column C where we’d like to calculate sum A+B for each line. We select cell C1 and set formula for that cell: =A1+B1. Thus we now have the sum calculated for the line 1. Lets now click the bottom right corner of selected C1 and drag it down so that all the lines (we need to parform calculation for) are selected. Now we can see that LibreOffice automatically changed the references in formulas in C2, C3 and so on. They are now refer to the same columns A and B but line numbers are changed (C2=A2+B2, C3=A3+B3, etc.).

The same concept is used if we extend the formula horizontally. Formula in each new cell shifts its inner references horizontally. But what if we want to fix some reference so that it cannot be shifted either horizontally or vertically or in both directions. We use $ symbol for achieving this.

In our example, we might want to sum not A+B for each line, but sum A1 with each line’s B. In such case we set the following formula for C1: =A$1+B1. Here in this formula we have prohibited to shift A cells vertically (however horizontal shift is still allowed - note that, however this is not relevant to the given example, since we’re not going to extend formula horizontally). Extend the formula to remaining lines down. Now we can see that LibreOffice automatically changed the references in formulas in C2, C3 and so on. This time in a bit different way. They are now refer to the same columns A and B but line numbers are changed (C2=A1+B2, C3=A1+B3, etc.).

Great, go on!

So, we’re going to set the conditions which would define the style for each cell in our selection. In the conditional formatting dialog change the condition type for the first condition so that it is changed from Cell value is to Formula is. In the formula field I set the following value (this is relevant to my particular case so that I’m going to explain what is what to make you easily bring it into line with your case): $D4=BackEnd.$B$4. So: $D4 is the very first cell in Step result column having the column D fixed; BackEnd - is the tab name where we store our data not to be an eyesore; $B$4 - the coordinate where I store step status PASSED. You might have different values for cell coordinates.

Let’s note once again, that we have fixed column D on our FrontEnd tab, but we haven’t fixed the row axis. We also completely froze B4 cell.

The fact is that when we apply formula in conditional formatting to a range, we actually apply the formula to the top left cell of a range and then "extend" it to the bottom right cell of the range. This means that for each cell to the right the formula will attempt to shift column references to the right and for each cell to the bottom the formula will attempt to shift all references one step down. If I wouldn’t fix column D, then condition evaluated for the cells to the right of the first column in my range would miss the proper cell with Step result. So we addressed this issue by freezing horizontal shift when refer to` Step result` column with $ symbol. Vertical shift has to be allowed because each step should take the Step result from its own line.

We fixed B4 cell for both axes because the conditions for all the cells have to match the value of Step result with value PASSED that is stored within fixed coordinate.

It is only left to pick a style which is to be applied to the cells meeting the condition. In drop-down list choose a pre-set style Good. Now we can save our rule, close hte dialog and check that all the steps for which we set Step result = PASSED a colored with green. However if we set some different value, the colorization disappear. We’ll fix it in a few more steps.

As we have created and saved some conditional formatting rule, we can get back to editing using the same button. With the only difference that now we should use Manage menu item. On Manage dialog we can see that our rule we have recently created. Select it and click Edit…​. We’re now in the rule configuration section that we are familiar with already. Click Add button so that new condition is added. Repeat what you did for your first condition but change the formula slightly. Instead of the reference to the cell where PASSED status were stored we no should point the cell with FAILED status. So that the entire formula will look (in my case) like $D4=BackEnd.$B$5. Assign pre-set style Bad to this condition and repeat all the same for remaining status SKIPPED. Pre-set style Neutral seems to be a good choice for it.

So now this is done. We are to add summary chart to make the tutorial completed.

Adding a Chart

We will add the chart in three steps: 1 - prepare data for the chart, 2 - prepare colors for the chart (yes, that is not that trivial to colorize chart in LibreOffice or I am just not aware of how to do that more effectively), and 3 - creating chart over prepared data.

Prepare data for the chart. The chart will be demonstrating which part of total is taken by a particular step result. That means we have to calculate somehow the number of steps in each status. Get to BackEnd tab. On that tab in the cell that is one step to the right (close neighborhood is very important) of PASSED I’m adding the following formula: =COUNTIF(FrontEnd.$D$4:FrontEnd.$D$13, $B4). You’re adding the same one, but amend the coordinates if required. Function COUNTIF counts the number of elements (cells) in specified range the value in which meets specified condition. In our example the range is defined as FrontEnd.$D$4:FrontEnd.$D$13, i.e. these are the Step result cells. We freeze the range (using $ symbol) in order to properly extend the formula for remaining statuses. Note that we refer to FontEnd tab now since it is not the tab we’re currently at. The condition that has to be met for the cells we’d like to count is defined as $B4. It is the coordinate where on the BackEnd tab (where we are currently) I keep the status PASSED. Now extend the formula two more cells down covering statuses FAILED and SKIPPED. It is easy to test that the counting result is being changed when you change step results on FrontEnd page.

Adding custom colors to the chart. Actually this is not very important step and you will probably want to skip it. If we’ll not take this step, the chart segments will get their colors automatically by default. I would like my colors would be as close to the colors of steps as possible (however I’m afraid we will not reproduce the colors precisely in this training), this is why I’m going to show you how to do this in general.

We have two approaches to colorize our chart. First approach is more interactive but it is more buggy at the same time. Following this approach we need to define color codes at first, place them to some cells and then when create chart specify that range in dedicated field intended to store color codes. This will allow creating more interactive chart since we can control those color values, change them on conditions, etc. But this approach has quite serious drawback. When we colorize the chart in this way, the legend preserves the colors as if it would be by default. It makes reading the legend not very convenient and even may lead to misinterpretation of the chart data.

Second approach is less interactive but less buggy as well. Following that one we create chart with default colorization. Then we edit chart style after it has been created and placed. As soon as we do not have the objective to develop interactive colorization I think we’ll use the second approach.

Place the chart. Get to FrontEnd tab and click add chart button. In wizard choose Pie Chart chart type, then choose chart style (on the same step) or just leave everything as default. Move to the next step where you’re to select the range over which the chart on BackEnd tab. The selection should take a rectangle of two cells in width and three cells in height. In my case that rage was defined as BackEnd.$B$4:$C$6. On the same screen select "Data series in columns" radio button and make sure you have the only check-box checked against "First column as label". Click Finish button, resize the chart and place it in the place it fits better.

Then right-click the chart and choose Edit - now we’re in edit mode. Left-click the chart area and then once again click the segment you want to set the color for. Then again right-click that segment and choose Format data point. We can now see the dialog allowing us to set up the format for the segment. On Area tab you can pick the required color. You can choose the color from one of pre-set palette (which you can find on the same tab in dedicated drop-down list) or pick the color on your own using the combination of color channels intensiveness in different formats or just specify the hex value for the color if you already have the code you require. After you have set up style for the segment, repeat the step for remaining ones.

This is it! The result should look like shown below (I demonstrate both the tabs here in order to let you compare what you can see and the formulas I used in the example):

libre office checklist final result frontend
libre office checklist final result backend

Still have questions? Ask them here. I will try to extend the article with missed points leaning on your feedback.