63 Conditionally Format Data

 <  Day Day Up  >  

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).

See Also

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:

  • 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)

KEY TERM

graphics/newtermbw_icon.gif

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.


  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.

    graphics/08inf10.jpg
  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

    graphics/notebw_icon.gif

    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.

  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.

    NOTE

    graphics/notebw_icon.gif

    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.

  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.

 <  Day Day Up  >  


Sams Teach Yourself OpenOffice.org All In One
Sams Teach Yourself OpenOffice.org All In One
ISBN: 0672326183
EAN: 2147483647
Year: 2003
Pages: 205
Authors: Greg Perry

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net