Ask Your Question

Calc alternating row colors? LO

asked 2019-09-11 16:05:47 +0200

JoelH gravatar image

updated 2019-09-12 11:19:36 +0200

Hi I can not make the rows alternate with different colors? Tryed to search but nothing works

How do i make the rows have f ex white and grey alternating?

Thanks J

image description

edit retag flag offensive close merge delete



Your formula is incorrect.
You wrote: mod(row()0,2)=1 but should read MOD(ROW(),2)=1 and important: I'm not aware where you from, so two others things are important:

  • The parameter delimiter for functions in your localization may be ; instead of , (English)
  • You may have localized function names

Since this is an English site, I do use the English delimiter and English function names.

From what I see Villkor is Swedish for Condition and if I set my LibreOffice language to Swedish, then indeed it tells me that the delimiter in Swedish is ;: Thus: MOD(ROW();2)=1 etc...

Opaque gravatar imageOpaque ( 2019-09-12 11:26:06 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-09-11 18:47:17 +0200

Opaque gravatar image

updated 2019-09-12 11:15:22 +0200


Solution 1: Reusable - Define your own Auto format

  • Select range A1:D1 and set the color (e.g. white)
  • Select range A2:D2 and set the alternate color (e.g. grey)
  • Select range A3:D3 and set the color (e.g. white - step 1 needs to repeated here)
  • Select range A1:D4 (important to have on uncoolorized line)
    (Updated as per comment of OP)
  • Go to menu Format -> AutoFormat Styles
  • Click Add - button, and deselect every option but not Pattern
  • Provide a Name (e.g. AltWhiteGrey)
  • Click button OK

Now you can use this Autoformat for any arbitray range (Selecting the range) and using Format -> AutoFormat Styles

Solution 2: - Use Conditional formatting

  • Define a cell style with white background color (e.g. BGWhite)
  • Define a cell style with grey background color (e.g. BGGrey)
  • Add a conditional format using
    • Condition 1: MOD(ROW(),2)=1 - Apply Style 1 (BGWhite)
    • Condition 2: MOD(ROW(),2)=0 - Apply Style 2 (BGGrey)
    • Cell Range: Add here the cells, where you want to apply the format

Drawback of this solution: Conditional Formats take precedence over direct formats.

Update to Solution 2 - related to comment of OP

For each cell, two conditions need to be specified. One if the row of the cell is even and one, if it is odd (see also answer of @erAck - while his solution uses functions ISVEN(),ISODD(), my solution makes use of MOD() function

image description

edit flag offensive delete link more


Hi Does not work for me?!

Solution 1. When i go to menu: Format -> AutoFormat Styles I can not klick Add?! (greyed out...)

solution 2: Nothing happens to my cells?

Should the formula be the same for condition 1 and 2 ?! mod(row()0,2)=1

I can not understand why it does not work :-(


JoelH gravatar imageJoelH ( 2019-09-12 10:42:13 +0200 )edit

Solution 1: If you cannot use Add then you have not enough cells preselected and correctly formatted and you need more cells to fill so LibreOffice recognizes the pattern.

Solution 2: You obviously never used Conditional Formatting - See screenshot in my answer

Opaque gravatar imageOpaque ( 2019-09-12 10:56:32 +0200 )edit

Thank you i will try again Where is the screenshot?

JoelH gravatar imageJoelH ( 2019-09-12 11:03:20 +0200 )edit

Sorry for my comment - there was of course a typo in the original version and you are right that the conditions need to be different.

Just saw that Solution 1 had a typo too:
Select range A2:D4 (important to have on uncoolorized line) must read:
Select range A1:D4 (important to have on uncoolorized line)* - that's why you don't see Add button.

Updated Solution 1

Opaque gravatar imageOpaque ( 2019-09-12 11:07:06 +0200 )edit

Upploaded my screenshot Can not understand why i can not do it?!

JoelH gravatar imageJoelH ( 2019-09-12 11:20:07 +0200 )edit

Wohoo :-) Got solution 1 working! Thank you for uppdating

JoelH gravatar imageJoelH ( 2019-09-12 12:40:01 +0200 )edit

Thanks, solution #1 worked for me and was very helpful. First it solved my issue with applying alternating row colors and second it teached how to create your own auto format style. Would like to upvote it but I'm lacking points. Just created this account to say thanks.

kaabssi gravatar imagekaabssi ( 2019-12-04 09:23:18 +0200 )edit

answered 2019-09-11 18:31:35 +0200

erAck gravatar image

Use a conditional formatting with two conditions, one condition with formula ISEVEN(ROW()) applying a style with one color, and a second condition with ISODD(ROW()) applying a style with another color. Obviously does not adapt to hidden rows, only the row number is considered.

edit flag offensive delete link more


Hi Nothing happens to the rows... Should there be more in this ISEVEN(ROW()) ISEVEN(ROW(A2)) ?

JoelH gravatar imageJoelH ( 2019-09-12 10:43:32 +0200 )edit

No, ROW() without argument evaluates the current actual row. Tried and works for me. Having the two conditions, one with ISEVEN() and the other with ISODD(), in one conditional format ensures that exactly one condition is true on alternating rows.

erAck gravatar imageerAck ( 2019-09-12 12:32:31 +0200 )edit

I spent 30 minutes doing this over and over and it does not work for me. Problem is it's hard to track all the changes and what people are doing to get it to work. A single entry showing exactly how it should be done wold very much be appreciated.

Sergerator gravatar imageSergerator ( 2021-03-18 00:43:01 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2019-09-11 16:05:47 +0200

Seen: 11,014 times

Last updated: Sep 12 '19