Excel - 3 Methods to Shade Every Other Row
HTML-код
- Опубликовано: 5 авг 2024
- Highlighting or shading every other row in Excel is a great way to make your data easier to read. I'll show three methods for applying color to alternate rows. Method 1 is my least favorite, but I see users doing this a lot. Method 2 is one of my favorite features in Excel, but for the most control, use method 3. Method 3 is Conditional Formatting and I use the MOD Function and the ROW function. The MOD function returns the remainder of one number divided by another. The ROW function tells you what row you are in.
MOD Examples:
=MOD(7,2) will return 1. Seven divided by 2 is 3 with 1 remaining.
=MOD(7,3) will return 1. Seven divided by 3 is 2 with 1 remaining.
=MOD(7,4) will return 3. Seven divided by 4 is 1 with 3 remaining.
=MOD(4,2) will return 0. Four divided by 2 is 2 with 0 remaining.
ROW Examples:
=ROW(A7) will return 7
=ROW(D6) will return 6.
=ROW() will return 3, if you are in row 3 will return 3.
Chapters:
00:00 Introduction
00:22 Method 1
00:47 Method 2
01:27 Method 3
05:27 Closing
Blog post - chrismenardtraining.com/post/...
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ RUclips keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
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. :-)
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.
Thank you for this!
Best explanation I found, thank you!
You are welcome, Crystal.
FINALLY!!! I found this video it has the right formula!!!!
That was easy. Dope.
Thank you.
Method 2 for the win! Nice 🦢
Formula =mod(row(),2)=1. Awesome tip. Thank you!! 🙏
Very welcome!
It says the formula is incomplete for me
you are a BEAST. thank you sir.
This really helped!! Thank you
You're welcome!
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.
Thankyou So Much
Brilliant
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?
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
excellent
Thanks!
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
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.
What if you want conditional to look like the table method which gives an alternating dark color row, then a light color row?
Thanks
Welcome
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 of applying different colors to different rows or columns 😮
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.
Not homeboy rocking resident evil merch, noice
For some reason it doesn't work for me. nothing happens when I hit ok :(
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?
seem slike office 365 doesn't like this: "the value you entered isn't valid. Please try again using alternative values, punctuation or symbol"
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