 # Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]

Share

Here is a quick home work on excel conditional formatting.

Lets say you have data as shown below to left and you need to show zebra lines whenever the value changes (see right). Your home work is simple. Just figure out how to write conditional formatting rules to add zebra lines.

The data set is here.

Go..

PS: Problem inspired from this post on Chandoo.org forums. Don’t cheat.
PPS: Here is some beautiful help on conditional formatting.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

## Related Tips

### 82 Responses to “Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]”

1. Deependra Vikram says:

I Solved it 🙂

• Manish says:

=mod(row(),2)=1

This will work

2. Amit Goyal says:

if(mod(code(a2),2)=0)

Use this formula in conditional formatting.

3. Chandoo says:

@Amit... that formula will not work if the data has values other than ABCD,.... in that order.

4. Chuck Bily says:

siimple!
In conditional Formating use the rule: if A2A3

5. Chuck Bily says:

siimple!
In conditional Formating use the rule: if A2 "not equal" A3

6. Godzilla says:

All I have done is cell not equal to B3 then pasted down.

If they do not match it highlights.

7. Jan Høgh says:

Once I figured out that Excel treats formulas in Conditional Formatting as array formulas, it was straightforward.

=MOD(SUM(IF(\$B\$3:B15=\$B\$2:B14;0;1));2)

8. Amit Goyal says:

@Chandoo:

In that case, do this:

C3 =1
C4 = if(B4=B3,C3,C3+1)

This will give us a numbered list, starting 1, that will appear alongside the original list. Then use the formula previously written to do conditional formatting based on values in column C.

9. VaraK says:

Hi,

Well, I used a helper column (May be, we can do it wothout one, but for now, I can only think this).

Formula for Helper column F: =IF(D3=D2,F2,F2+1) F2 is just blank or we can make it zero. This formula is like a counter, each time it sees a change in the data it increments 1 to the count. Effectively, that gives us count of unique values from the data set we have.

Try this formula : =MOD(F3,2)=1...Then select a format
=MOD(F3,2)=0...then select a format

And we have the zebra lines. Hope this helps. Looking forward to a much more easy solution. I'll keep trying for that as well.

10. VaraK says:

Jan,

When I tried your formula, all the rows got colored. Did I miss anything?

11. Deependra Vikram says:

I think people are coming up with Kewler ways..If my Data set is in D3:D17 I simply put =+IF(D3=D2,E2,NOT(E2)) which is kinda similiar to Varaks Solution...Where in corresponding E cells(Helper Column)... I have Trues and False Populated ....:P .....It sure changes with every change in data but then uses more realestate and Is clumsy :(...Ill see the better solutions when I get back home...Btw Chandoo..Great Blog I must say...I never had the chance to thank u...You make most of my day easier and creative....

12. Deependra Vikram says:

Holy Crap!!!Chandoo Came with the Same solution...I guess I can see the link for better solution :P...I just saw the solution link..And their it was :)...No doubt I am getting intelligent...Beer on me guyz...Time for my Nirvana...

13. Luke Moraga says:

You could use a formula to count unique values within the MOD function (no helper column required). It does require data set to be sorted. Assuming your data set starts in A1:

=MOD(SUM(IF(FREQUENCY(MATCH(\$A\$1:A1,\$A\$1:A1,0),MATCH(\$A\$1:A1,\$A\$1:A1,0))>0,1)),2)

14. Deependra Vikram says:

@ Luke...Super Dude..Saved My Real Estate Property..and then it seems it is self populated..In the other Solutions the helper Cells have to be dragged down...You can hav Ma Beer... 😛

15. Jan Høgh says:

@Varak
It depends on how you select the area when entering the conditions. If you selected from the top down, it would colour all the cells.
I had selected the entire list, starting from the bottom when I entered this. Must be a habit from my days selecting with the mouse 🙂

Of course, this is a lot simpler with a helper column. 😉

16. Chuck Bily says:

I didn't get it!
Why we need this stuff of formula, helper colkumn and so on?
As I posted above, it's enough to write the condition formula " B3 not equal B4" and set pattern of cells B3,B4... to change if condition is satisfied. For me it's enough, isn't it?

