Comments on: How to color alternate rows in Excel: highlight every other row

See how you can quickly alternate row and colomns colors in your worksheets by using Excel Table Styles. You will also learn a few smart formulas to highlight banded rows and columns with 3 different colors and to shade groups of rows based on a value change. Continue reading →

Comments page 2. Total comments: 78

  1. Hi! Need your help please :)

    I badly want to make my excel sheet an organised one. In my excel I have column for document statuses. For which one at a time changes ofcourse depends on it's status.
    Is there any formula for which colours in a row with a specific status (word) can be changed?
    I have tried some formula but only one cell changes it's color.
    Please help :)

    1. Hi, Rutchee,

      you need to create conditional formatting rules with simple formulas. For example, you want to highlight the first row of you table (A2:H2, for example) with green if H2 contains "Yes". The formula for the formatting rule will be H1="Yes" and it should be applied to A2:H2.

      Please read this article to see how the conditional formatting rules work, are created and applied.
      Hope this helps!

  2. Hi Svetlana Cheusheva
    Happy New Year
    How can I make shade and color both columns and rows at a time.

    Can you please help me.

  3. Hi Svetlana Cheusheva;
    I'm trying to find out how to color code 1 cell with 2 or 3 different color.For example
    If i'm tracking my Coverage % and FAIL%.
    If my coverage is 50% and I'm seeing 15% FAIL how i can show a cell have 15% RED and 35% GREEN.

    Can we do this in a cell Please help me.

  4. OMG. Been searching for this (=MOD(ROW()-2,4)+1<=2) for an hour.
    Thank you!
    E

  5. hi to who it may concern,

    Thanks for supporting all who working in Excel and it really good for my professional

    i need a help regarding

    i have to highlight the cell that contains formulas in entire sheet or selected area.

  6. Is there a way to retain the 2 colour banding when copying or dragging data or formula down the sheet?

    When I do it, whatever colour the cell I am copying from is pasted onto the cells below.

  7. Can I apply this formula (MOD(IF(ROW()=2,0,IF(A2=A1,F1, F1+1)),2)) to a pivot table?
    Many thanks in advance!
    Mearah

  8. Hi.

    I have 2 columns of data to work with. The left column features the numbers 0,1, and 2. I have conditionally formatted this column to show 0 is red, 1 is yellow, and 2 is green.

    Further to this, the right hand column has the values "Not started", "Working", and "Complete". They are determined using the following nested IF function:

    "=IF(B4=2,"Complete", IF(B4=1,"Working",IF(B4=0,"Not Started")))"

    Is it possible to get the right hand column to show the same colour as the left? I.e. If B4 = 2, I want C4 to be green.

    What would you recommend?

    Kind regards,

    John

  9. I am trying to shade every other visible column in a rage using the Mod function with a conditional format. What is a good formula to use?

    1. Please check answer of Alexander in post #3 above ;-)

  10. On the "How to alternate row colors based on a value change":
    Is there a way to do it without adding new column ?
    I want to change between two colors based on date changing for example:
    15/6/2015 in color #1
    16/6/2015 in color #2
    20/6/2015 in color #1 (no dates between 16-20/6/2015).

    Thank you very much.

    1. YES! This is what I need too. Couldn't find a solution anywhere though :\

  11. How do you have a different shading for 4 different colors every 4th row?

    Your examples only cover up to 3 rows. I need 4 rows. But if you also have another formula for every X rows, that would be nice.

    Also, how do I make it so that I can start from a certain row and finish at a different row (not the whole spreadsheet). Currently, I just highlight the section I want to do and the highlights stay within the selected area at the time I applied the rule.

    1. Hi Jon,

      Here is the answer to your questions:
      1) Put in cell N1 your number X (=the row width of your band) (experiment later with values 1,2,3,4,5,6,7,8,9 and 10)
      2) Put in cell N2 1 or any (small number), which is the starting row of your band.
      3) Put in Cell A1: "=MOD(ROW()-$N$2,$N$1*2)+1<=$N$1" (without quotes)
      4) Copy down the formula from A1 (by dragging the little black cross at lower right corner of A1) from A2 to A20.
      5) Now select A1:C20 and conditionally format the area A1:C20 with the formula =MOD(ROW()-$N$2,$N$1*2)+1<=$N$1 (Put a yellow highlight)

      Because of 3) you will see TRUE or FALSE (the result of the formula) in A1
      Because of 4) you will see TRUE or FALSE (the result of the formula) in A2:A20
      Because of 5) you will see YELLOW highlighted bands in the area A1:C20 with a TRUE value in cell Ax(N1 bands wide, starting at row indicated by value in N2)

      Experiment by changing the values in N1 (=band width) and N2 (1st row to highlight) to see the differences.

      Again N1 is your X number (1...10 or greater)

      Kind regards,
      George

  12. Thanks for the article. I was able to apply your 'How to alternate row colors based on a value change' by using a 'key field' and when the key field value changed to then trigger the conditional formatting.
    - my key field is in column D
    - my first condition is in E15, and is set to zero
    - I created the condition below in E16 and copied it down
    =IF(E15=0,IF(D16=D15,E15,1),IF(D16=D15,E15,0))
    - so for the first group of rows that have the same key, they have condition = 0
    - when the second group of rows have the same key, they have condition = 1
    - when the third group of rows have the same key, they then have condition = 0, and we repeat the cycle on and on.

    Thanks for the great information to get me on the path.

  13. I want to see with colour when I selected one row or one column or one cell

    1. how should I do?

      1. Hello, Kaung,

        For us to be able to assist you better, please describe your task in more detail.

  14. hai Svetalana, i can not make three colours row like in this article. could u please fix my sheet , thank you very much.

  15. I wanted to use a banded style with a stripe size of 4 for row 'and' columns. Modify Table Style just doesn't work -either rows are banded or columns are banded -but not both.

    1. Home (menu)->Styles (group)->Format as Table (pull down)->New Table Style.
    2. Give a name. Select Row & Column stripe sizes to be 4...apply '4 different colors' (for 1st,2nd -row and column)
    3. Oops-only row 'or' column banded stripes -not both !

    1. Hello, Chandran,

      You can use Banded Rows and Banded Columns at the same time. However the colors will not mix in the overlapping ranges. By default the row color is regarded as the main one. For us to help you better, please send us a screenshot of the expected result to support@ablebits.com

  16. Thank you Alexander!

  17. Great tips, Svetlana! However, what if I want to use more than just 2 alternating colors? What if I want to use 3 colors? (For example: blue, red, and yellow rows.) I searched Google, but can't find anything on this. Thanks in advance for your help!

  18. Hi Svetlana, this is exactly what I've been looking for all day. Thanks so much for your help!

  19. Thanks Svetlana, just what I was looking. Excellent article!

  20. I found the problem. I recently installed non-English Excel and it doesn't accept english integers (IF, ROW etc.. )

  21. Hi, I can't get this to work - not even the most simple formula =MOD(ROW(),2)=0 doesn't do ANYTHING. I'm in europe so I replaced the comma with a semicolon: =MOD(ROW();)=0 Still nothing.

  22. maybe you can address the problem:
    How to keep the alternate color display
    when sorting a database with alternate fill colors?

  23. Just an FYI: IN EUROPEAN VERSIONS, IF IT KEEPS GIVING YOU A FORMATTING ERROR, ENTER ";" IN STEAD OF "," AS A SEPARATOR. FINALLY FOUND THE SOLUTION. THANK GOD
    =MOD(ROW();2)=0

    1. You are the man

  24. Is it possible to have the color function, and still be able to use Conditional Formatting? For example, I have a To-Do Excel Log, which I love having colors, one row one color and the next row a different color. BUT, I would to use Conditional Formatting to highlight the text of the items that are do TODAY or that are PAST DUE! Is that possible?

  25. I want to create hyperlink in first cell or every row in a sheet and cell value should be used as hyper link .We are using excel template and filling data to it using POI java .

  26. is there a way to keep a pop up box on the side or something that will be visible when opened/instantly that shows "our" meaning per color we've selected for each row?
    Our employees are not aware or will remember the colors and the meaning to the info they are viewing. Thanks.

  27. Hi Svetlana,
    I, too, have the issue where the formula calculated accordingly (Like Jo), but when applying the conditional formatting to shade/not shade when the value changes, the SHADING is off by one. For example, it is highlighting the last record from the previous set, which causes the entire shading pattern to be off by one. Any help you can provide would be greatly appreciated.

    1. Hi Dio,

      Please check whether your formula is written for the top left cell of the range to which the rule applies. For example, if you apply the rule to A2:F10, the formula shall be =$A2=1 or =$A2=0

      If this does not work, you can send me your worksheet and I will try to help.

      1. Please check whether your formula is written for the tip left cell of the range to which the rule applies. For example, if you apply the rule to A2:F10, the formula shall be =$A2=1 or =$A2=0

        I am not sure what you mean here. If doing a conditional rule, ALL I put in is =$A2=1
        How does that work for every row. Does it know to check the value as you go?

        1. Hi LT,

          First off, I apologize for my misprint, it should be "formula is written for the _top_ left cell of the range".

          I meant to say that in conditional formatting rules, a formula should always reference the left-most cell of the range to which the rule applies. For example, if you are creating a rule for cells A1 through A10, you write =$A1=1; if your range is A2:A10, then your formula is =$A2=1.

          If you are creating the rule for A2:A10, but your formula says =$A1=1, the shading will be off by one row, as reported by Dio.

          >Does it know to check the value as you go?

          Yes, Excel adjusts the formula for all other rows like when you drag the formula down a column, provided that you use correct cell references (absolute vs. relative) in your formula.

          If you want to find more about using proper cell references when creating conditional formatting rules, this article may be helpful:
          https://www.ablebits.com/office-addins-blog/relative-absolute-cell-references-excel-conditional-formatting/

  28. I just wanted to say THANK YOU, THANK YOU, THANK YOU!!!! I have been trying to figure out a way to do banded rows based on a value change FOREVER! You are amazing!!!! Thank you so much!!!!!!!!!!!

  29. If you use the conditional formatting option, you lose features in Excel:

    You can no longer use a color fill on the rows where the conditional formatting is true. If you filter, you can get rows with the fill adjacent to each other defeating the point of the banded rows.

    Use a table, when you can. Tables have lots of built in features that make them extremely powerful tools.

    1. Thank you for your comment, Johann.

      You are absolutely right, a table is a very powerful Excel tool. With many power users, pressing CTRL+T on new data is an instinct rather than a habit. But a lot of users, especially beginners, continue working with ranges, that's why in this article we've covered both ways.

  30. hi Svetlana Cheusheva,

    I am really thankful for this tip..=MOD(IF(ROW()=2,0,IF(A2=A1,F1, F1+1)), 2)!!

    It made my sheet become alive..!!!

  31. Hi,

    I came across a problem when I used the formula pertaining to "alternate row shading based on value" and appreciate if you could assist. The shading is off by one row and highlight the row before.

    1. Hi Jo,

      Most likely one extra row was somehow added to the applied range. If this is not the case, please send me your workbook at support@ablebits.com and I will try to help.

      1. When applying conditional formatting do not select header row otherwise it will be off by one row

  32. I want to shade every other visible row using the Mod function with a conditional format. What is a good formula to use?

    1. If you want to color only every other visible row, please use the following formula:
      =MOD(SUBTOTAL(102,$A$2:$A2),2)=0

      If you want to color every other row, including the hidden ones, use this one:
      =MOD(ROW(),2)=0

      Where A2 is the left-most cell of the range you want to shade.

  33. I'm sure I could have figured out the "Based on value change" version sooner or later. Maybe. Thanks for making it so simple!

  34. Now when you change the row order by using the dropdown widgets on a column, the alternate table colors are lost.

    1. Hi,

      This is very strange. The alternate colors should be preserved when you sort or filter table rows. Can you please specify what method you used to highlight rows and what is your Excel version?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)