Shade Alternate Rows in Excel

To shade alternate rows in Excel, use conditional formatting or apply a table style. Coloring every other row makes it easier to read your data.

Use Conditional Formatting

First, let's use conditional formatting to shade/highlight every other row in Excel.

1. For example, select the range A1:D15 below.

Note: to apply the shading to the entire worksheet, select all cells on a worksheet by clicking the Select All button (see orange arrow).

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click New Rule.

4. Select 'Use a formula to determine which cells to format'.

5. Enter the formula =MOD(ROW(),2)=0

6. Select a formatting style and click OK.

Result:

Explanation: the MOD function gives the remainder of a division. The ROW function returns the row number of a cell. For example, for the seventh row, MOD(7,2) = 1 because 7 divided by 2 equals 3 with a remainder of 1. For the eight row, MOD(8,2) = 0 because 8 divided by 2 equals 4 with a remainder of 0. As a result, all cells in even rows return 0 and will be shaded.

Apply a Table Style

If you're not a formula hero, apply a table style to quickly color alternate rows in Excel.

1. Select any cell within a range.

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

3. Choose a table style with alternate row shading.

4. Click OK.

Result:

To change the color shading from rows to columns, execute the following steps.

5. First, select a cell inside the table. Next, on the Table Design tab, in the Table Styles Options group, uncheck Banded Rows and check Banded Columns.

Result:

Note: use the filter arrows to sort and filter this table. Visit our chapter about tables to learn more about this topic.

To convert this table into a normal range of cells (with banded rows or banded columns), execute the following steps.

6. First, select a cell inside the table. Next, on the Table Design tab, in the Tools group, click Convert to Range.

Result:

Tip: use Excel VBA to highlight the row and column of the Active Cell (selected cell). This program will amaze and impress your boss.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.