Md. Araf Bin Jayed
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.
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.
Step 2:
- Secondly, go to the Home tab of the ribbon.
- Then, from the Styles group, select the Format as Table command.
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.
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.
Step 5:
- Finally, you will be able to shade every other row after following those steps.
Read More: How to Alternate Row Colors in Excel Without Table (5 Methods)
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.
Step 2:
- Secondly, go to the Styles group from the Home tab of the ribbon.
- Then select the Conditional Formatting command from the group.
Step 3:
- Thirdly, after choosing the command you will see the some more options.
- Then, from there select the New Rule… command.
Step 4:
- Fourthly, under the Select a Rule Type heading, choose Use a Formula to determine which cells to format.
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.
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.
Step 7:
- In this step, you will see the dialogue box from Step 4 again.
- Press OK from the dialogue box.
Step 8:
- Finally, your data table will look like the following picture after completing all the above steps.
Read More: Excel Alternating Row Color with Conditional Formatting [Video]
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.
Step 2:
- Secondly, go to the Developer tab of the ribbon.
- Then, from there choose the Visual Basic command under the Code group.
Step 3:
- Thirdly, you will see the VBA window after selecting the previous command.
- Then, from the Insert tab choose Module.
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
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.
Step 6:
- Finally, every other row from your data set will be shaded after running the code.
Read More: How to Color Alternate Rows in Excel (8 Ways)
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.
