Section 64. Conditionally Format Data

64. Conditionally Format Data

BEFORE YOU BEGIN

60 Format Cells

62 Set Up Calc Page Formatting

SEE ALSO

70 Protect Spreadsheet Data

Conditional formatting enables you to make your spreadsheets respond to the data they contain. When certain conditions arise, you can draw attention to particular cell entries by automatically making those cells display differently from the cells around those exceptions.

KEY TERM

Conditional formatting The process of formatting cells automatically, based on the data they contain. When the data changes and triggers a predetermined condition, Calc automatically changes the cell's format.

64. Conditionally Format Data.

Most often, users place conditional formats on cells that they want to watch (for example, for extraordinarily high or low conditions that might require special attention).

The way you indicate if a condition is met is to specify a value and a condition that must become true before the format takes place. Here's a list of the available conditions:

• Less than

• Greater than

• Equal to

• Not equal to

• Less than or equal to

• Greater than or equal to

• Between (requires two values for the condition)

• Not between (requires two values for the condition)

1.	Request Conditional Formatting Click to select the cell or range on which you want to apply a conditional format. Select Conditional Formatting from the Format menu. The Conditional Formatting dialog box appears.
2.	Enter the Format Conditions You can apply up to three conditions for the cell you've selected to format conditionally. Most often, you'll probably only need to use a single conditional format. Click to select either Cell value is or Formula is to indicate how the conditional format is to apply. If the format depends on a value, as it usually will, select Cell value is . If the format depends on a formula, select Formula is and enter a cell reference for that formula to the right of the option. Select the Cell Style setting that you want Calc to use for the data's display if the condition is met by data within the cell. Any time the data changes to make the condition true, Calc applies the format style to the cell. NOTE The available formats are Default, Heading, Heading1, Result , and Result2 . If you want to display a different format, you'll have to select one of these styles and change its format so it appears the way you prefer (see 65 About Calc Styles and Templates ). Select the condition, such as Less than . If the cell's value ever goes below the value you enter next to the condition, Calc changes the cell's format to match the style you select. Two of the conditions, Between and Not between , require two values for the condition to be matched. If you select either of these conditions, Calc provides two text boxes so you can enter both values that define the condition.
3.	View the Spreadsheet Again The Conditional Formatting dialog box consumes quite a bit of space, and while you're filling it in, you may need to see your data on the sheet beneath the dialog box. If so, click the Shrink button. Calc temporarily shrinks the Conditional Formatting dialog box to a thin line so you can view the spreadsheet once again. Click the Shrink button once again to return the Conditional Formatting dialog box to its original size so that you can complete the formatting. After setting up the conditional format, click OK to apply it.
4.	Test the Format If the cell never matches the condition, the cell's format will remain unchanged. If, however, the cell does pass the condition, Calc applies the format so it becomes noticeable to anyone looking at the spreadsheet.

NOTE

Calc constantly monitors cell contents. Every time you enter a new value or the spreadsheet recalculates, Calc tests all the conditional formats and applies any of those formats if needed.