This is the final part of this tutorial. We are going to put some colors and a larger font just to make our activity plan a bit more attractive to the eye.
In the first part we looked at renaming a sheet, entered some random data, formatted cells and started to build the logic of the agenda.
In the second part of this tutorial we wrote some time and IF formulas.
In the third part of this tutorial we learned about nested IF and AND function.
In the fourth part of this tutorial we learned about VLOOKUP.
In the 5th part we looked at how to create and use a ‘named range’.
In the 6th part, we learned the INDEX function.
In the 7th part, we learned how to code a (very) simple LibreOffice Macro.
In the 8th part, we used the MROUND function to round the time.
In the 9th part we did some debugging and learned about the ISERROR and NOT functions.
So let’s now make our sheet named ‘Now!’ looking a bit more awesome!
Choosing a style for your spreadsheet is up to you, I kept the default font in LibreOffice. The font is called LiberationSans, I find it simple and I like how the clock looks like with that font.
Before starting fiddling with the different formatting tools, I disabled the event handler associated with the macro that recalculate the spreadsheet.This is in order to avoid any weird behaviour every time we click in the spreadsheet (see at the start of part 9 to know how to disable the macro). Remember to set it back when your finish with editing the sheet (see part 7 for assigning the macro to the sheet event…).
For a start I deleted the two first row of Sheet ‘Now!’ as we don’t need these empty rows A and B. So the clock is now displayed in B2.
Like for a website, I think a spreadsheet document looks better if you keep the same font across all the sheets if you present them to an audience. In our agenda I prefer also keeping the same font for the visible sheet, the one named ‘Now!‘.
I have chosen to set the font size to 48 pixels for the clock at the top, in cell B2. I have centred it horizontally.
I have an empty line between the clock and the cell where the first activity (the activity before the activity before the current activity (yes twice before…)). I have added the text ‘From’ in C3 and ‘To’ in D3 as a header for the start time and end time of the displayed activities.
Now to make it look much better I have set the font sizes, font colors and background colors like that:
Before previous Activity, which is at row 4: size 16, Blue, Green 10
Previous Activity, which is at row 5: size 22, Blue 2, Yellow Green 10
Current Activity, which is at row 6: size 40, Blue 5, Turquoise
Next Activity, which is at row 7: size 22, Blue 2, Yellow Green 10
After next Activity, which is at row 4: size 16, Blue, Green 10
So as you can see the idea is to have font for the current activity bigger than the others, the previous and next at a medium size and the before previous and after next at a smaller size. I have also set 3 different shade of colors (I chose green) for the font and for the background.
The last touch is to get rid of the grid on the spreadsheet and this is very easy to do:
Either through menu
View Grid Lines
or by right clicking on the spreadsheet tab ‘Now!’ and selecting ‘View Grid Lines’ in the context menu.
You may also have some vertical dotted lines (You can see one on the picture above) and if like me you don’t want them you just need to change the options for displaying page break:
And here is how it looks like:
Download the file:
You can download the finished version of this file on my GitHub repository:
If you are not used to GitHub, just click on the green button “Clone or Download” then download the Zip file on your computer. Then unzip the file to be able to use it with LibreOffice.
A prompt will pop up when you open the file, choose ‘Enable Macro‘ if you simply want to use the file. If you only want to look at the formulas, it will be easier to select ‘Disable Macro‘ otherwise the macro ‘recalc’ is triggered every time you click somewhere in the table making it a bit funny to work with.
I hope you enjoyed this tutorial and that you learned a bit more how to use LibreOffice
Please leave a comment to let me know if you liked it or if you had any problem at any stage.