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.
Before You Begin
59 Format Cells
61 Set Up Calc Page Formatting
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).
69 Protect Spreadsheet Data
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:
| || |
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.
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.
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.
| || |
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 64 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.
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.
| || |
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.
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.