Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I alternate grid background color in excel when a value of a single column changes?

Such as this:

enter image description here

I would want to do this with conditional formatting, but I can't figure out how to.

share|improve this question
i looked. Be damned if i can figure it out - without resorting to writing a macro. – Ian Boyd Feb 23 '11 at 2:57
Interesting idea. I'm sure I could probably figure out a way, but it would be a total kludge. Will be interesting to see if anyone does come up with something. – Iszi Feb 23 '11 at 4:42
up vote 4 down vote accepted

Conditional format formula



  • Range to apply formatting applies from row 2 down
  • Apply above conditional format to all cells in row 2 that require shading
  • paint cell format from row 2 to all rows that require format
  • that the value to check for change is in column B.
share|improve this answer
This makes Excel grind to a halt on my machine, FYI. Especially when scrolling down through the conditionally formatted rows. Is there any way to copy the dynamic formatting and permanently apply it so that Excel doesn't lag terribly? There's a much more efficient way to do this with a hidden column. – Triynko Feb 27 '13 at 1:24
The answer works, but as @Triynko said, it is very resource intensive. Perhaps this solution is better from a performance perspective:… – Steve Jul 19 '13 at 14:30

The ISODD method kept on crashing my excel (30,000 records), so I did it another way. I ran a counter next to the variable I wanted to cond format on (so in the OP example, A=1, B=2 (each time col2 is different from one row above, add one). Then I used added a column with the formula =MOD(col2,2). Which gives an alternating 1,0,1,0 (all As 1, all Bs 0, all Cs 1). Then conditionally formatted on that column. Worked!

share|improve this answer

You must log in to answer this question.

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