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!
I appreciate that your presentation of the subject matter is crisp, clear, and understandable. Very glad that I happened to find your channel. Great job!
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.
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
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?
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!
I appreciate that your presentation of the subject matter is crisp, clear, and understandable. Very glad that I happened to find your channel. Great job!
Great to hear!
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.
Just what I needed. I knew the procedure once upon a time.. Great review for my Grey Matter (:
Thanks for watching!
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. :-)
You are a great teacher. you explained much clearer than other site. Thank you!
Good explanation, thenks you. I get trought this subject without much problems.
Formula =mod(row(),2)=1. Awesome tip. Thank you!! 🙏
Very welcome!
It says the formula is incomplete for me
Best explanation I found, thank you!
You are welcome, Crystal.
No 3 takes one into the woods for sure
Brilliant. Thank you!
Glad to help
FINALLY!!! I found this video it has the right formula!!!!
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
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.
This really helped!! Thank you
You're welcome!
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!
you are a BEAST. thank you sir.
That was easy. Dope.
Thank you.
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
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?
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() ?
What if you want conditional to look like the table method which gives an alternating dark color row, then a light color row?
Thankyou So Much
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.
Brilliant
What of applying different colors to different rows or columns 😮
excellent
Thanks!
Thanks
Welcome
Not homeboy rocking resident evil merch, noice
seem slike office 365 doesn't like this: "the value you entered isn't valid. Please try again using alternative values, punctuation or symbol"
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 :(
I loved tables in Excel, but that changed when I started using VBA more...now I'll never use an Excel table again
Its from the bottle you found a few manholes back
ROMANS
Method 2 for the win! Nice 🦢