• 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

    Hui...

  • 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

amitcohen

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..


Thanks,

Amit
 

Hui

Excel Ninja
Staff member
Amitcohen

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

Apply


The conditional formatting will apply regardless of the sort order
 

amitcohen

New Member
Hi Hui

Unfortunate, my tests shows me a different results.

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

Hui

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?
 

xld

Member
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.
 

amitcohen

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.


Thanks.
 

Hui

Excel Ninja
Staff member
Amitcohen

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.


Solution:


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.


Voila...
 

keymaster

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:


=MOD(SUBTOTAL(3,$B$3:B3),2)=1


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/
 
Top