How To Shade Every Other Line in Excel with Conditional Formatting



****Read the comments section for tweaks to the formula that are needed for different regions (and I posted a recent comment about Microsoft 365 (from a viewer in South Africa) down below in these video details.)****
Different versions of the program require different characters (commas vs. semi-colon etc.)
How to shade every other line on an Excel spreadsheet using conditional formatting.

First, select the area of the spreadsheet you wish to have alternating lines shaded.
Then From the Home tab –Conditional Formatting –New Rule –Use a formula to determine which cells to format –Format values where this formula is true:

=MOD(ROW(),2)=0

Select the Format button — select a color for your alternating line shade –OK –OK

Alternately you can use these formulas:

=ISEVEN(ROW())

Select the Format button — select a color for your alternating line shade –OK –OK (This will shade EVEN numbered rows)

=ISODD(ROW())

Select the Format button — select a color for your alternating line shade –OK –OK (This will shade ODD numbered rows)

(October 2018) comment from a viewer regarding Microsoft 365: “On my version of Excel (Microsoft 365) the formula uses a semicolon in place of the comma. This took me a while to figure out. If the formula doesn’t work for you try =MOD(ROW();2)=0. ”

Nguồn: https://goindocal.com/

Xem thêm bài viết: https://goindocal.com/category/cong-nghe

Comments(42)

  1. WheeliePete says:

    Alternately, you can use these formulas in conditional formatting if you specifically want to shade even numbered rows on color and odd numbered rows another color:
    =ISEVEN(ROW())
    Select the Format Button – select a color for your alternating line shade — OK — OK (This will shade EVEN numbered rows)
    =ISODD(ROW())
    Select the FORMAT button – select a color for your alternating line shade — OK –OK (This will shade ODD numbered rows)

  2. Bimbola Oyekanmi says:

    I have been looking everywhere for this. Thanks for posting!

  3. Juan M. Aguayo-Leal says:

    Excellent!!! Worked very well !!!!!

  4. mobayguy says:

    Excellent Video Pete! So simple but incredibly useful. You've just saved me a whole lot of time and effort. Thanks !

  5. Debbie Richey says:

    Why won't the gridlines print through the colored selection? What am I doing wrong?

  6. Security Consultant Marshall says:

    Thank you Sir, this was great.

  7. A Akın says:

    Hi, formule does not work. Take error.

  8. ralph von says:

    short video and very clear.

  9. MyOneBlack Friend says:

    Thank you, sir!!

  10. Lynn Lawrence says:

    This was awesome and easy to follow. Thank you so much.

  11. Maria Friend says:

    Thank you. You can also create a table out of the data and it will highlight every other row.. Thanks for sharing.

  12. Renegade21 says:

    In case it doesn't work for you, this is what I had to do, 

    Check your system's regional settings to see what your "List Separator" is set to:
    In Control Panel, search for Region then select Change date, time, or number formats, click the Additional settings… button, then look for List separator under the Numbers tab.  In my case, I had it set to a pipe '|' because I was messing with some script that would change an XLS to a CSV and needed to end up with a pipe-separated file instead of a comma-separated one.
    Either use that character to separate the values in your formulas or just change it back to comma and the formula finally worked for me

  13. Ivan Valencia says:

    I just don't understand after YEARS of improvement and technology Microsoft doesn't just include this in the GUI

  14. J Shea says:

    super helpful and easy to understand – thank you so much! A better, more versatile option than if I was in the "Format Table" mode.

  15. auPointX says:

    German:
    =ISTGERADE(ZEILE())
    =ISTUNGERADE(ZEILE())

  16. 1stp4ward says:

    That is awesome, thank you

  17. Eliana Seidl says:

    VERY CLEAR AND EASY. THANK YOU.

  18. Eva McGann says:

    Very helpful and easy to follow! thank you!

  19. My MIKI says:

    Thank you. Great video, excellent editing and to the point

  20. Ted Moy says:

    Best video. Clear and concise. Thank you!

  21. عمران فيصل says:

    Thanks

  22. lisaviolet video says:

    Thank you!

  23. JustHelping The Christmas tree collection says:

    Brilliant. Thank you.

  24. Carolyn Harper says:

    Thanks. I don't work in an office. If I did, I'd be on Youtube all day to figure out how to work in an office.

  25. MsCritiq says:

    I appreciate the simplicity of your explanation and practical application of the function

  26. Tom Mascinskas says:

    what if the cell already has a fill and i would prefer that the manual fill would stay rather than the conditional

  27. Pat Hughes says:

    How can you do this with some rows that have merged cells in the beginning of the row and at the end of the row? I have employees names and ID# in first 2 cells (which have 2 cells merged for each cell). Then the last cell in each row also has 2 cells merged together. The cells in-between those cells are not merged at all. The upper row in the non-merged cells is where I put dates in. The 2nd row in the non-merged rows is where I put the hours worked in. How can I have every other row (meaning 2 actual rows but the beginning and the end of the rows have 2 merged cells. I want the 2 single cell rows to be the same color as the beginning and end. So for every other person the whole role is shaded a color. I hope this makes sense. I would really love any help someone could give me. Thanks.

  28. Mizefi says:

    Thank you so much.

  29. Timmy McCann says:

    THANK YOU!!!

  30. amastershock says:

    Thank you so much

  31. FaeriesDance says:

    Thanks

  32. Annie2509 says:

    Thank you so much for this. On my version of Excel (Microsoft 365) the formula uses a semicolon in place of the comma. This took me a while to figure out. If the formula doesn't work for you try =MOD(ROW();2)=0.

  33. vivek pal says:

    More video on excel for using commercial data

  34. Lloyd Christmas says:

    Clean and easy. Thanks so much.

  35. Will Bollman says:

    That worked out perfect man, Thank You!!!!!!!!!!!!!!!!!!!!

  36. budrfly57 says:

    First I would like to say, THANK YOU for the awesome video!!! I was able to do 99% of what I wanted. Now I have a question. I would like to know if it is possible, which I'm sure it probably is, but I can't figure out the formula. I want to shade my ODD rows but I want it to START at row #3 and not #1. Can you tell me what the formula would be for that, PLEASE!!!! and Thank you in advance.

  37. Le Lounga says:

    Thanks you

  38. Vladimir Davydenko says:

    so how do i now color the whites in?

  39. Robert Kajita says:

    Awesome, thank you. This tutorial was precisely how I needed it presented; simple, easy to mirror/follow, and verbally clear.

  40. Osiris Infante says:

    Thanks bro, you saved my life.

  41. mark arney says:

    you are the man!

  42. Andrea Illés says:

    Thanks 🙂 The Spanish formula is: =RESIDUO(FILA();2)=0

Post a comment