42 Comments



****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

Author

42 thoughts on “How To Shade Every Other Line in Excel with Conditional Formatting

  1. 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. 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

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

  4. 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.

  5. 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.

  6. 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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *