Applies ToExcel for Microsoft 365 Excel for the web Excel 2024 Excel 2021 Excel 2019 Excel 2016

This article shows you how to automatically apply shading to every other row or column in a worksheet.

There are two ways to apply shading to alternate rows or columns —you can apply the shading by using a simple conditional formatting formula, or you can apply a predefined Excel table style to your data.

One way to apply shading to alternate rows or columns in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered and then applies the shading accordingly. The formula is shown here:

=MOD(ROW(),2)=0

Note: If you want to apply shading to alternate columns instead of alternate rows, enter =MOD(COLUMN(),2)=0 instead.

  1. On the worksheet, do one of the following:

    • To apply the shading to a specific range of cells, select the cells you want to format.

    • To apply the shading to the entire worksheet, click the Select All button.

      Select All button

  2. On the Home tab, in the Styles group, select the arrow next to Conditional Formatting, and then select New Rule.

  3. In the New Formatting Rule dialog box, under Select a Rule Type, select Use a formula to determine which cells to format.

  4. In the Format values where this formula is true box, enter =MOD(ROW(),2)=0, as shown in the following illustration.

    Formula in New Formatting Rule dialog box

  5. Select Format.

  6. In the Format Cells dialog box, select the Fill tab.

  7. Select the background or pattern color that you want to use for the shaded rows, and then select OK.

    At this point, the color you just selected should appear in the Preview window in the New Formatting Rule dialog box.

  8. To apply the formatting to the cells on your worksheet, select OK

Note: To view or edit the conditional formatting rule, on the Home tab, in the Styles group, select the arrow next to Conditional Formatting, and then select Manage Rules.

Another way to quickly add shading or banding to alternate rows is by applying a predefined Excel table style. This is useful when you want to format a specific range of cells, and you want the additional benefits that you get with a table, such the ability to quickly display total rows or header rows in which filter drop-down lists automatically appear.

By default, banding is applied to the rows in a table to make the data easier to read. The automatic banding continues if you add or delete rows in the table.

Banded rows in an Excel table

If you find you want the table style without the table functionality, you can convert the table to a regular range of data. If you do this, however, you won't get the automatic banding as you add more data to your range.

  1. On the worksheet, select the range of cells that you want to format.

  2. On the Home tab, in the Styles group, select Format as Table.

  3. Under Light, Medium, or Dark, select the table style that you want to use.

    Tip: Custom table styles are available under Custom after you create one or more of them. For information about how to create a custom table style, see Format an Excel table.

  4. In the Format as Table dialog box, select OK.

    Notice that the Banded Rows check box is selected by default in the Table Style Options group.

    Outlook Ribbon Image

    If you want to apply shading to alternate columns instead of alternate rows, you can clear this check box and select Banded Columns instead.

  5. If you want to convert the Excel table back to a regular range of cells, select anywhere in the table to display the tools necessary for converting the table back to a range of data.

  6. On the Table Design tab, in the Tools group, select Convert to Range.

    Tip: You can also right-click the table, select Table, and then select Convert to Range.

Note: You cannot create custom conditional formatting rules to apply shading to alternate rows or columns in Excel for the web.

When you create a table in Excel for the web, by default, every other row in the table is shaded. The automatic banding continues if you add or delete rows in the table. However, you can apply shading to alternate columns. To do that:

  1. Select any cell in the table.

  2. Select the Table Design tab, and under Style Options, select the Banded Columns checkbox.

To remove shading from rows or columns, under Table Style Options, remove the checkbox next to Banded Rows or Banded Columns.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.