17. Magda says:

surprisingly my Ex2003 accepts an array formula for conditional formatting:
=1=MOD(SUM(1-(\$B\$3:\$B3=\$B\$2:\$B2)*1),2)
And if it would not, it should accept a named formula "=MOD(SUM(1-(\$B\$3:\$B3=\$B\$2:\$B2)*1),2)" entered as a named formula while acitive cell is in row 3.

• Nathan says:

Thank you so much for this solution, this was a huge help. I've used this several times. Both options work perfectly!!!

18. Magda says:

@Chuck Bily
IF one wants to have all "A" grey and all "B" white, and then "C" grey again - he needs such a formulas. Your solution makes just first "A", first "B", first "C" grey (and if there are unique letters one by one - they are all grey). It could work with underlining cells instead of filling them. With a lot of data your solution would be more effective: less calculations -> less time required to calculate a spreadsheet. But there is rather little fun in finding such a solution 😉

19. DQKennard says:

This has been something I've occasionally really wanted to do, but couldn't figure out (at least in the time available at the time). Thanks. I really like Jan and Magda's variations on doing it with mod and sum on the compared array ranges. Selecting an entire table, then using changes in values in the sort column to change colors for all cells in the row(s) worked like a charm.

Now, what I'd *really* like to do would be to be able to identify the primary sort column and do the format change based on *that* column, but the Intarwebs is not yielding a good way of determining that piece of information.

20. Ian says:

But using SUMPRODUCT is so much nicer 🙂

=MOD(SUMPRODUCT(--(B3:B21=B4:B22)),2)

21. Ian says:

And if you want to make it more generic you can set the range using an OFFSET.

=MOD(SUMPRODUCT(--(OFFSET(\$B3,0,0,COUNTA(\$B:\$B),1)=OFFSET(\$B3,1,0,COUNTA(\$B:\$B),1))),2)

22. Rohit1409 says:

Its so simple..

Select Cell D3,

Go to format > conditional formatting --> choose formula is --> =B3D3

and set format conditions

now the moment it differs from original set it will highlight 🙂

Hows it Chandoo !!!

Regards
Rohit1409

23. Rohit1409 says:

B3D3

is

B3 less than sign greater than sign D3

24. Ramnath says:

It looks simple. But I am not sure whether my below formula is what you expect.

I just did =A1B1 and it works for me.

Regards,
Ramnath

25. Ramnath says:

Opps! sorry. Not Equal to sign is not appearing on the screen.

So it is =A1 (Not Equal To) B1.

26. Simon P says:

Already solved and posted back in March - post #64 under the conditional formatting article!

In a nutshell, I used a helper column too: Formula for Helper column F: =IF(D3=D2,F2,F2*-1), F2 can be 1 or -1. This will alternate the values in the helper column between 1 and -1, just run your conditional format against that.
I like it as the helper column formula is very light, and the conditional formatting criteria is even lighter!
The MOD solutions are elegant - it's not as neat to have to create a new column - but they have to run calculations against the entire dataset for every cell. My guess is that with very large datasets, this could be hard work for excel!
If you want to run the stripes across multiple columns, this solution needs no more calculation, either.

27. Jan Høgh says:

@Simon P
I agree with your conclusion, helper columns are computationally lighter, and easier to troubleshoot too!

To cut to the bone, all you need in the helper column (A3)is =IF(B3=B2;A2;NOT(A2)).
That will return either TRUE or FALSE, and is easy to evaluate against in a conditional format.

For B3:
Formula is: =A3

Now, THAT'S lightweight!

28. Peter Ramirez says:

Ok people, I am really lost. I have tried all your formula and cannot get the results. Can I ask for more details on how you were able to make it work??

29. Uday says:

I hve used a helper column to include the following formula
"=IF((IF(B3=B4,1,0))=1,D3,(1-D3))". The formula is entered in the helper column for Row 2 onwards. Row 1 contains value '1' in the helper column.

