Mar
13

Alternate row color and column shading in Excel (banded rows and columns)

by | updated on

This tutorial explains how you can alternate row colors in Excel to automatically highlight every other row or column in your worksheets. You will also learn how to apply Excel banded rows and columns and find a few smart formulas to alternate row shading based on a value change.

It is a common practice to add shading to alternate rows in an Excel worksheet to make it easier to read. While it is a relatively easy job to highlight rows of data manually in a small table, it could be an arduous task in larger ones. A better way is to have row or column colors alternated automatically and this article is going to show you how you can quickly do this.

Alternating row color in Excel

When it comes to shading every other row in Excel, most gurus will immediately point you to conditional formatting, where you will have to invest some time in figuring out an ingenious mix of MOD and ROW functions.

If you'd rather not use a sledge-hammer to crack nuts, meaning you don't want to waste your time and creativity on such a trifle as zebra striping Excel tables, consider applying the built-in table styles as a quick alternative.

Highlight every other row using table styles (Excel banded rows)

The fastest and easiest way to apply row shading in Excel is by using predefined Excel table styles. Along with other benefits of tables such as automatic filtering, color banding is applied to rows by default. All you need to do is convert a range of data to table.

  1. Select the range of cells where you want to alternate color rows.
  2. Navigate to the Insert tab on the Excel ribbon and click Table, or press Ctrl+T.
    Navigate to the Insert tab on the Excel ribbon and click Table.
  3. Done! The odd and even rows in your table are shaded with different colors. The best thing is that automatic banding will continue as you sort, delete or add new rows to your table.
    Odd and even rows in a table are shaded with the default table colors.

    If you'd rather have alternate row shading only, without the table functionality, you can easily convert the table back to a usual range. To do this, select any cell within your table, right click and choose Convert to Range from the context menu.
    Converting the table back to a usual range

    Note: If you opt to convert a table to range, you won't get the automatic color banding when you add new rows to the range. Another disadvantage is that as you sort the data, i.e. move certain cells or entire rows within the range, your color bands will travel with the original rows and your nice zebra stripe pattern will get distorted.

As you can see, converting a range to table is a very easy and quick way of highlighting alternate rows in Excel. But what if you want a bit more?

How to choose your own colors of row stripes

If you are not happy with the default blue and white pattern of an Excel table, you have plenty more patterns and colors to choose from. Just select your table or any cell within the table, switch to the Design tab > Table Styles group and select the colors of your liking.

You can use the arrow buttons to scroll through the available table styles or click the More button More to view them all. When you hover the mouse cursor over any style, it is immediately reflected to your table and you can see how your banded rows would look like.
Excel Table Styles Gallery

How to highlight a different number of rows in each zebra line

In case you want to highlight a different number of rows in each stripe, e.g. shade 2 rows in one color and 3 in another, then you will need to create a custom table style. Assuming that you have already converted a range to table, perform the following steps:

  1. Navigate to the Design tab, right click on the table style you want to apply and choose Duplicate.
    Right click on the table style you want to apply and choose Duplicate.
  2. In the Name box, enter a name of your table style.
  3. Select "First Row Stripe" and set the Stripe Size to 2, or to some other number you want.
    Set the size of the first row stripe.
  4. Select "Second row stripe" and repeat the process.
  5. Click OK to save your custom style.
  6. Apply the newly created style to your table by selecting it from the Table Styles gallery. Your custom styles are always available at the top of the gallery under Custom.
    Your custom table styles are always available at the top of the gallery under Custom.

    Note: Custom table styles are stored only in the current workbook and therefore are not available in your other workbooks. To use your custom table style as the default table style in the current workbook, select the "Set as default table style for this document" check box when creating or modifying the style.
    Setting your custom table style as the default table style in the current workbook

If you are not happy with the style you created, you can easily modify it by right-clicking your custom style in the Styles Gallery and choosing Modify from the context menu. And here you have plenty of room for your creativity! You can set any Font, Border, and Fill styles on the corresponding tabs, even choose gradient stripe colors, as you see in the screenshot below : )
Modifying the custom table style

Delete alternate rows shading in Excel with a click

If you no longer want to have color banding in your Excel table, you can remove them literally in a single click. Select any cell in your table, go to the Design tab and uncheck the Banded rows option.

To delete alternate rows shading, go to the Design tab and uncheck the Banded rows option.

As you see, Excel's predefined table styles provide a wealth of features to alternate color rows in your worksheets and create custom banded rows styles. I believe they will suffice in many situations, though if you want something special, e.g. shading entire rows based on a change of value, then you will need to use conditional formatting.

Alternate row shading using Excel conditional formatting

It goes without saying that conditional formatting is a bit trickier that Excel table styles we have just discussed. But it has one undisputable benefit - it allows more room for your imagination and lets you zebra stripe your worksheet exactly as you want it is each particular case. Further on in this article, you will find a few examples of Excel formulas for alternating row colors:

Highlight every other row in Excel using conditional formatting

We are going to start with a very simple MOD formula that highlights every other row in Excel. In fact, you can achieve exactly the same result using Excel Table styles, but the main benefit of conditional formatting is that it works for ranges as well, meaning that your color banding will remain intact as you sort, insert or delete rows in a range of data to which your formula applies.

You create a conditional formatting rule in this way:

  1. Select the cells you want to shade. To apply the color banding to the entire worksheet, click the Select All button in the top left-hand corner of your spreadsheet.
    To apply the color banding to the entire worksheet, click the Select All button in the top left-hand corner of your spreadsheet.
  2. Switch to the Home tab > Styles group and click Conditional Formatting > New Rule...
  3. In the New Formatting Rule window, choose "Use formula to determine which cells to format" option and enter this formula: =MOD(ROW(),2)=0
    Creating a new conditional formatting rule of the
  4. Then click the Format button, switch to the Fill tab and select the background color that you want to use for the banded rows.

    At this point, the selected color will appear under Sample. If you are happy with the color, click OK.
    Select the fill color that you want to use for the banded rows.

  5. This will bring you back to the New Formatting Rule window, and you click OK one more time to apply to color to every other of the selected rows.

    And here's how the result looks like in my Excel 2013:
    Banded rows in Excel 2013

    If you'd rather have 2 different colors instead of white lines, then create a second rule using this formula: =MOD(ROW(),2)=1

    And now you have odd and even rows highlighted with different colours:
    Create 2 conditional formatting rules to shade odd and even rows with different colors.

That was pretty easy, wasn't it? And now I'd like to briefly explain the syntax of the MOD function because we are going to use it in other a bit more complex examples.

The MOD function returns the remainder rounded to the nearest integer after the number is divided by the divisor.

For example, =MOD(4,2) returns 0, because 4 is divided by 2 evenly (without remainder).

Now, let's see what exactly our MOD function, one that we've used in the above example, does. As you remember we used a combination of the MOD and ROW functions: =MOD(ROW(),2) The syntax is simple and straightforward: the ROW function returns the row number, then the MOD function divides it by 2 and returns the remainder rounded to the integer. When applied to our table, the formula returns the following results:

Row No. Formula Result
Row 2 =MOD(2,2) 0
Row 3 =MOD(3,2) 1
Row 4 =MOD(4,2) 0
Row 5 =MOD(5,2) 1

Do you see the pattern? It's always 0 for even rows and 1 for odd rows. And then we create the conditional formatting rules telling Excel to shade odd rows (where the MOD function returns 1) in one color and even rows (that have 0) in another color.

Now that you know the basics, let's look into more sophisticated examples.

How to alternate groups of rows with different colors

You can use the following formulas to shade a fixed number of rows, regardless of their content:

Odd row shading, i.e. highlight the 1st group and every other group:

=MOD(ROW()-RowNum,N*2)+1<=N

Even row shading, i.e. highlight the 2nd group and all even groups:

=MOD(ROW()-RowNum,N*2)>=N

Where RowNum is a reference to your first cell with data and N is the number of rows in each banded group.

Tip: If you want to highlight both even and odd groups, then simply create 2 conditional formatting rules with both of the above formulas.

You can find a few examples of formula usage and the resulting color banding in the following table.

To color every 2 rows, starting from the 1st groupThe data starts in row 2. =MOD(ROW()-2,4)+1<=2 Color every 2 rows, starting from the 1st group
To color every 2 rows, starting from the 2nd groupThe data starts in row 2. =MOD(ROW()-2,4)>=2 Color every 2 rows, starting from the 2st group
To color every 3 rows, starting from the 2nd groupThe data starts in row 3. =MOD(ROW()-3,6)>=3 Color every 3 rows, starting from the 2st group

 

How to shade rows with 3 different colors

If you think your data will look better with rows shaded in three different colors, then create 3 conditional formatting rules with these formulas:

To highlight 1st and every 3rd row =MOD(ROW($A2)+3-1,3)=1

To highlight 2nd, 6th, 9th etc. =MOD(ROW($A2)+3-1,3)=2

To highlight 3rd, 7th, 10th etc. =MOD(ROW($A2)+3-1,3)=0

