 Color Banding Rows In A Worksheet

This page describes how to create color banding of rows on a worksheet. Introduction

If you have a large number of rows in a worksheet, especially if those rows span many columns, you may find it useful to color alternate rows or alternate groups of rows with another color. This creates a visual style similar to an accounting ledger or good old-fashioned computer green-bar paper. Excel has built-in styles for this, and of course you can do it manually. However, if you sort the rows or you insert or delete rows, the color banding will get out of whack.

The solution to this problem is to use Conditional Formatting from the Format menu to apply a format to the cells. The formula used in the Conditional Formatting rule is based solely on the row number, so the formatting it applies will remain intact as you sort the rows or insert or delete rows.

The formatting techniques described here create color bands of a fixed number of rows, regardless of the content of the cells on worksheet. To create color banding based on the content of the cells, see the Content Banding With Conditional Formatting page. When banding is applied, the cells will have alternate bands of color, as shown below:  The image on the left shows even banding, where the even number of row groups are colored. The image on the right shows odd banding, where the odd numbers of row groups are colored.

First, select the rows that you want to format. Then, go to the Format menu and choose Conditional Formatting. In this dialog, change Cell Value Is to Formula Is. The formula to use depends on whether you want the color applied to the first and every other group of rows, or you want no color on the first group of row and color applied to the second and then every other group of rows. Each group of rows can contain 1 or more rows.

To apply the color on the first group and every other group, enter the following formula in the formula bar in the Conditional Formatting dialog.

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

where Rw is the row number of the first cell in the rows that are to be formatted, and N is the number of rows in each banded group. For example, if the first row is 12 and you want each band to contain 3 rows, you would use the formula:

=MOD(ROW()-12,3*2)+1<=3

Click the Format button and select the color or pattern that should be applied to the cells.

If you want the banding to appear only on rows that are not empty, you can use a formula like

=AND(MOD(ROW()-Rw,N*2)+1<=N,ROW(\$D5)<>"")

In this formula, change the reference to \$D5 to the cell reference that is to be tested for data. You should fix the column specifier with the \$ character, but do not fix the row number.

To apply the color starting in the second band of rows, use the following formula in the Conditional Formatting dialog:

=MOD(ROW()-Rw,N*2)+1>N

As before, Rw is the row number of the first row to be formatted and N is the number of rows in each band. For example, if the first row is 12 and the bands contain 3 rows, use the following formula:

=MOD(ROW()-12,3*2)+1>3

By using Conditional Formatting rather than directly styling a range, you can prevent the colors from getting out of order when you sort the range or insert or delete rows.

If you want the color banding to be based on the content of the cells (alternating between formatted and unformatted when content changes), see Content Based Banding  This page last updated: 28-February-2009.

-->