How to Shade every Other or Alternate rows in Excel?

1,986 Views Updated: 06 Sep 2017
Follow Post
How to Shade every Other or Alternate rows in Excel?

If you have worked on an excel sheet, then one thing you would agree with is that the application is vast. Entering values, making tables, doing calculations using formulas and filtering data may seem like a lot, but believe us, it is merely the tip of the iceberg.

There is a wide variety of tricks that you can do on an Excel sheet and one of them is shading every other row. If you have worked with Excels, then you must have come across sheets with alternate values highlighted. This format can be quite impactful in increasing the readability of the data, especially in a loaded Excel sheet.

Do you know how to shade every other or alternate row in Excel? No? Don’t worry, in a couple of minutes you will. Let’s begin.

How To Share Every Other Row In Excel?

There are two methods you can use to color every alternate row in an Excel worksheet. These are:

#1. Using The ‘Format as Table’ Option

The following steps can be followed to shade alternate rows in Excel:

• The first step would be to open the Excel worksheet in which you wish to shade the alternate rows.

• The next thing you have to do is selecting the cell range which you need to shade alternative row wise. In case, you need to do this on the entire sheet; you can select the sheet by pressing Ctrl + A or clicking on the ‘Select All’ button present on the top left corner of every worksheet.

Image result for How To Share Every Other Row In Excel?

(Image Courtesy: YouTube)

• Once you have selected the rows you seek to shade alternatively, click on ‘Home.'

• In the tab, you will find the ‘Styles’ group featuring the ‘Format as Table’ option.

• Clicking on the choice, you will be presented an array of light, medium and dark shades, from which you are free to pick the one of your choosing.

• Thereafter, a dialog box with a short formula would appear, where you will have to click on ‘OK.' Doing this automatically create another row with filters in each column of the table, allowing you to present the table as you wish. If you selected the header of your table before formatting it as a table, then you can select the ‘My table has headers’ in the dialog box.

#2. Using the ‘Conditional Formatting’ Option

Another way for you to shade alternative rows in an Excel worksheet is using the ‘Conditional Formatting’ option. To use this option, trace the following steps:

• The first two steps would be the same as the other method as you will again have to open the worksheet and select the cells in which you wish to shade the alternate rows.

• After having selected the cells, open the ‘Home’ tab available below the Title Bar.

Image result for Using the ‘Conditional Formatting’ Option

(Image Courtesy: Addintools)

• In the tab, you will find a group named ‘Styles,' the same one we used in the above-mentioned method. However, instead of ‘Format as Table,' this time look for the ‘Conditional Formatting’ choice.

• Once the list of options has appeared before you following clicking on the option, you need to select the ‘New Rule’ choice.

• When the ‘New Formatting Rule’ dialog box has emerged on the screen, you will find six options in front of you, out of which, you have to select the ‘Use a formula to determine which cells to format.'

• After selecting the option, paste ‘=MOD(ROW(),2)=1’ in the formula bar and click on ‘Format….'

• In the box appearing on the screen, go to the ‘Fill’ option, select the shade you wish to use and click on ‘OK.'

• Now, click on ‘OK’ in the ‘New Formatting Rule’ dialog box.

• Then click on ‘Apply’ and then ‘OK’ on the ‘Conditional Formatting Rules Manager’ box.

After you have followed the steps, you will find that all alternate rows in the cells you selected have been shaded with the hue you chose.

Learn more tech hacks here.

We hope the article helped. Tell us through your comments below. We would love to hear from you.

(Featured Image Courtesy: Twitter)

Posted by: Gage Posts: (2) Opinions: (9) Points: 470 Rank: 343

Related polls