6

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?

Improve this question
8
  • 2
    The formula works for me in excel 2013, without problem.
    – Scott Craner
    Sep 14, 2016 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, 2016 at 20:21
  • 1
    Really silly, but copy and paste this in, see if it works: =MOD(ROW(),2)=0
    – BruceWayne
    Sep 14, 2016 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, 2016 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, 2016 at 22:06

2 Answers 2

Reset to default
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
Improve this answer
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, 2017 at 11:26
  • It did not work to me
    – lfvv
    Dec 24, 2017 at 16:30
7

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/

Improve this answer
1
  • Please give more information in order to help OP
    – yass
    Apr 7, 2017 at 22:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .