How to Shade Every Other Row in Excel


Learn how to shade every other row in Excel with a unique function and a custom conditional formatting rule.

How to Shade Every Other Row in Excel

Excel is one of the leading spreadsheet applications for organizing data. Not only can you manipulate data in thousands of different ways, but you can also customize how it’s displayed. Sometimes, you may have to shade cells in different colors. Doing this can make tables and data sets easier to read. This guide will show you how to shade every other row in Excel.

Before we dive into the tutorial, you’ll want some data to work with so that you can follow along. Start by opening up a document containing the table or data you want to use as an example. Once you finish the steps below, you’ll know how to shade every other row in an Excel document.

How to Shade Every Other Row in Excel

To shade every other row in Excel:

  1. Highlight all of the data containing the rows you want to shade.
How to Shade Every Other Row in Excel - Highlight Data
  1. Navigate to the Home tab in Excel.
Excel - Home Tab
  1. Click Conditional Formatting under the Styles section.
Excel - Conditional Formatting
  1. Choose the New Rule option.
Excel - Conditional Formatting - New Rule
  1. Go to “Use a formula to determine which cells to format.”
Excel - New Formatting Rule
  1. In the blank field, type in =isodd(row()) to highlight every odd row.
  1. Tap on the Format button.
Excel - New Formatting Rule
  1. In the Fill tab, select a color for the Background Color.
Excel - Format Cells - Fill Background
  1. Select OK twice to apply the Background Color to every odd row.
Excel - Format Cells - OK Buttons

This tutorial uses a unique function to select every odd row from a pre-selected array of data. The =isodd function selects only odd numbers from a data set, and in this case, we’re telling it to choose every odd row.

We then use Conditional Formatting to select a Background Color to fill the rows. You can choose any color here or any style, font, or text size, for that matter. For simplicity, we decided to go with a simple gray color.

How to Shade Every Other Row in Excel

Highlighting every other row is common in finance when dealing with financial statements and documents. Now you know how to shade alternating rows in Excel!

To learn more about this powerful application, browse our Excel guides. You may also find some useful tips and tricks in our tech guides section!