Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 row, you cannot get it done easily and quickly. Nevertheless, with Alternate Row / Column Shading utility of Kutools for Excel, you can quickly:

Shade or color every other row

Shade or color every other column

Remove all conditional formatting shading and background color good idea3

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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

doc shade altenative rows 1


Shade or color every other row

1. Highlight 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, check Rows from the Apply shading to option. And specify other options. See screenshot:

A: 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.

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.

Remove existing alternate shading: this option removes all conditional formatting and background shading.

B: Click the drop down arrow to select the color that you like.

C : Specify the number of rows that you want to shade, such as every other row, every two rows, every three rows…

D : 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.

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

shot-shade-every-other-row4
-1
shot-shade-every-other-row5

Shade or color every other column

1. Select the range that you want to shade.

2. Go to the Alternate Row / Column Shading dialog box, check Columns from the Apply shading to option. And specify other options. See screenshot:

A: 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.

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.

Remove existing alternate shading: this option removes all conditional formatting and background shading.

B : Click the drop down arrow to select the color that you like.

C : Specify the number of rows that you want to shade, such as every other column, every two columns, every three columns…

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

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

shot-shade-every-other-row7
-1
shot-shade-every-other-row8

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:

shot-shade-every-other-row9 arrow-big shot-shade-every-other-row10

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.


Note:

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

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

Kutools for Excel: with more than 300 handy features, free to try with no limitation in 60 days. Download and free trial Now!

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.