• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Keep zebra strips proper look when sorting the table


New Member
Hi Guys

Thanks to Chandoo's email, I use this formula to create a zebra strips =Mod(Row(),2)=0.

I'd like to know how to keep the zebra strips in the table when I'm sorting it based on a criteria? Right now when sorting the table, I might get 2 row or even more with the same color..




Excel Ninja
Staff member

Using Conditional Formatting is the solution

Select your data table area

Goto Conditional Formatting

clear all rules

Add a new Rule using =Mod(Row(),2)=0 and set your colors


The conditional formatting will apply regardless of the sort order


New Member
Hi Hui

Unfortunate, my tests shows me a different results.

(That is why I started this thread at the first place..)


Excel Ninja
Staff member
This technique has been working since Conditional Formatting was introduced

What version of Excel are you using?

Can you post your worksheet anywhere?


Just saying it doesn't work doesn't help much. You need to post a workbook, because you have clearly done something wrong but you are not able to express it in a way that we can resolve.


New Member
Cool guys. you right ;)

Here is a sample: http://amit-cohen.com/excel/randomdata.xlsx

I'm using Excel 2007.

Sorting the table by any criteria (1,2 or 3)

will show you the problem I'm trying to solve.



Excel Ninja
Staff member

You aren't sorting your data you are filtering it.

The Conditional Format is working exactly as it should be in highlighting odd and even rows differently, but you are filtering some of them out and so it appears as if it isn't working.


Remove all the Conditional Formats from your worksheet

Then use the Format as Table Icon on the Home Tab and select an appropriate format or design your own.



New Member
@Amit.. as Hui pointed, since you are filtering, the MOD() solution will not work.

But you can use SUBTOTAL() formula inside MOD() to retain zebra lines even when filtering.

Assuming your data is in cells B3:B22, select all of it and enter Conditional Formatting formula like this:


and set formatting.

This will preserver the zebra ordering even when filtering.

Read more about SUBTOTAL formula here: http://chandoo.org/wp/2010/02/09/subtotal-formula-excel/