Quickly shade or color every other row / column in Excel

If a worksheet contains too many rows and columns, it will reduce the readability of the worksheet. To resolve this problem, you may apply coloring or shading to every other row or column by using a Conditional Formatting formula or a predefined Excel table style to your data in the worksheet. But no matter which way you choose to apply coloring or shading to alternate rows, you cannot get it done easily and quickly. Nevertheless, with Alternate Row / Column Shading utility of Kutools for Excel, you can quickly:

Shade or highlight every other or nth row

Shade or highlight every other or nth column

Remove all conditional formatting and background color

Click Kutools >> Format >> Alternate Row / Column Shading. See screenshot:

Shade or highlight every other or nth row

1. Select the range that you want to shade or color, and then apply this utility by clicking Kutools > Format > Alternate Row/Column Shading.

3. In the Alternate Row / Column Shading dialog box, specify the following operations:

• (1) Select Rows from the Apply shading to section;
• (2) Specify a shading formatting you need. You can select Conditional Formatting or Standard Formatting.
• (3) Click the Shade color drop down to select the color that you like.
• (4) Specify the number of rows that you want to shade, such as every other row, every two rows, every three rows…
• 

4. Then click OK. And the selected range has been shaded every other row. See screenshots:

Shade or highlight every other or nth column

1. Select the range that you want to shade.

2. Go to the Alternate Row / Column Shading dialog box,  specify the following operations: 

• (1) Select Columns from the Apply shading to section;
• (2) Specify a shading formatting you need. You can select Conditional Formatting or Standard Formatting.
• (3) Click the Shade color drop down to select the color that you like.
• (4) Specify the number of columns that you want to shade, such as every other column, every two columns, every three columns…
• 

3. Then click OK. The selected range has been shaded every other column. See screenshots:

Remove all conditional formatting shading and background color

With this utility you can also remove the conditional formatting shading and background color in Excel.

1. Select the range which contains conditional formatting or background color that you want to remove.

2. Go to the Alternate Row / Column Shading dialog box, select Remove existing alternate row shading option. Then click OK. See screenshots:

3. If you click Yes, the conditional formatting and background color will be deleted; and if you click No, only the background color will be deleted.

Tips: In the Alternate Row / Column Shading dialog box:

1. Conditional Formatting: This option uses Excel's Conditional Formatting to apply shading to alternate row. The advantage of conditional formatting is that if you insert new rows within the range, the alternate row shading will be adjusted automatically.

2. Standard Formatting: This option use standard formatting to apply shading to alternate row. If you insert new rows within the range, the alternate row shading will not be adjusted automatically, and you have to reapply the alternate row shading manually.

3. If your selected range is filled with background color, and you don’t want to remove the background color after you apply this operation, please checked Leave existing color for non-shaded rows

Notes:

1. You can press Undo (Ctrl+Z) immediately to restore it.

2. If there is filtered data, this feature will not work.

Demo: shade or color every other row / column in Excel