What it does is, the moment the value changes in row, it toggles the value to either of 1 OR 0.

Then the conditional formating formula is applied for all '1' thereby giving the ZEBRA lines...

The explanation above is probably not very good... Maybe I can attach the actul XLSX file

30. Sid says:

Hi,
Please see Chip Pearsons site for some excellent examples on Data Validation:
http://www.cpearson.com/Excel/Duplicates.aspx
Not my solution but these worked examples are great!!

31. Magda says:

@Peter Ramirez
for Ex 2003
-open and select cell B3
-select option Formula is (instead default of Value is)
-copy formula : =1=MOD(SUM(1-(\$B\$3:\$B3=\$B\$2:\$B2)*1),2)
-paste
-select format (grey background)
-confirm

-copy active cell
-select area to be formatted
-then Paste special-> Formats

32. Chandoo says:

@all.. here is a little secret. There is a hidden sheet in the data file which has solution in it. Just unhide the sheet to see how I would have solved this problem. Not the most optimum way, but certainly easiest to understand or implement.

33. Chandoo says:

@Magda, Jon, Ian .. Interesting solutions. Can you elaborate the logic behind your formulas and tell us why they work?

34. Magda says:

Hi , I've tried to. But still can'see submitted post.

35. Magda says:

Hi,
third trial resulted in a comment:
"Duplicate comment detected; it looks as though you’ve already said that!"

A formula "=B2=B3" results with TRUE if cells are equal or FALSE if they aren't.
Array formula "=B\$2:13=B\$3B14" results with a table {FALSE/TRUE/TRUE/FALSE/TRUE/FALSE/TRUE/TRUE/TRUE/FALSE/FALSE/TRUE/FALSE}
Multiplication =(B\$2:13=B\$3B14)*1 makes Excel to treats FALSE/TRUE as numbers (quite often Excel treats TRUE as 1 and FALSE as 0 - but not in this case) and turns an array into {0/1/1/0/1/0/1/1/1/0/0/1/0}.
=1-(B\$2:13=B\$3B14)*1 makes {1/0/0/1/0/1/0/0/0/1/1/0/1} (I could simply use B\$2:13B\$3B14)
=SUM(1-(B\$2:13=B\$3B14)*1) just counts how many cells values an the table are different from cell above and if equal values are one by one - formula gives the same number. MOD - to distinguish even and odds for conditional formatting

36. Magda says:

Sorry, in former post should be:
(I could simply use B\$2:13"not equal"B\$3B14)
and in next line I mised one colon:
=SUM(1-(B\$2:13=B\$3:B14)*1)

37. Jan Høgh says:

I'll give it a shot.

=MOD(
SUM(
IF(
\$B\$3:B15=\$B\$2:B14;0;1
)
)
;2)

The IF() returns an array of numbers. A 0 is added to the array each time a cell is equal to the one above it, and a 1 is added for each time it isn't.
TIP: Conditional formating treats every formula as an array formula!

The SUM() takes this array as input and returns the current sum.

The MOD() then returns the residual of division by 2. For odd numbers this is 1, which is then interpreted as TRUE by the conditional formating. For some reason using ISODD() does not work here!

This is a quite calculation-intensive way of solving the problem. It is not suitable for large datasets, as it gets more intensive for every added line of data. Also, it is impractical, but it was a fun challenge!

38. Chaluva says:

I have numbers in cell (A1) 9845078861 which is =56 (9+8+4+5+0+7+8+8+6+1) = 56
would be great if you could advise me to sumup/total with the help of Excel Formula or Functions.

Thanks & Regards,
Swamy

39. Hui... says:

@Chaluva
Try this UDF
Copy and paste it into a Code Module in VBA
``` Function SumDigits(Target As Variant) As Long For i = 1 To Len(Target) If Mid(Target, i, 1) >= 0 And Mid(Target, i, 1) <= 9 Then SumDigits = SumDigits + Mid(Target, i, 1) End If Next End Function ```

To use it simply enter =SumDigits(A1) or =SumDigits(987654321)

• Chandoo says:

Hi Chaluva...

