Conditional formatting is a useful feature to highlight certain values in your spreadsheet. For example, define a condition and if the condition is true, a style is applied to each cell that fulfills this condition.
Note: Enable AutoCalculate
Before you apply conditional formatting, choose Tools › Cell Contents › AutoCalculate. You should see a check mark in front of AutoCalculate.
Procedure 11.4: Using Conditional Formatting
Define a style first. This style is applied to each cell when your condition is true. Use Format › Styles and Formatting or press F11. For more information, see Procedure 11.1, “Creating a Style”. Confirm with OK.
Select the cell range where you want to apply your condition.
Select Format › Conditional Formatting › Condition from the menu. A dialog opens.
You now see a template for a new condition. Conditions can operate in multiple modes:
Cell value is
The condition tests if a cell matches a certain value. Next to the first drop-down box, select an operator such as equal to, less than, or greater than.
Formula is
The condition tests if a certain formula returns true.
Date is
The condition tests if a certain date value is reached.
All Cells
This mode allows creating data visualizations that depend on the value of a cell, similarly to Cell value is. However, with All Cells, you can use one condition to apply an entire range of styles.
The types of styles that can be used are color scales (cell background color), data bars (bars with changing width in the cell) and icon sets (an icon in the cell).
For example, a color scale allows assigning 0 a black background and 100 a green background. All values in between are calculated automatically. For example, 50 receives a dark green background.
For this example, keep the default: Cell value is.
Select an operator and the value of the cell you want to test for.
Choose the style you want to apply when this condition is true or click New Style to define a new appearance.
If you need additional conditions, click Add. Then repeat the previous steps.
Confirm with OK. Now the style of your cells has changed.