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.