How to apply banded rows in Excel without converting to a table

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:

banded2

How to apply banded rows using Conditional Formatting:

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

banded rows

  1.  Click the Format button.
  2. On the Fill tab, select the color of your choice.  You may also apply Patterns and Fill Effects.
  3. 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.

  1. The MOD function returns the remainder of a number after dividing it by a specified number, which is 2 in this example.
  2. 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!

Advertisements

4 thoughts on “How to apply banded rows in Excel without converting to a table

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s