The quickest way is to turn the data into a table: click inside it, press Ctrl + T and click OK. Tables shade alternate rows automatically. Turn the shading on or off with Table Design → Banded Rows and pick colours from Table Styles.
Without a table, select the data, go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format, enter =MOD(ROW(),2)=0 and choose a fill. Use =MOD(ROW(),2)=1 for odd rows or =MOD(ROW(),3)=0 for every third row.
For alternate columns, use =MOD(COLUMN(),2)=0. For bands of two rows at a time, starting in row 2, use =MOD(INT((ROW()-2)/2),2)=0.
Row-based rules don’t adjust when you filter: hidden rows still count, so the visible rows lose their pattern. To keep the banding on filtered data, use =MOD(SUBTOTAL(103,$A$2:$A2),2)=0, where column A has a value in every row.
Don’t shade rows by hand or with a macro. Fixed fill colours move with their rows when you sort, and the pattern breaks.
In Excel, when you are working with a large set of data, you can highlight alternate rows with a color shade to give it a better look and increase readability. It makes each row of the data distinct and helps you to read it.

In this tutorial, we are going to discuss three simple ways that you can use for this.
Use an Excel Table to Highlight Alternate Rows in Excel
Using an Excel table is the best as well as the easiest way to apply a shade of color to highlight alternate rows in Excel.
- Select the data on which you want to apply it.

- After that, go to the Insert Tab and click on the Table button. You can also use the keyboard shortcut Control + T.

- Now, in the dialog box, make sure to tick “My table has headers”.

- In the end, click OK to apply the table.
Once you click OK, you will get a default table style on your data, just like the following.

But you can change the style if you don’t want to go with the default one. Go to the Table Design Tab and then open the Table Styles.

From the styles, you can choose one, or you can create a style for yourself from scratch.
The alternate shading comes from the Banded Rows option in the Table Style Options group of the Table Design Tab. Uncheck it to remove the shading, or check Banded Columns to shade alternate columns instead. And if you don’t want to keep the data as a table, go to Table Design ➜ Convert to Range: the colors stay, and the data becomes a normal range again.
One more thing: a fill color that you have applied to the cells yourself sits on top of the table style. So, if the banding doesn’t show, select the data and set the fill to No Fill first.

Using Conditional Formatting to Apply Color Shades on Alternate Rows
You can also use a formula in conditional formatting to apply a shade of color to each alternate row in the data. This method gives you better control and a more customized look.
- First, select the data on which you want to apply the color shades.

- After that, go to the Home Tab and click on the Conditional Formatting drop-down.

- From there, click on New Rule.

- Next, click on “Use a formula to determine which cells to format”.

- At this point, you have an input bar to add a formula, just like I have below.

Now, you need to enter one of the below formulas:
- =ISEVEN(ROW()) – If you want to shade the even rows.
- =ISODD(ROW()) – If you want to shade the odd rows.
You can also use MOD, which works the same way. The following formula shades the even rows (use =1 instead of =0 for the odd rows):
=MOD(ROW(),2)=0
And if you want to shade alternate columns instead of rows, use COLUMN in place of ROW:
=ISEVEN(COLUMN())

After that, click on the format button to decide which color shade you want to give to each alternate row to highlight it.

In the end, click OK to apply the conditional formatting.
Shade a Group of Rows
To shade rows in groups (like three rows shaded, three rows not), use the following formula when your data starts in row 2. Change the 3 to the number of rows you want in each group:
=MOD(INT((ROW()-2)/3),2)=0
Keep the Shading After Filtering
The ROW-based formulas look at the row number, so after you filter the data, the visible rows don’t alternate anymore. SUBTOTAL with 3 counts only the visible cells, so the following formula keeps the shading alternate even on filtered data (data in A2:A100, select A2 down when you create the rule):
=MOD(SUBTOTAL(3,$A$2:$A2),2)=0

Use a VBA code to Apply a Color to Alternate Rows
If you know how to use VBA in Excel, you can use a macro to apply a color shade to each alternate row. Just select the range and run the following code.

Sub apply_shades_to_alternate_rows() Dim myRange As Range Dim i As Long Dim C1 As Long Dim C2 As Long 'define colors C1 = RGB(238, 238, 238) C2 = RGB(145, 196, 131) 'checks if a range is selected or not If TypeName(Selection) <> "Range" Then Exit Sub 'specify the range to the variable Set myRange = Selection 'end the code if only one row is selected If myRange.Rows.Count = 1 Then Exit Sub 'loop through each row one by one and color alternate rows with different color shade For i = 1 To myRange.Rows.Count If i Mod 2 = 0 Then myRange.Rows(i).Interior.Color = C2 'Even Row Shade Else myRange.Rows(i).Interior.Color = C1 'Odd Row Shade End If Next i End Sub
Quick Links: Visual Basic Editor – Run a VBA Code
Unlike the table and conditional formatting methods, the macro applies a fixed fill color. So, if you add, delete or sort rows later, the colors don’t adjust, and you need to run the code again.
Which Method to Use
- Excel table: the quickest way, and the shading adjusts when you add, delete, sort or filter rows.
- Conditional formatting: best when you can’t use a table, or want groups of rows or your own rules.
- VBA: a fixed fill color for a one-time job, like a report you send out.
Frequently Asked Questions
How do I remove the alternate row colors?
For a table, uncheck Banded Rows on the Table Design Tab. For conditional formatting, go to Home ➜ Conditional Formatting ➜ Clear Rules ➜ Clear Rules from Selected Cells. For colors applied with VBA, set the fill to No Fill.
Why does the shading break when I filter the data?
Because ISEVEN(ROW()) uses the row number, which doesn’t change when rows are hidden. Use an Excel table or the SUBTOTAL formula shown above instead.
Can I highlight every third row?
Yes. Use =MOD(ROW(),3)=0 as the conditional formatting formula. Change the 3 to shade every fourth, fifth or nth row.