You’ve probably seen spreadsheets produced using Microsoft Excel that have this kind of look. You might have seen files produced using OpenOffice Calc that look like this. Excel 2007 comes with many (and I mean lots) of styles for the tables, many of which include alternating row colors. Calc comes with about 10 styles in total, 3 of which use alternating styles, none of which look very “professional” in my opinion (I’d never use them), and all of which are unwiedly and unconfigurable. So, aside from selecting the rows individually one by one and setting the alternating colors (then inserting a cell and having to redo it all over again, ugh), does OO Calc 3 provide an easy way to do this?
Not so far as I can tell. Google showed up a couple of options, all of which were either too hard to setup or too difficult to change once it was setup. I have to conclude that OO3 doesn’t provide a pre-built function for this. But it does give the tools necessary to create one. I’m talking about the conditional styling. Here’s what I do.
We want to apply a conditional style that says “If this cell is even then make it color 1, if it is odd then make it color 2″. Conditional formatting relies on styles being defined. You can’t just tell the Conditional Formatting box that you want the cell to be color 1. You have to tell it that you want the cell to be style1. No problem, all we need to do is create a style based off the default that has a different background color.
If you’re styles window isn’t showing, just go up to the Format > Styles and Formatting option from the menu and it will. In my window I already have the styles defined, but you want to right click in the window and select “New”. Set the name of the style (as you can see, I’ve given mine obvious names for what I used them for). Go to the Background tab and select your desired color.
Click ok to exit the dialogue box. You’ll probably want to create 2 styles like I have, one for your “light” row and one for your “dark” row. Once you’ve done that, we’re ready to setup the alternating row colors in the actual table. Select the cells that you want to apply the alternating row color to and go up to Format > Conditional Formatting like I’ve done below.
Once you’re in the Conditional Formatting dialogue box, we need to set 2 conditions. The first one is “if cell is even” and the other one is “if cell is odd”. Every cell falls into one of these two categories in an alternating fashion. By default the dialogue box asks for “Cell Value”. You need to drop that box down and select “Formula”. You also need to tick the 2nd condition box and drop that down to “Formula” as well. It doesn’t matter what order you enter these in, but the formula for the odd cell is:
MOD(ROW();2)==1
And the formula for the even cell is:
MOD(ROW();2)==0
Then select the appropriate style for each i.e. you’re newly created styles.
Click OK to apply the settings and your rows should be alternating with the colors you set in the styles. Woohoo! 
Some notes:
- To edit the colors that are applied, just edit the styles. Click on your style then click on “Edit” and just go change the background color.
- Conditional formatting is applied on a per-cell basis just like when you make the text in one cell bold. If you copy a cell from somewhere else in the sheet that is not part of the alternating rows, and paste it into the sheet (overwriting some other data perhaps), the pasted cell will not have the conditional formatting and thus will probably not have the right color. The easiest way I have found to fix this is to re-select the cells in the alternating colored table, and re-apply the conditional formatting. This is easy because when you go into the conditional formatting dialogue box, the formulae should already be there, and clicking OK will apply it to the new cell.
- The formatting is dynamic. That is, as you insert rows, the numbers of the rows change, and so the table automatically re-adjusts for the correct alternating row colors.
