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

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.

• Alternate row colors in Excel
  • Shade every other row using table styles (Excel banded rows)
  • Alternate row shading with conditional formatting
• Alternate column shading in Excel
  • Shade every other column using table styles (banded columns)
  • Highlight alternate columns with conditional formatting

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

   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.
   

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

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

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

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.

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:

• Shade every other row
• Shade groups of rows with different colors
• Highlight rows using 3 colors
• Alternate rows based on value change

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

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:
   

   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:
   

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:

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

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
To color every 2 rows, starting from the 2nd groupThe data starts in row 2.	=MOD(ROW()-2,4)>=2
To color every 3 rows, starting from the 2nd groupThe data starts in row 3.	=MOD(ROW()-3,6)>=3

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 1^st and every 3^rd row =MOD(ROW($A2)+3-1,3)=1

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

To highlight 3^rd, 7^th, 10^th 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:

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.

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.
   

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

• Excel table styles
• Conditional formatting rules

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.
   
3. Voila! Your 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.

And this is how your custom column bands may look like 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
To color every 2 columns, starting from the 1st group	=MOD(COLUMN()-1,4)+1<=2
To shade columns with 3 different colors	=MOD(COLUMN()+3,3)=1 =MOD(COLUMN()+3,3)=2 =MOD(COLUMN()+3,3)=0

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

• Excel conditional formatting formulas
• Excel conditional formatting Icon Sets, Data Bars and Color Scales
• How to change the row color based on a cell's value
• Two ways to change background color based on cell value
• How to delete every other row or every Nth row in Excel
• How to remove empty rows in Excel