How to conditionally highlight cells in OpenOffice Calc?

Question

I have a big chunk of data in an OpenOffice spreadsheet. To make real information standout more, I would like cells with an absolute value over a certain threshold to be highlighted in red.

Is there a way to do this automatically?

Edit: I tagged as excel as well, because I would also be interested in a way of doing this for Excel.

Solution

Conditional formatting is what you're after.

It's been a long time, but I think in OpenOffice Calc it was under Format | Conditional Formatting. So:

1. Select the cells containing the value you want in red
2. Go to Format | Conditional Formatting
3. Select 'Cell Value is'
4. Select 'Greater than'
5. Enter your threshold
6. Choose the formatting you require

In Excel, the steps involved depend on your version, however, the basic premise is the same.

For versions earlier than or Excel 2003, the menu option is the same too.

For 2007/2010, you need to use the ribbon.

1. You still select the cells first
2. Then select the Home tab
3. Look in the styles group, click Conditional Formatting
4. Hover the mouse over the option that says 'Highlight Cells Rules'
5. Select 'Greater than...'
6. Enter your threshold
7. Select your required format (red text)

Comments

[Peltier]

Thanks for a great answer! You really solved my problem!