Here’s how to implement conditional formatting using a formula in a LibreOffice Calc cell and apply it to your custom condition.
Conditional formatting is a way of styling cells based on various conditions. For example, you may want to highlight cells as Red in a set of negative data. And make Green for the positive collection of data.
You can easily do it manually for a few rows. But for larger sets, you can use conditional formatting in LibreOffice.
But, the conditional formatting module in LibreOffice is a little advanced and requires adding various custom conditions. It’s comprehensive and should suffice for complex formatting for large volumes of data.
In this article, I will show you how to apply similar formatting with minimal effort using cell formula without using the conditional formatting feature of LibreOffice.
Let’s explain with an example of student data and their marks.
Table of Contents
Conditional Formatting Using Cell Formula in LibreOffice Calc
I have this set of student data and their marks from an exam. And let’s consider their pass mark is 75 out of 100.
How can I highlight the cells with Red that have failed (i.e. marks less than 75)?
To do that, I will use STYLE and CURRENT functions and use the following formula in the first cell, then drag it down.
=(B2)+STYLE(IF(CURRENT()>=$E$2,"Good","Bad"))
And you can see the rows are correctly highlighted.
Explanation
STYLE Function
STYLE function applies a predefined style code to the cell. It can also apply the second style with an optional time delay. It is helpful when you need to dynamically change the formatting of a cell based on a calculation. Here’s the syntax.
STYLE(Style[; Time[; Style2]])
Style: It is a pre-defined string enclosed in quotation marks such as “Good”, “Bad”, “Neutral”, “Error”. If nothing matches, “Default” style is applied.
Time is a positive number or a relative/absolute reference to a cell containing a time number. It is the delay in seconds before Style2 is applied.
Style2 is also those predefined styles such as “Good”, “Bad”, etc.
CURRENT Function
The CURRENT function evaluates/returns the current value of a cell formula evaluation just before CURRENT is called. There is no argument for this function.
CURRENT()
So, in the above example, the cell B2 value is evaluated using the CURRENT function from the PassMark value from cell $E$6. And if it is greater than 75, the STYLE function dynamically formats the cells using Red or Green.
Preety neat, isn’t it? You can even add additional formulas, such as calculating percentages and adding marks inside it the same cell.
I hope it explains the concept. Now, let’s learn some cool tricks for those functions.
More STYLE Examples for conditional formatting using cell formula
The "+" sign should be used before STYLE when a formula is evaluated as numbers. For text, use the concatenation "&" operation.
For example, I should do this if I want to custom style a simple text to “Warning”.
The T() function before STYLE is to return only for formatting and not the return value 0. If you omit this, a “0” will be concatenated to your string.
="This is your final warning" & T(STYLE("Error"))
If you want to change the formatting after a time delay, you can use Style2 and the time parameter. You can use the following example formula to change the above text to GREEN after 6 seconds. See the below video.
="This is your final warning" & T(STYLE("Error",6,"Good"))
Closing Notes
I hope this guide explains the basics of the powerful STYLE function combined with CURRENT. As you can see, without conditional formatting, you can use formula to style your cells “on the fly”.
Do let me know if it helps for your use case.
