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.

1. Select a range.

Shade Alternate Rows Example

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.

Click Conditional Formatting

3. Click New Rule.

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.

New Formatting Rule


Shade Alternate Rows in Excel

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.

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

7. Select any cell within a range.

Select Any Cell

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

Click Format as Table

9. Choose a table style with alternate row shading.

Choose Table Style With Alternate Row Shading

10. Click OK.

Create Table


Color Alternate Rows

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

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

Check Banded Columns


Banded Columns

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.

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

Convert to Range


Highlight Every Other Row in Excel

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