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 data with banded rows initial data

 

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.

 

display data with banded rows initial data

 

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).

 

display data with banded rows table

 

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

 

display data with banded rows final data

 

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

 

display data with banded rows table styles

 

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…

 

display data with banded rows conditional formatting

 

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

 

display data with banded rows conditional formatting 2

 

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.

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

 

display data with banded rows conditional formatting 3

 

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

 

display data with banded rows conditional formatting 4

 

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

 

display data with banded rows conditional formatting final data

 

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.

 

google sheets alterrnating colors 1

 

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

 

google sheets alterrnating colors 2

 

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

 

google sheets alterrnating colors 3

 

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.

 

google sheets display data with banded rows

 

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

 

google sheets display data with banded rows 2

 

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

 

google sheets display data with banded rows final