January 31, 2007

Conditional Formatting in OpenOffice Calc Spreadsheets

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

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

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.

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.

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.

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.

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?

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

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?

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:

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

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.