Conditional Formatting – OpenOffice Calc [MS Excel & Numbers]

If you are interested in applying a format for a cell based on the value in a cell, then you are interested in something called conditional formatting. Below is a tutorial for OpenOffice.org, NeoOffice and LibreOffice users. Links to tutorials for MS Excel and Mac Numbers users are provided at the bottom of the page.
This tutorial is designed to assist in setting the formats and applying rules to those formats using the following broad steps.
  • 1. Conditional Formatting – based on values in cells
  • 2. Select Conditional Formatting
  • 3. Set the Rules for the Formatting
  • 4. You can set multiple rules
  • 5. Create New Cell Styles
  • 6. Apply the Styles Based on Rules
  • 1. Conditional Formatting – based on values in cells

    Take a range of cells with values that you wish to format.

    2. Select Conditional Formatting

    First, select/highlight the range of cells that you wish to format.

     

    From the Format menu, select “Conditional Formatting”

    3. Set the Rules for the Formatting

    Set the rules that need to be met in order to apply the format.

    Eg. If the cell value is greater than 9, then a different format will be applied.

    4. You can set multiple rules

    You can set up more than one rule, and apply a different format to each rule.

    5. Create New Cell Styles

    Click on “New Style”

    Name the new style that you want to apply for the rule

    Then change the format to meet your needs.

     

    For example, I am going to make the background green for all cells that have a value greater than 9, and any less than or equal to 3 will be highlighted with yellow. So I am creating New Styles, which I call green_bk and yellow_bk, which will be applied to each rule.

     

    Click on OK when you’re done.

     

    6. Apply the Styles Based on Rules

    You will then have a spreadsheet that has styles applied depending on the value in the cell.

    Microsoft Office

    If you are after a Microsoft Excel version of this tutorial, then there is an excellent one over at WikiHow.

    Mac: Numbers

    The following is a vide tutorial on the Apple site. You will need the Quicktime plugin to view the video.

      Additional Resources

      Comments

      1. I am having trouble with applying conditional formatting to a blank cell. That is, a cell awaiting an input. In Oo 4.1.1, in the above example a blank cell would, apparently be considered to be a value of zero and would, therefore, have a background of yellow. Specifying a third condition for the cell as “” [null or blank] doesn’t seem to work.

      Trackbacks

      1. […] So all that was left for me to do was to test each date in row 3 against the task start and end dates, and return a 1 or 0 to the cell. Then I could tell the spreadsheet to format the cell depending on the value returned. This resulted in bars. If you are not sure about conditional formatting, check out my post on conditional formatting. […]

      Speak Your Mind

      *