How to Shade Every Other Row in Excel (3 Ways)

Shading every other row in an Excel spreadsheet to facilitate understanding and enhance readability is just a routine procedure. This practice makes your data table easier to read and understand. But, shading every other row one by one in Excel is time-consuming. In this article, you will see how to shade every other row in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


3 Handy Ways to Shade Every Other Row in Excel

To solve the problem in our article, we will use the following data set. Here, we have some random information about some people. In this article, you will see three different ways to shade every other row in Excel by using this data set. In our first approach, we will format our data set as a table, then use conditional formatting in the second procedure, and lastly, we will apply VBA as our third method.

Handy Ways to Shade Every Other Row in Excel

1. Formatting as Table to Shade Every Other Row in Excel

You can shade every other row of your data set by formatting the data set as a table. See below for the detailed steps of this procedure.

Step 1:

  • First of all, select the range of data set in your worksheet.

Handy Ways to Shade Every Other Row in Excel

Step 2:

Handy Ways to Shade Every Other Row in Excel

Step 3:

  • Thirdly, after choosing the command, you will see many pre-existing table formats.
  • Then, from there select the format in which every other row is shaded.

Handy Ways to Shade Every Other Row in Excel

Step 4:

  • Fourthly, after selecting the format, a dialogue box naming Create Table will appear.
  • In there, you will see the data range is already present.
  • Then, press OK.

Handy Ways to Shade Every Other Row in Excel

Step 5:

  • Finally, you will be able to shade every other row after following those steps.

Handy Ways to Shade Every Other Row in Excel


2. Using Conditional Formatting to Shade Every Other Row

In our second procedure, we will use the Conditional Formatting feature of Excel to complete our task. For a better understanding, go through the following steps.

Step 1:

  • Firstly, select your data range excluding the table headers.

Handy Ways to Shade Every Other Row in Excel

Step 2:

  • Secondly, go to the Styles group from the Home tab of the ribbon.
  • Then select the Conditional Formatting command from the group.

Handy Ways to Shade Every Other Row in Excel

Step 3:

  • Thirdly, after choosing the command you will see the some more options.
  • Then, from there select the New Rule… command.

Handy Ways to Shade Every Other Row in Excel

Step 4:

  • Fourthly, under the Select a Rule Type heading, choose Use a Formula to determine which cells to format.

Handy Ways to Shade Every Other Row in Excel

Step 5:

  • Fifthly, type the following formula in the Format values where the formula is true type box.
=MOD(ROW(),2)=1
  • Then, press the Format command.

Handy Ways to Shade Every Other Row in Excel

Step 6:

  • Then, you will see the Format Cells dialogue box.
  • Here, from the Fill tab choose any color of your preference for the shading.
  • Lastly, press OK.

Handy Ways to Shade Every Other Row in Excel

Step 7:

  • In this step, you will see the dialogue box from Step 4 again.
  • Press OK from the dialogue box.

Handy Ways to Shade Every Other Row in Excel

Step 8:

  • Finally, your data table will look like the following picture after completing all the above steps.

Handy Ways to Shade Every Other Row in Excel


3. Applying VBA to Shade Every Other Row in Excel

We will apply a VBA code to shade every other row in Excel as our last procedure. To learn more about this, follow the following steps.

Step 1:

  • Firstly, select the data range from your worksheet which is B5:E10 in our example.

Sample Data Set

Step 2:

  • Secondly, go to the Developer tab of the ribbon.
  • Then, from there choose the Visual Basic command under the Code group.

Sample Data Set

Step 3:

  • Thirdly, you will see the VBA window after selecting the previous command.
  • Then, from the Insert tab choose Module.

Sample Data Set

Step 4:

  • Fourthly, copy the following VBA code into your Module.
'Set the function name
Sub Shade_Every_Other_Row()
'Declare Rng variable as type Range
Dim Rng As Range
'Selecting cell range from the data set
Set Rng = Selection
'Using For loop to go through every 2nd row
For i = Rng.Rows.Count To 1 Step -2
'Returns the number of the first row in the data range
Set myRow = Rng.Rows(i)
'Set shade color through RGB function to shade every other row
myRow.Interior.Color = RGB(135, 206, 250)
'Repeat the steps to complete until the last row appears
Next i
End Sub

Sample Data Set

VBA Breakdown

  • The function name is Shade_Every_Other_Row.
  • The variable name is Dim Rng and it is a range type variable.
  • Set Rng = Selection: Selecting the cell range manually where the shading will apply.
  • For i = Rng.Rows.Count To 1 Step -2: Using the for loop to go through every other row in the data set.
  • Set myRow = Rng.Rows(i): Returning the number of the first row in the data range Interior.
  • Color = RGB(135, 206, 250): Using the RGB function to shade the rows using the preferred colors.
  • Next i: Repeating the steps to complete the procedure until the last row appears.

Step 5:

  • Then, save the code and press the play button or F5 to run the code.

Sample Data Set

Step 6:

  • Finally, every other row from your data set will be shaded after running the code.

Sample Data Set


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to shade every other row in Excel. Please share any further queries or recommendations with us in the comments section below. The Exceldemy team is always concerned about your preferences.

Araf
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo