Excel 2010’s Table feature provides many different Table Styles including options for banded rows and customization. What if you want to format your data with banded rows without having to convert it to a Table? You can do this with Conditional Formatting.

What’s a banded row? It’s when every other row has a different shaded background, like the example below:

### How to apply banded rows using Conditional Formatting:

- Select the range to be formatted.
- On the
**HOME**ribbon, select**Conditional Formatting**, and then click**New Rule**. - In the
**Select a Rule Type**box, select**Use a formula to determine which cells to format**. - In the
**Format values where this formula is true box,**type**=Mod(Row(),2)**

- Click the
**Format**button. - On the
**Fill**tab, select the color of your choice. You may also apply Patterns and Fill Effects. - Click
**OK**.

**Tip**: If you want to apply banded columns instead of banded rows, enter **=MOD(COLUMN(),2)=0** instead of the formula shown above in step 3.

**Note**: This will also work in Excel 2003 Conditional Formatting. Select **Formula is** in the first drop-down box in the Conditional Formatting dialog box, and then enter the same formula shown above in step 3.

### Curious as to how this works?

Banded rows formula explained:

In the syntax: **Mod(Row(),2)** , both the MOD and ROW functions are used.

- The
**MOD**function returns the remainder of a number after dividing it by a specified number, which is 2 in this example. - The
**ROW**function returns the row number of a reference.

The formula **=MOD(ROW(),2)** will always return zero or 1. Zero is equal to false, while any number greater than zero is equal to true. When we enter our formula in the **Formula is:** box of the Conditional Formatting dialog, we must use a formula that returns only **true** or **false**. When the result is **true**, the specified format is applied. When the result is **false**, the specified format is not applied. This formula always returns a result of true, which means that every second row will be formatted.

Happy formatting!

Thats helpful, thanks. Since im new to excel, i was wondering if you could help: At http://www.excel-aid.com/excel-cell-color-coloring-and-shading-cell-backgrounds.html i read about inserting rows or columns. If i already have those banded, will the newly added ones also have that formatting? I would think it does, but im not sure.

marot90: Yes, the banded rows will continue as long as the new rows are inserted within the range that you applied the conditional formatting to. Good luck!

This is terrific. Sometimes, though, I would like to highlight a certain group of cells. Is there a way to use an if statement that first looks to see if a highlight has been assigned, if not, band the line?

Yes, this can be done!

1. Highlight the range that you want to apply the conditional formatting to.

2. Select “Use a formula to determine which cells to format” and enter your formula using the following example (changing the cell reference to your own).

Example: =$B1=”approved” (column B contains the condition “approved” or “not approved”.)

Be sure to include the $ in front of the column letter so that the fill color will get copied across the applicable row.

In this example, Excel checks to see if the item is approved, if it is then the Conditional Format fills the row with a pink color (if image does not display, click link to view: http://www.m-g-consulting.com/images/condformat.jpg ).

Good luck!