Skip to content

Microsoft Excel Alternate Background Row or Column Color In A Worksheet

These instructions apply to the following Microsoft software versions.

  • Excel for Microsoft 365
  • Excel for the Web
  • Excel 2019
  • Excel 2016
  • Excel 2013
  • Excel 2010
  • Excel 2007

Windows

Choose one of the following methods.

Conditional Formatting

You can apply an alternate background color by creating a conditioanl formatting rule.

Row Formula Example

=MOD(ROW(),2)=0

Column Formula Example

=MOD(COLUMN(),2)=0
  1. Within a Excel worksheet choose one of the following actions.
    • Apply by selecting the desired range of cells.
      1. Left mouse click on the first desired cell in the range.
      2. While holding down the shift key then left mouse click on the last desired cell in the range.
    • Apply to an entire worksheet by left mouse clicking on the "Select All" button located at the top left of the spreadsheet.
  2. On the "Home" tab within the "Styles" group left mouse click the arrow next to "Conditional Formatting" and then click "New Rule".
  3. In the "New Formatting Rule" dialog box under "Select a Rule Type" left mouse click on "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.
  5. Left mouse click on the "Format..." button.
  6. In the "Format Cells" dialog box left mouse click on the "Fill" tab.
  7. Select the desired background or pattern color.

  8. Left mouse click on the "OK" button.

    Info

    A preview of the selected background or pattern color will appear within the "New Formatting Rule" dialog box.

  9. Apply the changes by left mouse clicking on the "OK" button.

Tip

To view or edit the conditional formatting rule go to the "Home" tab > "Styles" group and left mouse click on the arrrow next to "Conditional Formatting" > left mouse click on "Manage rules".

Table Style

  1. Within a Excel worksheet select the desired range of cells.
    1. Left mouse click on the first desired cell in the range.
    2. While holding down the shift key then left mouse click on the last desired cell in the range.

  2. On the "Home" tab > "Styles" group left mouse click on "Format as Table".

    Tip

    Custom table styles are available under "Custom" after one or more have been created.

  3. Left mouse click on the desired style under "Light", "Medium" or "Dark".

  4. In the "Format as Table" dialog box left mouse click on "OK".

    Tip

    If alternate background color is desired for columns instead clear the check marks and left mouse click on "Banded Columns".

Tip

You can convert the Excel table back to regular range of cells as follows.

  1. Left mouse click on the table to deselect.
  2. On the "Design" tab > "Tools" group left mouse click on "Convert to Rage".

Web

By default when a table is created in Excel for the web every other row in the table has an alternate background color. The automatic banding will continue if a row is added or deleted within the table.

Note

Excel for the web does not allow for conditioanl formatting rules.

Alternate Column Background Color

  1. Select any cell within the table.
  2. Left mouse click on "Table Design" tab > "Style Options" > "Banded Columns" checkbox.

Tip

Remove the background color from rows or columns by left mouse clicking on "Table Design" > "Style Options" and removing each checkbox next to "Banded Rows" or "Banded Columns".