Remember to replace A2 with a reference to your first cell with data.

The resulting table will look similar to this in your Excel:
Rows shaded in three different colors

How to alternate row colors based on a value change

This task is similar to the one we discussed a moment ago - shading groups of rows, with the difference that there may be a different number of rows in each group. I believe, this will be easier to understand from an example.

Suppose, you have a table containing data from different sources, e.g. regional sales reports. What you want is shade the first group of rows related to the first product in Color 1, the next group related to the second product in Color 2 and so on. Column A listing the product names may serve as the key column or unique identifier.
Alternating row colors based on a value in the key column

To alternate row shading based on change of value, you'd need a bit more complex formula and an additional column:

  1. Create an additional column over the right side of your worksheet, say column F. You will be able to hide this column later.
  2. Enter the following formula in cell F2 (assuming that row 2 is your first row with data) and then copy it across the entire column:
    =MOD(IF(ROW()=2,0,IF(A2=A1,F1, F1+1)), 2)

    The formula will fill down column F with blocks of 0 and 1, every new block staring with the Product name change.
    Create an additional column on which the conditional formatting rule will be based.

  3. And finally, create a conditional formatting rule using the formula =$F2=1. You can add a second rule =$F2=0 if you want a second color to alternate blocks of rows, as shown in the screenshot:
    Alternating blocks of rows with 2 different colors

Alternating column colors in Excel (banded columns)

In fact, shading columns in Excel is pretty much similar to alternating rows. If you have understood all of the above, this part is going to be a piece of pie for you : )

You can apply shading to columns in Excel by using either:

Alternate column colors in Excel with table styles

  1. You start with converting a range to a table (Ctrl+T).
  2. Then switch to the Design tab, remove a tick from Banded rows and select Banded columns instead.
    Uncheck 'Banded rows' and select 'Banded columns'.
  3. Voila! Your columns are shaded with the default table colors.
    The columns are shaded with the default table colors.

In case you'd like prettier colors, you are free to choose any other pattern from the Table Styles Gallery.

If you want to shade a different number of columns in each stripe, then create a duplicate of an existing table style of your choosing, exactly as described here. The only difference is that you choose "First Colum Stripe" and "Second Colum Stripe" instead of the corresponding row stripes.
Setting the size of the First Colum Stripe

And this is how your custom column bands may look like in Excel:
Custom column bands in Excel

Alternating column colors with conditional formatting

The formulas to apply color banding to alternate columns in Excel are very similar to the ones we've used for shading alternate rows. You just need to use the MOD function in conjunction with the COLUMN function rather than ROW. I will name but a few in the table below and I'm sure you will easily convert other "row formulas" to "column formulas" by analogy.

To color every other column =MOD(COLUMN(),2)=0
and/or
=MOD(COLUMN(),2)=1		 Color every other column
To color every 2 columns, starting from the 1st group =MOD(COLUMN()-1,4)+1<=2 Color every 2 columns, starting from the first group
To shade columns with 3 different colors =MOD(COLUMN()+3,3)=1
=MOD(COLUMN()+3,3)=2
=MOD(COLUMN()+3,3)=0		 Shade columns with 3 different colors

Hopefully, now you won't have any problems with applying color banding in Excel to make your worksheets handsome and more readable. If you want to alternate row or column colors in some other way, don't hesitate to leave me a comment and we will figure this out together. Thank you for reading!

You may also be interested in

