My Technical Notes

Wednesday, 29 April 2015

OpenOffice Calc: Alternate row coloring

OpenOffice Calc does not have the "table" feature that Excel has, therefore you will need to use other features of the program to get the same functionality.

One aspect of Excel tables I like is the alternate row coloring. This makes it easier to distinguish between rows. To mimic this with OO Calc, we use a feature called "conditional formatting".

Let's says that you have the following table:

Steps to get alternate row colors:

  • First create a new style:
    • Click on `Format` → `Styles and Formatting`.
    • Right click the white area and selecting `New`.
    • Give it the name `AltRow`, and set the background to a color of your choice (I have chosen light green).
  • select cell `A2` (Nick).
  • Now select all cells using `Ctrl a` or clicking on the grey area where the column and row headers touch.
  • Click on `Format` → `Conditional Formatting`
    • For `condition 1`, use `Formula is` instead of `Cell value is`
    • Set the formula to
      
      AND(ISEVEN(ROW());NOT(ISBLANK(A$1)))
            
      The row must be even (2, 4, 6 ...) and the cell in row 1 must have a value (i.e. column must have a title).
    • Lastly, Set the `Cell Style` to `AltRow`.

The result:

Notice that columns with no title are not colored, and the row coloring extends all the way down.

No comments: