Using Conditional Formatting

From Apache OpenOffice Wiki
Jump to: navigation, search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.



Using Conditional Formatting

You can set up cell formats to change based on conditions that you specify. For example, in a table of numbers, you can show all the values above the average in green and all those below the average in red.

Documentation note.png To apply conditional formatting, AutoCalculate must be enabled. Choose Tools → Cell Contents → AutoCalculate.

Conditional formatting depends on the use of styles. If you are not familiar with styles, please refer to Chapter 4. An easy way to set up required styles is to format a cell the way you want it and click the New Style from Selection icon in the Styles and Formatting window. You can define new cell styles before setting up conditional formatting or do it from within the conditional formatting dialog.

To set up conditional formatting:

  1. In your spreadsheet, select the cells to which you want to apply conditional formatting. You may need to know which cell is the active cell to set up the formatting. If you select several cells, note which cell is active afterward.
  2. Choose Format → Conditional Formatting from the menu bar.
  3. On the Conditional Formatting dialog (Figure 57), enter the conditions. Click  OK  to save. The selected cells are now formatted in the relevant style.
Cell value is / Formula is
Specifies whether conditional formatting is dependent on a cell value or formula. If you select cell value is, the Cell Value Condition box is displayed, as shown in the example. With this box, you can choose from conditions including less than, greater than, between, and others.
Parameter field
Enter a cell reference, value, or formula in the parameter field or in both parameter fields if you have selected a condition requiring two parameters. You can also enter formulas containing relative references. The relative references are defined with respect to the active cell if multiple cells are selected. For example, if you selected the range A1:A10 by dragging down from A1, then A10 will be the active cell. Any relative references should be written as would be appropriate for A10. The reference will be adjusted automatically for the other cells in the range.
AOO41CG02 057.png
Figure 57: Conditional formatting dialog


Cell style
Choose the cell style to be applied if the specified condition matches. You can define the style by clicking on the  New Style  button. See the Help for more information and examples of use.
To apply the same conditional formatting later to other cells:
  1. Select one of the cells that have been assigned conditional formatting.
  2. Copy the cell to the clipboard.
  3. Select the cells that are to receive this same formatting.
  4. Choose Edit → Paste Special.
  5. On the Paste Special dialog, in the Selection area, select only the Formats option. Make sure all other options are not selected. Click  OK .
AOO41CG02 057a.png
Personal tools