How to Display Data With Banded Rows in Excel & Google Sheets

This tutorial demonstrates how to display data with banded rows in Excel and Google Sheets.

 

 

Display Banded Rows

Table Formatting

In Excel, you can format your data in banded rows (coloring rows in two alternating colors). One way to do this is by formatting your data as a table. Say you have the following data set in the range B1:E8.

 

 

To display these rows as banded, follow these steps:

1. First, format your data as a table. Select the data range (here, B1:E8), and in the Ribbon, go to Insert > Table (or use the keyboard shortcut CTRL + T).

 

 

As a result, your data range is converted to the table, and by default, rows are banded in two colors.

 

 

2. If you click on the table, the Table Design tab will appear, and when you click on it, you can choose between different table styles with banded rows.

 

 

If you want to keep banded rows and convert the table back to the range, you can use the Convert to Range option.

Conditional Formatting

Another way to display banded rows is to use conditional formatting.

1. Select the data range (B1:E8), and in the Ribbon, go to Home > Conditional Formatting > New Rule…

 

 

2. In the Rule window, (1) select Use a formula to determine which cells to format, and (2) in the formula box, type the formula:

=MOD(ROW(),2)=0

Then, (3) click Format.

 

 

In this formula, the ROW Function returns the number of a row, while the MOD Function returns the remainder of division by two. This way, every even number is formatted according to the conditional formatting rule.

3. In the Format Cells window, go to the Fill tab, choose a color (for example, light blue), and click OK.

 

 

4. Back in the Rule window, you can see the formatting preview. Click OK to finish.

 

 

Finally, every second row is now colored in light blue color, as a result of the conditional formatting rule.

 

 

Display Banded Rows in Google Sheets

Alternating Colors

In Google Sheets, you can’t insert a table, but you format cells with alternating colors.

1. Select the data range where you want to display banded rows (B1:E8), and in the Menu, go to Format > Alternating colors.

 

 

2. In the window on the right side, choose a format style (e.g., blue), and click Done. Here, you can also change colors for header and items and make your own custom style.

 

 

As a result, all rows in the range are now banded.

 

 

Conditional Formatting

Another way to display banded rows is to create a conditional formatting rule, similar to the rule set in Excel above.

1. Select the data range where you want to display banded rows (B1:E8), and in the Menu, go to Format > Conditional formatting.

 

 

2. In the rule window, (1) choose Custom formula is, and (2) enter the formula:

=MOD(ROW(),2)=0

Then, (3) click on the Fill color icon to choose a color, and (4) click Done.

 

 

The final output is the same as in Excel, every other row is colored in blue.