65 comments to "Alternate row color and column shading in Excel (banded rows and columns)"

  2. Gee says:
    May 31, 2020 at 7:12 pm

    Hi,
    I need to give alternate merged rows different color along with its non merged adjacent columns. Example A1:A2 , A3:A4 , A5:A6 , A7:A8 are merged. But B1:B8 are not merged. I want all the columns of rows (1,2), (5,6) blue colour and the other sets white

    Reply
  3. Sonia says:
    May 8, 2020 at 11:28 am

    Hi,
    I have excel template and fill the data from database. There could any number of data in excel. Its first column merge on the basis of duplicate data. I want to do setting in the excel template for alternate merge rows coloring. Please help me to do this.
    Note :- I don't know the number of rows.

    Reply
  4. Gustavo says:
    April 19, 2020 at 3:25 am

    How can I shade alternate rows but only that display data, the reminder rows must stay without shade? Thanks

    Reply
  5. Jerry says:
    December 20, 2019 at 5:47 pm

    Hi,
    As beginner in excel functions, please I need your help:
    I have this question:
    How to use highlight Mod function for alternating shading in one chosen row easily?
    Is it possible to mix MOD function with other condition in one formula for this row.
    For example use function AND : example in the first column in this chosen row is inserted value (for example 1 function : = 1) , how to u use function MOD(column) in this row?
    Thank for youur help

    Regards

    Jerry

    Reply
  8. anonimos says:
    March 11, 2019 at 3:07 pm

    the easiest way I know is by intorducing the following formula as a new rule in conditional formating: = =indirect("A"&ROW())="Product1" and ofcourse choose a color for this selection. I hope it hels as the examples above are too complicated or not selective enough.

    Reply
  11. Detlius says:
    December 19, 2017 at 2:41 pm

    The results are not bad but,

    sorry, always to use them, my page brake for printing is one line higher as it shall be :-(

    How to install colored lines in changing with noncolored lines under the page brake rules?

    To correct all 2000 lines to move several page brakes is not fine.

    Reply
    • Gennady Terekhov (Ablebits Team) says:
      January 17, 2018 at 11:24 am

      Hello,

      Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry we can’t assist you better.

      Reply
  13. Rutchee says:
    August 23, 2017 at 5:28 am

    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 :)

    Reply
  14. Rafiques Saleah says:
    January 1, 2017 at 8:01 am

    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.

    Reply
  15. Hi Svetlana Cheusheva says:
    December 6, 2016 at 1:14 pm

    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.

    Reply
  17. Nirmal says:
    November 15, 2016 at 5:39 am

    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.

    Reply
  18. Ruth says:
    October 5, 2016 at 1:27 pm

    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.

    Reply
  20. John says:
    September 12, 2016 at 2:51 pm

    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

    Reply
  21. Mrjackg says:
    August 22, 2016 at 1:49 pm

    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?

    Reply
  22. Yon says:
    June 2, 2016 at 2:57 pm

    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.

    Reply
  23. Jon says:
    April 20, 2016 at 1:44 am

    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.

    Reply
    • George V says:
      January 1, 2017 at 9:25 pm

      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

      Reply
  24. Brad Wurtz says:
    March 7, 2016 at 5:26 pm

    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.

    Reply
  26. Gioscani says:
    August 12, 2015 at 1:12 pm

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

    Reply
  27. Chandran Palanisamy says:
    August 11, 2015 at 2:13 pm

    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 !

    Reply
  29. Pat says:
    May 19, 2015 at 8:26 pm

    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!

    Reply
  30. zengqian88 says:
    May 4, 2015 at 5:48 pm

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

    Reply
  32. George says:
    March 1, 2015 at 12:45 pm

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

    Reply
  33. George says:
    March 1, 2015 at 12:32 pm

    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.

    Reply
  34. relax inc says:
    February 21, 2015 at 8:28 am

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

    Reply
  35. Martijn De Schrijver says:
    January 16, 2015 at 4:51 pm

    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

    Reply
  36. Luis says:
    December 18, 2014 at 5:22 pm

    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?

    Reply
  37. nitin says:
    November 17, 2014 at 9:18 am

    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 .

    Reply
  38. debbie says:
    November 10, 2014 at 6:25 pm

    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.

    Reply
  39. Dio says:
    November 6, 2014 at 7:08 pm

    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.

    Reply
    • Svetlana Cheusheva says:
      November 7, 2014 at 7:50 am

      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.

      Reply
      • LT says:
        March 4, 2015 at 3:34 pm

        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?

        Reply
        • Svetlana Cheusheva says:
          March 5, 2015 at 10:27 am

          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/2014/08/07/relative-absolute-cell-references-excel-conditional-formatting/

          Reply
  40. Melissa Cunningham says:
    October 22, 2014 at 3:10 pm

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

    Reply
  41. Johann Erickson says:
    October 7, 2014 at 3:20 pm

    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.

    Reply
    • Svetlana Cheusheva says:
      October 8, 2014 at 4:33 pm

      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.

      Reply
  42. Arvind says:
    September 22, 2014 at 2:52 am

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

    Reply
  43. Jo says:
    August 14, 2014 at 7:16 pm

    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.

    Reply
  44. Anonymous says:
    August 8, 2014 at 1:33 pm

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

    Reply
    • Alexander says:
      August 11, 2014 at 2:29 pm

      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.

      Reply
  45. Philip Kelley says:
    June 4, 2014 at 7:55 pm

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

    Reply
  46. anonymous says:
    May 15, 2014 at 8:58 pm

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

    Reply
    • Svetlana Cheusheva says:
      May 16, 2014 at 5:47 pm

      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?

      Reply

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 :)