How to quickly apply color schemes to a spreadsheet with OpenOffice or LibreOffice

How do you quickly create a spreadsheet where, for example, every other line has a background of a different color? Here are a couple of methods.

The first one is faster (and works even in controlled environments where you can’t install extensions) if you want white rows alternated with colored ones: define a cell style and apply it only to cells in even (or odd) rows. Here is the detailed procedure:

1. Remove any already existing background formatting.

   1. Create an appropriate cell style with the color that you want as background:

   2. Select Format=>Style and Formatting, or press F11

   3. Click on the Cell Styles button

   4. Click the New Style from Selection button

   5. The Create Style pop-up window will appear. Type in it the name you want to give to your style and click OK

   6. Now the new style name will appear in the Styles list. Right-click on it name and select Modify

   7. Go to the Background tab and select the appropriate color

   8. Now close the Styles window and apply the new cell style where needed:

   9. Select the required region of the spreadsheet

   10. Select Format=>Conditional Formatting

   11. Select “Formula is” from the drop-down menu under the “Condition 1” label.

   12. Type “MOD(ROW();2)”, without the quotes, into the formula box

   13. Select your new cell style from the Cell Style drop-down menu.

Here is the result. The MOD operator, when used as in the formula in step 3.4 returns true only on odd row numbers: to select even rows you should use “MOD(ROW()+1;2)”. A formula like “MOD(ROW();3)”, instead, would select every third row.

An extension to quickly color spreadsheet table rows with OpenOffice Calc

If you can install extensions on the OpenOffice copy that you use, you can also use the Color2Rows extension. Go to its home page, download it (it’s a file called Color2Rows-{Version-Number}.oxt and then open it with OpenOffice. This action will start the OpenOffice Extension Manager.

After you accept to install the extension, it will appear as a button in the toolbar:

When you need to color a section of the spreadsheet, select it with the mouse then click on the Color2Rows button. Choose the colors for the lines inside the listing fields and click on the “Performing” button. Color2Rows will do the rest:

Unfortunately, unlike the first method, the coloring scheme will be messed up when you add or delete rows, but it may be faster to get more sophisticated coloring schemes than the first method.

(source: suggestion from B. Barker on the OOo users list)