You can use SUMPRODUCT to get this. Assuming your number 987654321 is in cell B10, write this formula in an empty cell:

=SUMPRODUCT(MID(\$B\$10,ROW(OFFSET(\$A\$1,,,LEN(\$B\$10))),1)+0)
and press enter.

40. Werner van Waesberghe says:

I have not looked into it yet, but I would want to create a similar version that also works on records filtered on the same column, for example show only records with B, C and E and have C in another background color than B and E

41. Shmuel says:

Hi,

Chandoo, nice test as always. Simple but imaginative.

I have not yet looked at the suggested solution, here's my effort:

I used a helper coulmn (A) and used the following formula to increment by 1 with every change in the contents of the cell in the same row of the adjacent column B, =IF(A3=A2,A2,A2+1) [A2, the cell adjacent to the header, "Value", is left blank] and copy down to cell A15.

I then highlighted cells B3 to B15 and used the following formula in conditional formatting =ISODD(A3), this will give TRUEs and FALSEs down the list, then selected the Fill format Light Grey.
You could do away with the helper column by copying the incrementing formulas except for the equals sign and using that instead of A3 in the ISODD formula.

This is my clumsy effort!

42. Ravindra says:

Hi all.

I want the farmula for S.no in column A till the data in column B. (if you run the macro the S.no should display in coloumn A til where the data end in colunnmn B.

43. Clemens says:

Hi,

Just stumbled on this site.

A solution WITHOUT a helper column

=IF(B3=B2,A2,IF(A2=1,0,1))=1

Then format for the colour that you want.

Clemens

44. Shmuel says:

@Clemens Neat!

But I note that .....,(IF(A2=1,0,1)...... is always 1. Is there any reason why we can't just substitute that nested IF with 1?

It seems to give me the same result.

45. Clemens says:

@Shmuel,

There's an error in my initial post.

I just realised that my formating can be achieved with the given formula only if there is a helper column (in the case of the formula the helper column should be in column A.

The formula, at A3 shoudl be IF(B3=B2,A2,IF(A2=1,0,1)). This should be copied down to A15
The formula for conditional formating for the range B3:B15 should be =B3=1 and apply the color to this.

As per the homework, the colouring can be achieved by VBA, not sure if that would constitute a valid answer to the original question....

Clemens

46. Chaluva says:

Hi, Am trying to concatenate Date & Amount and looking with the help of Vlookup as dates may vary max by 2 days i could not get the accurate answer.

FYI... (formula Used for Date =TEXT(C2+1,"DD.MM.YYYY"),=TEXT(C2+2,"DD.MM.YYYY") & =TEXT(C2+3,"DD.MM.YYYY") to make constent.

Would be a great if you could come up with some logic or function (VBA Codes) which helps me to overcome this issues.

Requirements :
Source data’s are coming from two different applications namely ABC Aopplication & XYZ Application for which we need to remove the duplicated data in XYZ application (we are
not removing from ABC application)

Date range in the sheet ABC application may vary maximum by 2 days.

FYI... 10th dated (ABC application) amount may come in XYZ application
on 10th ,11th or on 12th)

Please note: same day we will not get two or more same amounts

Thanks & Regards,
Chaluvarayaswamy

47. Hui... says:

@Chaluva
To concatenate a date and a \$ amount try something like
=Concatenate("Some words ", text(C2,"DD.MM.YYYY"), " Some more text ", C3,"\$")
where
C2 is a Date
C3 is an amount

Your seperation delimieter may be different depending on your language version of Excel.

48. krishnasamy mohan says:

Hello Chandoo....
You may be interested to find MrExcel's challenge of this months posted at http://www.mrexcel.com/Challenge2011/challenge_42011.html. Also, to demonstrate the excel's simples capability in predicting the lotto numbers, I've submitted my entry under the link <iframe src="http://r.office.microsoft.com/r/rlidExcelEmbed?su=5455323543535607345&Fi=SD4BB533DBFB43DE31!107&AllowInteractivity=True&quot; &AllowTyping=True&activecell=smartpick!a35"width="402" height="450" frameborder="0" scrolling="no".
Mohan:)

49. Chaluva says:

To make you better understanding, I have received an amount of 100.00 on 11/04/2011 (orginal amount) in a spread sheet and the same amount of 100.00 may received in another spread sheet on 11/04/2011 or 12/04/2011 or on 13/04/2011 (duplicate entry 100.00 in any of these three days)
Requirements:
-> Need to remove the second entry (entire row) which is 100.00 coming on 11/04/2011 or 12/04/2011 or 13/04/2011.
Please note: in first spread sheet we will not receive the same amount of 100.00 in any of these dates again.(11/04/2011 or 12/04/2011).
The same amount of 100.00 may come on 13/04/2011 which is orginal amount in first spread sheet. This 100.00 need to be removed in sacond spread sheet which may come on 13/04/2011,14/04/2011 and 15/04/2011.
I would expecting a Excel Formula or VBA codes to overcome the above situation.
Early response would be appriciated.
Thanks & Regards,
Chaluvarayaswamy

50. Peter says:

=IF(CODE(B3)=CODE(D3),1,0)=0

convert each letter to a number, compare and format based on weather they are the same.

51. aa says:

=MOD(SUM(1/COUNTIF(\$B\$3:B3,\$B\$3:B3)),2)=1

• DianaC says:

I like this solution because it doesn't require the helper column.  I don't know how to explain to colleagues why/how this formula works.  Can you or someone else explain?

Thanks!

52. Murali6688 says:

Use the formula in conditional formatting based on formula

=if(a3<>a2,true,false)

and in the format if true option select the desired border

Regards,
Murali M

53. Murali6688 says:

Oops the not equals symbol is not showing

if(a3<>a2,true,false)

Regards,
Murali M

ps: with edits by Hui

54. rakesh kumar says:

I learnt everything on xl reading your site only.Thanks for another great post.

55. joann says:

i dont git it.
why do dis stuff have to be so different?! 🙁

56. Hui... says:

@Joann

What stuff is so different and to what is it different?

57. vietkuong says:

=IF(MOD(CODE(B3),2),TRUE,FALSE)

This will get ASCII code of the cell value and mod by 2. B3 is 'A' --> ASCII code = 65 --> TRUE --> highlight

58. sam says:

59. Hui... says:

@Sam
Why is this question here when your post is over at http://chandoo.org/wp/2008/06/09/what-the-if-learn-6-cool-things-you-can-do-with-excel-if-functions/ ?

60. Raj says:

Simplest conditional formating formula is =isodd(code(\$B\$3:\$B\$15) )

61. Rana says:

Guys need serious help.
Cant get a macro to work. Want to force employees to fill specific infor in form and if they have completed the form attached form to an email.

Got the following code but just not able to crack it.

Code ----
Sub Mail_workbook_Outlook_2()

If Sheets("Sheet1").Range("D7").Value = "" Then
MsgBox ("Pls enter value in D7")
Cancel = True 'cancels the save event
End If
Exit Sub

'Working in 2000-2010
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object......then the code to attach file to email which works perfect with out the checking code

end with
end sub

• aa aa says:

=MOD(IF(MATCH(A3;\$A\$2:\$A\$22;0)>MATCH(A2;\$A\$2:\$A\$22;0);MATCH(A2;\$A\$2:\$A\$22;0)+1;MATCH(A2;\$A\$2:\$A\$22;0));2)=0

Go to the second value in the list and Cond. Formatting > Use a formula > copy this formula there, then perform. Then go pick that value and click on format painter and paint the whole list.

Note that the remainer of the mod is 0, if it was 1 then it would format the second and below cells of the first group but the first one. Since we cant compare the first cell with another, it has to be manual, or the best leave it unformatted and make the remainer of the =MOD formula 0...

62. Ana-Maria says:

Hi,

I have 3 columns, A, B, C with manual entries values. A+B always should be equal with C, but the C value is a manual entry of the amount and not a sum of A and B. I want to use the Conditional formatting for the entire C column to highlight the C cells in case A+B not equal C . Please advise. Thanks!

• Hui... says:

@Ana-Maria
I have assumed your data starts at C2 and goes down
Select your Data from C2 to Cx
Goto Conditional Formatting
New Rule
Use a Formula
=A2+B2<>C2
Select a Format
Apply

• Ana-Maria says:

Thanks! It works!!! 🙂

63. MCH says:

Zebra:
=MOD(SUM(1-(A\$2:A2=A\$1:A1)),2)
or
=-1^SUM(1-(A\$2:A2=A\$1:A1))<0

64. Amritansh says:

Hi

I used a helper column for this.

=IF(C2=1,IF(B3=B2,1,0),IF(B3=B2,0,1))

After that based on col C did conditional formatting where C3=1.

Amritansh

65. 2Iron says:

=OR(B3="A",B3="C",B3="E") works and can be copy and pasted down the column... works for me.....

66. Iwik says:

Helping column F3 with =IF(D3=D2;F2;NOT(F2)*1) , fill down to F15 and conditionFormats on selected D3 to D15 with only the formula =F3
Choose color.

Great solutions ;O)

67. Chad says:

the provided solution (on the hidden sheet) works well for me. HOWEVER, it is not useful because each time i analyze the values, i will be using the autofilter. i have a report with 250 rows amongst 10 users and i want to evaluate each user individually. when i use the autofilter on a single persons name, the colors do not re-sort themselves based on the rows shown. this leaves different values that are next to each other with the same highlight/non highlight. is there a way to manipulate this formula so it will be accurate when i use my autofilter and am not looking at the entire data set?

68. Emily says:

Is there a way to apply the conditional format to the row rather just in the first cell of the row as shown in the example?

69. Prag says:

Any count of data:
Step 1: go to data>short> a2z
Step 2: apply to adjacent row " =IF(A3=A2,B2,NOT(B2))"
Step 3: again Goto Home> conditional formatting>highlight text rule>Text that contains> (put True)
Step 4: apply the filter
Step 5: Select True and mark color gray to original line

thats all .. Arranged and zebra lining

in addition to it count also done (i needed in my project)

70. Prasanna says:

We can try below formula in the conditional formatting.

data is present in Column A.

=IF(MOD(SUM(IF(FREQUENCY(MATCH(A\$1:A1,A\$1:A1,0),MATCH(A\$1:A1,A\$1:A1,0))>0,1)),2)>0,"TRUE","FALSE")

By this method u don;t need a helper column.

71. Prasanna says:

But for the above method to work, need to have the data sorted in a particular order.

72. Florian says:

Hey all,

sorry I am a bit late and I guess nobody will read this post anymore but I just stumbled on it tonight and was excited trying to solve the problem. I am using Excel 2013 so maybe there is a difference in handling some formulas than in earlier versions. In my opinion, this homework can easily be solved when you think about the letters as numbers. In Chandoos example, all the cells containing a letter that can be translated into an odd number (A=1, C=3 and so on) where coloured grey. Therefore we just have to translate the letters into numbers and colour the cells accordingly.
My approach was therefore, without using a helper column:

Mark (b3:b15) --> conditional formatting --> new rule --> use formula...

My Formula is: "=isodd(code(b3))"

If the formula is true, the colour of the cell should be grey, otherwise nothing happens. The formula worked well without any problems, and when I change the letter the colour of the cell adapts automatically (in case it is a letter that is translated into a different kind of number than it was before).
Just wanted to post my solution, would be happy to receive any kind of feedback. 🙂
Have a great week!

• Hui... says:

@Florian

I read every new comment twice a day so don't fear, it has been noticed.

In regards to this my only concern will be if the data has for example

Dogs
Donuts
Dumbo
Frogs
Felines
Fences

Because there is no E's and D and F are both even, then they will be shaded the same

where as
Dogs
Donuts
Dumbo
Elephants
Frogs
Felines
Fences

would be ok

• Florian says:

Hey Hui,

thanks for replying fastly! I see the problem with my approach. Thanks a lot again, great to receive feedback!
Have a nice week.

Florian

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.