Conditional Formatting in OpenOffice Calc Spreadsheets

I saw this article in Lifehacker this morning.
http://lifehacker.com/software/excel/excel-tip--conditional-formatting-231340.php

All about how conditional formatting in Excel is so useful. Well, yes, it is, but (as I said while bristling slightly, in my comment on the lifehacker site ;>  )  you can do the same things in OpenOffice.org Calc spreadsheets.

Here's how you use conditional formatting.

First, you have to make styles. There's no way around this but it's very simple.

1. Choose Format > Styles and Formatting.

2. Be sure that Cell Styles are displayed in the Styles and Formatting window.

3. Right-click in a blank spot and choose New.

4. Name the style something appropriate.

5. Define the style.

6. Click OK.

Next, just set up the conditions. Let's say that you want to have a total show up with the GreenBold style you just created, if it's 500,000 or more, and you want it to show up as RedItalic style if it's 200,000 or less.   Click the following image to see it bigger.

---

Comments

[Rob]

I've found this useful and picked up a few other tips whilst I was here but I can't get the conditional formatting to work for text in cells.
I want all cells with text beginning "An*" to be highlighted but it refuses to work!
I tried "=IF(H1778="An*";1;0)" and this doesn't work either.
Do you have any hints?
thanks
Rob

[Leandro Guimarães Faria Corcete DUTRA]

I use 2.2.1-7 in Debian, and it indeed behaves strangely with text.

[noii]

I'm sure there must be an easier way, but here's what I came up with to test a cell to see if it contains a text string value in conditional formatting:

Formula is:
COUNTIF(F13;".*P.*")>0

this tests for the character 'P' anywhere in the text of each cell in turn. The open office help says that COUNTIF uses regular expressions for matching the text - hence the '.*' before and after the 'P' (when looking for cells that only contain an exact phrase match i've had mixed success with a straight "P" test - it seems to work in conditional formatting formulas, but not in cells).

So to match the word 'yes' anywhere in a cell's text content, you could use:

Formula is:
COUNTIF(F13;".*yes.*")>0

the cell reference (e.g. 'F13') is also not particularly logical, the cell you have to put in here depends on how you dragged out the selection on the cells before selecting 'conditional formatting'. I found the only thing that worked for me was to drag top-left to bottom-right across the cells, and then enter the value of the bottom right cell in the formula (in this case 'F13'). Perverse i know. But if you do it like this, it will adjust each formula to match the current cell as you require.

[Sharon]

I have followed your steps for the conditional formatting. No matter what I type in the cell that has the it reacts to the second condition.. The "not equal to condition" What am I doing wrong?

[Solveig]

Hi Sharon,

It might be a syntax thing. Try doing another example with very simple info like =1, =2, and =3 as the conditions. Or it might be a cell format thing -- if you type 2 in a cell that's formatted as a date, then that might be the issue.

Solveig

[Pat]

Leandro's tips in his August 29th comment are superb! I never would've figured out how to make the conditional formatting depend on other data. (For example, I wanted to highlight all cells in column X where column J was non-zero and 'COUNTIF(j123:">0")>0' worked (where 123 is the last row of the highlighted conditional format region). See his comments for details.

Thanks!

[adam]

Leandro's tips in his August 29th comment are superb! I never would've figured out how to make the conditional formatting depend on other data. (For example, I wanted to highlight all cells http://www.batterygoshop.co.uk/apple/a1078.htm apple a1045 a1078 e68043 m9325 m9756 ,
in column X where column J was non-zero and 'COUNTIF(j123:">0")>0' worked (where 123 is the last row of the highlighted conditional format region). See his comments for details.

[David Sarnowski]

Is it possible to make the value of one cell affect the conditional formatting for the entire row that cell is in? For example, if the value of a cell is positive, can we change the background of the whole row?

Cheers,

Dave

[Solveig]

Hi David,

I don't think so -- there don't seem to be any functions that apply formatting to a cell. I think you have to do it one by one.

There is a STYLE() function but it can't be applied to particular cell or range.

Not saying it's impossible but there would need to be some serious function nesting to juggle everything.

[Ray Bernache]

RE: David Sarnowski
If I'm reading your question correctly?
Yes you can easily change one cell, to the entire row.
just highlight the range you want to affect.

So to make A1:Z1 turn Green when Z1 is Positive :
1) create a new style with a Green background (lets call it Positive)
2) highlight A1:Z1
3) The formula is : $Z1>0
4) Change "Cell Style" to "Positive"

