4

I'm trying to set up alternating row styling on a selection. I click on the first row and drag down to row 145 (where data ends).

Then I go into Conditional Formatting, choose new rule, and am trying to enter this formula:

=MOD(ROW(),2)=0

When I click OK, I get this not very helpful error in Excel 2016:

There's a problem with this formula. 
Not trying to type a formula? When the first character is 
an equal (=) or minus (-) sign, Excel thinks it's a formula.
<snip>

What is wrong with the formula?

8
  • 2
    The formula works for me in excel 2013, without problem. – Scott Craner Sep 14 '16 at 20:20
  • 1
    Maybe if you told us what you were hoping the formula would do? – i did not pay the royalties Sep 14 '16 at 20:21
  • 1
    Really silly, but copy and paste this in, see if it works: =MOD(ROW(),2)=0 – BruceWayne Sep 14 '16 at 21:22
  • @alwayssummer: As I wrote, I'm trying to set up alternating row styling. The formula uses the MOD function to test whether we're on an even-numbered or an odd-numbered row. – TRomano Sep 14 '16 at 21:55
  • If BruceWayne's suggestion doesn't work out, try omitting the "=0" and doing an integer test rather than a Boolean test in your conditional formatting rule. – picobit Sep 14 '16 at 22:06
4

Try to use ";" instead of "," before number.

Background: Some locales use a "List separator" character that is not the comma character. If you are outside the US, this might be the case for you.

Details here: http://datapigtechnologies.com/blog/index.php/why-your-version-of-excel-only-accept-semicolons-in-formulas-instead-of-commas/

1
  • Please give more information in order to help OP – yass Apr 7 '17 at 22:12
2

Just remove the =0 part and it should work.

When you are creating the rule, choose the Use a formula to determine which cells to format option, then input the following as the formula:

=MOD(ROW(),2)

If you want to format the other set of rows, use the following:

=MOD(ROW(),2)-1
2
  • Thanks, this worked. Sorry for the delay, I was unable to log in properly for some reason. I don't see a button to accept the answer. – Tim Apr 8 '17 at 11:26
  • It did not work to me – lfvv Dec 24 '17 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.