Skip to content

LibreOffice Calc Alternate Background Color

The background row or column color is determined by the use of "conditional formatting" and which color is used is determined by the chosen cell style using "styles and formatting". Using this method well allow for adding or deleting a row or column without having to fix the alternating background colors.

Environment

Instructions were tested using the following.

  • Arch Linux x86_64
  • LibreOffice v7.5.0

Create New Cell Styles

These steps will create two styles, one for even row/column and one for odd row/column.

  1. Open "Styles and Formatting" using one of the following options.
    • Menu Bar: Left mouse click on "Styles" > "Manage Styles".
    • Notebook bar Tabbed: Left mouse click on "Home" > next to "Conditional" click on the drop down menu > "More Styles".
    • Keyboard shortcut: CTRL+F5 or F11
  2. Create a new cell style.
    1. Click on the "Cell Style" icon (set square (triangle) with cells).
    2. Click on the "+" icon (plus sign).
    3. Enter the new style name (e.g. Background Even Row Color).
    4. Click on the "+" icon (plus sign).
    5. Enter the new style name (e.g. Background Odd Row Color).
  3. Set cell style background color.
    1. Right mouse click on the cell style "Background Even Row Color" then left mouse click on "Modify...".
    2. Left mouse click on "Background" and then left mouse click on the desired background color (e.g. Light Gray 4).
    3. Left mouse click on "Ok".
    4. Right mouse click on the cell style "Background Odd Row Color" then left mouse click on "Modify...".
    5. Left mouse click on "Background" and then left mouse click on the desired background color (e.g. White).
    6. Left mouse click on "Ok".

Note

If desired to separate cell styles for row and column, create two cell styles for each row (Background Even Row Color/Background Odd Row Color) and column (Background Even Column Color/Background Odd Column Color).

Apply Alternate Background Row Color

  1. Complete steps in "Create New Cell Styles".
  2. Select the range of cells that you wish to have alternating background colors.
    1. Left mouse click on the first desired cell in the range.
    2. Hold down the shift key then left mouse click on the last desired cell in the range.
  3. Use conditional formatting use one of the two directions.
    • Menu Bar: Left mouse click on "Format" > "Conditional" > "Condition...".
    • Notebook bar Tabbed: Left mouse click on "Home" > "Conditional" > "Condition...".
  4. Edit "Condition 1" in the "Conditional Formatting" dialog.
    1. Left mouse click on "Cell value" and change the selection to "Formula is".
    2. In the adjacent box enter without double quotes "ISEVEN(ROW())".
    3. Under "Apply Style" left mouse click on drown down menu and left mouse click on "Background Even Row Color".
  5. Add "Condition 2" by left mouse click on the "Add" button.
  6. Edit "Condition 2".
    1. Left mouse click on "Cell value" and change the selection to "Formula is".
    2. In the adjacent box enter without double quotes "ISODD(ROW())".
    3. Under "Apply Style" left mouse click on drop down menu and left mouse click on "Background Odd Row Color".
  7. Left mouse click on the "OK" button.

Example Screenshot

Screenshot example of a spreadsheet with alternate background row color.

Apply Alternate Background Column Color

  1. Complete steps in "Create New Cell Styles".
  2. Select the range of cells that you wish to have alternating background colors.
    1. Left mouse click on the first desired cell in the range.
    2. Hold down the shift key then left mouse click on the last desired cell in the range.
  3. Use conditional formatting use one of the two directions.
    • Menu Bar: Left mouse click on "Format" > "Conditional" > "Condition...".
    • Notebook bar Tabbed: Left mouse click on "Home" > "Conditional" > "Condition...".
  4. Edit "Condition 1" in the "Conditional Formatting" dialog.
    1. Left mouse click on "Cell value" and change the selection to "Formula is".
    2. In the adjacent box enter without double quotes "ISEVEN(COLUMN())".
    3. Under "Apply Style" left mouse click on drown down menu and left mouse click on "Background Even Column Color".
  5. Add "Condition 2" by left mouse click on the "Add" button.
  6. Edit "Condition 2".
    1. Left mouse click on "Cell value" and change the selection to "Formula is".
    2. In the adjacent box enter without double quotes "ISODD(COLUMN())".
    3. Under "Apply Style" left mouse click on drop down menu and left mouse click on "Background Odd Column Color".
  7. Left mouse click on the "OK" button.

Example Screenshot

Screenshot example of a spreadsheet with alternate background column color.