you can copy/paste it as long as you have the $ preceding the column letter

[Deb]

Is there any way to have more than 3 conditional formats? I need 7.

[peter]

I'm just starting to study the Calc notion of formula. Apparently Calc cells can contain a literal value or a value derived by a formula. Are there means to apply a formula to a cell, referencing its content (not the content of other cells!), to produce a side effect? ("Side effects" such as applying a style, that is.) I can't find a way. You would think that Conditional Formatting is an example of that, but the formula language doesn't seem to have the notion of "current cell content." CELL("CONTENTS") would seem to be such a thing, but doesn't work. The documentation on this is terrible. CHALLENGE PROBLEM: For a collection of cells, I want a format applied to those whose text content contains the substring "hello world" Can it be done? How?

[peter]

Hi,

Oh, I was so close to answering my own question (the CHALLENGE PROBLEM above). Using Conditional Formatting use the formula FIND("hello world",CELL("CONTENTS")).

Nice book, btw, but it doesn't help me when I'm home and it is on my desk at work.

- Peter

[yanhong]

This web named: http://www.batterygoshop.co.uk/apple/a1078-battery.htm apple a1078 battery can proved you rich laptop bettery message,It's very cool.

[annyhaiyan]

Welcome to the http://www.shopgogo.ca/apple/a1078.htm apple a1078 battery , here you have unexpected harvest Oh, opportunities are available for the event can not be!

[john]

i want to set up a table with values of 1-10 and have each cells background change to show what number has been entered into the cell. how can i set up conditional formatting for more than 3 values?

[Solveig]

Hi John,

You might try this solution http://www.oooforum.org/forum/viewtopic.phtml?t=42316 from Villeroy. I haven't tried it myself but it looks like it's short of macros, at least.

Solveig

[PETERV]

I'd like to find out if there is a way to test for multiple conditions when using conditional formatting. In Excel you can use a formula to do this. I'm trying to come up with a way to format a column of numbers as follows:

Value
100 or less -> Bold Green
101 - 129 -> Bold Blue
> 130 -> Bold Red

The way Calc's conditional formatting is set up, it looks like you can check for 3 conditions but they must ALL be true for the formatting to be applied. In Excel, you can do "or" conditions with formulas. Please advise. If I can't figure this out, I'll have to use Excel, and I'd really hate to go back to that :)

[Solveig]

Hi Peterv,

That's exactly how OpenOffice formatting works, the way you want. I created a column of 10 numbers and 3 conditions, < 200 is green, between 200 and 400 is blue, over 400 is red. I applied the three conditions to all the cells. The first few were green, next were blue, biggest ones were red.

When applying 2 or more conditions, the one that is true is used. If you apply conditions that are both true, i.e. over 200 or over 250, the number 300 is going to use the first condition in the list.

To do ORs, i.e. if a cell is equal to "CA" or "MA" just create two conditions, and give them the same cell format if the condition is true.

HTH,

Solveig

[John]

when I do conditional formatting it formats the complete cell, I just want the conditional formatting to change the text color, not mess up my borders or anything else.

how do i do that?

thanks

[fourreux]

Hi

I'd like to format a row in a certain way, when the date in the cell in column "a" corresponds to a certain weekday.

I'm trying with the formula TEXT. But got no chance so far.

=TEXT(1;"TTT") in a cell gives me "So" (for german sunday). So, I marked A2:J32 and in conditional formatting i entered: TEXT($A2; "TTT")=1, while in column A2:A32 theres the date)

but this doesn't work ... help apreciated :-)

[stif]

I used this command to format weekends with another background color:

I marked all Cells i want to format and then in the conditional formating i used "Formula" and
IF(WEEKDAY($B4:$B368,2)>5)

in the row "B" i have my dates (row is formated as "date")

the only strange thing is, my daterow itself doesnt change its color on weekends, just the other rows i selected before...

cheers,
stif