58

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?

2 Answers 2

53

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

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.

3
  • @Cerin: With current LibreOffice, it's even easier because of the new Window for managing conditional formatting :-) - i've updated my answer accordingly. If something doesn't work, please explain exactly what you want to achieve and what you've tried already yourself.
    – tohuwawohu
    May 26, 2021 at 9:13
  • @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
  • 1
    You're right. I had the cell range syntax wrong. I thought you had to make a separate condition for each row, but all you have to do is set a square-shaped cell range that covers everything like A1:N1000. Thanks.
    – Cerin
    May 26, 2021 at 13:33
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.