Excel - 3 Methods to Shade Every Other Row

Not homeboy rocking resident evil merch, noice

Using Method 2 completely destroys my entire spreadsheet. Every column and row increases in size by double. Any idea on how to not make that happen?

I have a large a mount of data in many rows with multiple columns (necessary to sort). I love method 2 because it maintains the shading format even when sorting AND filtering. However, method 3 would be ideal for my project because I'm able to shade the first 10 rows a different color from all rows afterward (row 11 on). I'm also using a 3rd shade on the bottom 10 rows. But when I filter out any data from the columns, the shading for every other row goes away. Is there a way to combine the functionality of methods 2 & 3 where I'm able to filter out data and not lose the format of every other row being shaded as well as keeping the first and last ten rows of the data shaded in different colors?

I'll keep poking around until I figure something out. In the meantime any help and/or ideas would be very much appreciated.

Thank You in advance
~Jerry

For some reason it doesn't work for me. nothing happens when I hit ok :(

This really helped!! Thank you

Thank you for this!

Best explanation I found, thank you!

You are a great teacher. you explained much clearer than other site. Thank you!

Thank you for this video. I just want to ask if there's a way to highlight the duplicates in alternative colors (just two colors) so that I won't do it manually after I use the countif formula?

Helpful video and explanation. Any idea if one can apply a similar process using conditional formatting that would ignore hidden rows when filtering data? So whether you have the entire dataset in view or you filter to a subset, the displayed values will be shaded every other row. I use the aggregate function [AGGREGATE(2,5,{Array}] to identify the actual row # in the subset when filtering data. But I don't think you can use a function in the Conditional Formatting formula that references an array since it wouldn't have the proper context. Any ideas?

I loved tables in Excel, but that changed when I started using VBA more...now I'll never use an Excel table again

Hello Sir, Thank you for sharing this. Also, I just wanna know how to apply single color for all similarities and different color for other all entries.?

Thanks