73

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?

4 Answers 4

65

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):

Conditional Formatting 1

To do so:

  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:

Conditional formatting: result

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; enter image description here

  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.

      enter image description here

  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: enter image description here

3
  • @Cerin: Since you can set the affected cell range using the usual cell range syntax, you don't need to select cells, columns or other ranges manually. You can still do so if you want (using the button right of the "Range" input field), but you don't have to.
    – tohuwawohu
    May 26, 2021 at 9:55
  • To me version for LibreOffice 7 colorizes only the column, not the whole row
    – nulll
    Dec 14, 2023 at 9:50
  • 1
    @nulll: Sorry, you're right, my last example applies to the column, not the row. I've added another example for a conditional formatting applying to a row based on the value in a certain column.
    – tohuwawohu
    Dec 14, 2023 at 15:31
2

I confess I found this remarkably tricky. You need to 'juggle' the selected cell (black outline) with the selected array for formatting (shaded).

Click on D5 (to show black outline) and select entire sheet (above 1 and to the left of A). Set conditional formatting required with Formula is: $D5={whatever the contents of D5}.

If that does not work it is only that I have not explained myself properly!

1
  • 2
    Thanks Pnuts, your answer worked ! Here is what I did: 1. Click on cell that holds value on which color depends. Make sure it is in one corner of 'table' you want to apply coloring to. 2. Then select entire range either with mouse (starting from previously selected cell), or with holding shift key and clicking on diagonally opposite corner of the range. 3. Enter conditional formating and define condition as "Formula is" "$D5 = 'whatever condition is'. ("$D5" is reference to a corner cell holding criteria value) That should work :)
    – PopMilo
    Aug 7, 2013 at 6:52
1

My reputation does not allow me to directly comment on the answer above.

If you followed the answer and want to compare with a string (ex if fourth column is "Error") and you don't see any format change, then you have to wrap the string value within double quotes "". Otherwise it is thought to be "column/line header" and a caution sign will appear to warn you

not correct

So to style the cell if its content is "DIFF" you have to write "DIFF" (mind the double quotes) not simply DIFF.

Consequently the full conditions will read :

Cell Value is equal to "DIFF" apply style Good. As depicted on the picture below.

correct

Edit : Here is a full example (Libre Office 7.x) for setting the style of the rows based on a column values

  • First select all your data (you can click on the column header just before A column).

  • Second choose in the menu bar Format / Conditional / Condition

  • Third select for the first condition "formula is" instead of the default "cell value".

  • Fourth fill in the formula field (as explained above in the first part of the answer) with $G1="DIFF" and choose the style to apply. Then validate (Please mind the " around DIFF as explained earlier in my answer).

You should see all lines that have DIFF in their G column colored with the chosen style.

0
0

Let's say that you have 3 columns (A, B and C) and the "Status" column (on which the condition will be checked on) is the "B" column and the first row (B1) is used as it's heading so the data starts in the second row (B2).

  1. Select A2:C100

  2. Format -> Conditional Formatting

  3. Select "Formula is"

  4. Enter the formula: $B100="done" IMPORTANT: Make sure that you enter the LAST selected row number ($B100) into the formula, NOT the FIRST selected row number ($B2)

  5. Select the style you want and press "OK" to apply the conditional formatting.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .