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:
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 220.127.116.11. 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 www.webelement.click 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:
Pick cell values from drop-down checklist
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.
Statuses PASSED FAILED SKIPPED
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.
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:
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
B. We also have column
Cwhere we’d like to calculate sum
A+Bfor each line. We select cell
C1and 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
C1and 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
C3and so on. They are now refer to the same columns
Bbut line numbers are changed (
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+Bfor each line, but sum
A1with each line’s
B. In such case we set the following formula for
=A$1+B1. Here in this formula we have prohibited to shift
Acells 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
C3and so on. This time in a bit different way. They are now refer to the same columns
Bbut line numbers are changed (
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 is the very first cell in
Step result column having the column
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
FrontEndtab, but we haven’t fixed the row axis. We also completely froze
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 resultfrom its own line.
B4cell for both axes because the conditions for all the cells have to match the value of
Step resultwith value
PASSEDthat 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
SKIPPED. It is easy to test that the counting result is being changed when you change step results on
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):
Still have questions? Ask them here. I will try to extend the article with missed points leaning on your feedback.