How to color rows based on cell value in OpenOffice and LibreOffice

How do you automatically set the background color of an entire row based on the value of a specific column in that row in OpenOffice and LibreOffice? There are similar questions for Excel, but this doesn't completely work in LibreOffice. I can set a conditional formatting rule using a formula to set the color of a specific cell based on the value in that cell (e.g. =$D5 ), but how do I apply the formatting to the entire row?

• openoffice-calc
• libreoffice-calc
• conditional-formatting

4 Answers 4

For current versions of LO, see below!

It's even easier than pnuts' solution. You don't need to select the cell that holds the value that should be relevant for conditional formatting. Just select all the cells that should get conditionally formatted, and use a formula-based rule. Now, if your formula uses a cell address with fixed column (e.g. '$D5'), OpenOffice will adapt it for every selected cell.

For example: You want to conditionally format the following table based on the value of the second (B) column (format should be applied if value is greater than 2):

1. Select the cells A1 to C5;
2. Select Menu Format -> Conditional Formatting ->
   Manage.
3. Hit the Add Button to add a condition;
4. Select condition type Formula is
5. Enter as Formula $B1 > 2 and set the format to be applied if condition matches (for example, ugly red background);

The result will look like this:

Note: The row number you use in the formula should match the number of the first row you've selected. In the above, that's row 1, but if there were a row of headers above it and so your selection started on row 2 (leaving out the headers), you'd use $B2 > 2 as the formula (rather than $B1 as above).

To double-check what LibreOffice / OpenOffice did with your table, select a single cell, for example A4 , and select Menu Format -> Conditional Formatting ->
Manage. again.

You will see there's a conditional formatting rule defined for that cell, with Formula is as condition type, and $B4 > 2 as formula. So, LibreOffice translated the conditional format defined for the complete table in single rules for each of the cells automatically.

Update for LibreOffice 7 (tested with 7.1.3)

To set the conditional formatting for an entire column in LO Calc Version 7, proceed as follows:

1. Menu Format -> Conditional -> Manage.
2. In the "Manage Conditional Formatting" window, select Add ;
3. In the "Conditional Formatting" window:
   1. Set the condition (in my example: apply format if cell value > 3, alternatively, select "Formula is" instead of "Cell value" and add your formula in the adjacent field).
   2. Set the cell format to apply if condition is true;
   3. Set the cell range to apply the conditional formatting; for an entire column, enter "A:A".
   4. Hit OK .
4. Back in the "Manage Conditional Formatting" window, select OK again.

That's all - now the conditional formatting rule is activated for the entire column.

Formatting row based on column

Let's assume the following: We have a cell range A1:K8; there are numerical values in column "B"; if that value equals 2, the complete row (e.g. A1:K1) should have a certain formatting assigned.

To define such a conditional formatting rule applying to rows and based on the value of a certain column, proceed as described above ("Update for LibreOffice 7 (tested with 7.1.3)"), with the following modifications in step 3:

1. Condition: Formula is
2. Text field for formula: Enter the condition that triggers the format, with the cell reference pointing to the cell of the first row to check. In our case: $B1=2 . Notice the "$" before "B" which "fixes" the conditional check to column B.
3. Cell Range: A1:K8

Result: