Hi Chris, and thanks as always for the amazing content. Question: wouldn't you expect quite a large performance hit when using the conditional formatting solution for a very large data set? I would avoid this method in these cases, but for small ones it's great!
Quick search lead me here. Love the three options perspective as it helped me fully understand why the conditional formatting option has the most strengths
Thank you Chris. I look at 3 other videos before your and your was the most straight forward and informative. Thank you for adding the formula and rules explanation as well.
You missed the easiest way to do it: 1 chose the array (or whole sheet) 2 conditional formatting 3 use a formula to determine which cells to format 4 =isodd(row()) and chose a color. Also works for columns.
Great video, but not what was wanting. I a list of dates and want alternate colors. Like thee 1-1 dates being green, two 1-2 being red, and four 1-4 being green again (the date skip was intentional). I am having a tough time finding help on this.
Hey Chris! What about mixing alternating columns and rows with diferent shades of grey for the columns vs the rows using the Conditional Formatting method? And thank you for the great video! THANK YOU! PS: I think it's outrageos that, after four decades, this common task is so difficult, complicated, and unintuitive, that it takes a half hour of research to accomplish. Microsoft never really understood or prioritised asthetics and usability -- thay either don't care or they are inept.
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?
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
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?
Another useful formula besides Mod would be "=ISEVEN(ROW())" which returns True if the Row is Even to get the same result, if you want to shade every other row. But still want a way to reference a specific cell that varies by Row to apply that logic to instead of using Row() ?
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.?
I would use Conditional Formatting * duplicate values* . All the duplicates would be in one color. drive.google.com/file/d/1cvkGQeqYH5VE4lUWgHk3S7qo0WUY2WI-/view?usp=drivesdk
Blog post chrismenardtraining.com/post/excel-3-methods-to-shade-every-other-row
Hi Chris, and thanks as always for the amazing content. Question: wouldn't you expect quite a large performance hit when using the conditional formatting solution for a very large data set? I would avoid this method in these cases, but for small ones it's great!
Great tip. Thank you very much!
Quick search lead me here. Love the three options perspective as it helped me fully understand why the conditional formatting option has the most strengths
I'm glad the search picked up my video. Thanks for letting me know.
Thank you Chris. I look at 3 other videos before your and your was the most straight forward and informative. Thank you for adding the formula and rules explanation as well.
Glad to help. I wish you would have found my video first. :-)
Brilliant. Thank you!
FINALLY!!! I found this video it has the right formula!!!!
Formula =mod(row(),2)=1. Awesome tip. Thank you!! 🙏
Very welcome!
It says the formula is incomplete for me
You are a great teacher. you explained much clearer than other site. Thank you!
You missed the easiest way to do it:
1 chose the array (or whole sheet)
2 conditional formatting
3 use a formula to determine which cells to format
4 =isodd(row()) and chose a color.
Also works for columns.
Thanks, it works
That was easy. Dope.
Thank you.
Method 2 for the win! Nice 🦢
Good explanation, thenks you. I get trought this subject without much problems.
Best explanation I found, thank you!
You are welcome, Crystal.
Great video, but not what was wanting. I a list of dates and want alternate colors. Like thee 1-1 dates being green, two 1-2 being red, and four 1-4 being green again (the date skip was intentional). I am having a tough time finding help on this.
you are a BEAST. thank you sir.
This really helped!! Thank you
You're welcome!
excellent
Thanks!
Thank you for this!
Hey Chris! What about mixing alternating columns and rows with diferent shades of grey for the columns vs the rows using the Conditional Formatting method? And thank you for the great video! THANK YOU!
PS: I think it's outrageos that, after four decades, this common task is so difficult, complicated, and unintuitive, that it takes a half hour of research to accomplish. Microsoft never really understood or prioritised asthetics and usability -- thay either don't care or they are inept.
Brilliant
Thankyou So Much
What if you want conditional to look like the table method which gives an alternating dark color row, then a light color row?
What of applying different colors to different rows or columns 😮
Thanks
Welcome
seem slike office 365 doesn't like this: "the value you entered isn't valid. Please try again using alternative values, punctuation or symbol"
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?
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
Not homeboy rocking resident evil merch, noice
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?
Another useful formula besides Mod would be "=ISEVEN(ROW())" which returns True if the Row is Even to get the same result, if you want to shade every other row. But still want a way to reference a specific cell that varies by Row to apply that logic to instead of using Row() ?
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?
For some reason it doesn't work for me. nothing happens when I hit ok :(
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.?
I would use Conditional Formatting * duplicate values* . All the duplicates would be in one color. drive.google.com/file/d/1cvkGQeqYH5VE4lUWgHk3S7qo0WUY2WI-/view?usp=drivesdk
@@ChrisMenardTraining Thank you for your immediate response.
Its from the bottle you found a few manholes back
I loved tables in Excel, but that changed when I started using VBA more...now I'll never use an Excel table again
ROMANS