30 Advanced Excel Tips for 2026

Excel Champs / Advanced Excel / Highlight Alternate Rows in Excel with Color Shade
By Puneet Gogia (Microsoft MVP)

Highlight Alternate Rows in Excel with Color Shade

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.

highlight-alternate-rows

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.

  1. Select the data on which you want to apply it.
    select-data
  2. After that, go to the Insert Tab and click on the Table button. You can also use the keyboard shortcut Control + T.
    click-table-button
  3. Now, in the dialog box, make sure to tick “My table has headers”.
    check-mark-my-table-headers
  4. 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.

default-table-style

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.

table-design-tab

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.

choose-from-multiple-table-designs

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.

  1. First, select the data on which you want to apply the color shades.
    select-data-to-apply-color-shades
  2. After that, go to the Home Tab and click on the Conditional Formatting drop-down.
    click-on-conditional-formatting
  3. From there, click on New Rule.
    click-new-rule
  4. Next, click on “Use a formula to determine which cells to format”.
    click-on-use-a-formula-to-determine-which-cell-to-format
  5. At this point, you have an input bar to add a formula, just like I have below.
    formula-bar-to-add-a- formula

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())
enter-the-formula

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

click-on-format-button

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
apply-conditional-formatting

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.

vba-code-to-apply-color
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.