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:

Remove any already existing background formatting.

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

Select Format=>Style and Formatting, or press F11

Click on the Cell Styles button

Click the New Style from Selection button

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

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

Go to the Background tab and select the appropriate color

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

Select the required region of the spreadsheet

Select Format=>Conditional Formatting

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

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

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.

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.