Highlight Every Other Row in Excel

In this tutorial, we’ll explain how to highlight every other row in Excel. We’ll show you how to improve the readability of your spreadsheet and shade alternate rows.

Working with large data tables and organizing ranges in Excel, sometimes not a too easy task.

In this Excel guide, I will cover the following topics:

As first, you’ll learn how to highlight every other row using conditional formatting. After that, we’ll introduce the excel-table based solution. Finally, we’ll write a short macro to get the work even faster for shading every other row.

How to highlight every other row in Excel using Conditional Formatting?

Frequently you are facing the problem: It would be great to highlight rows in Excel! Conditional Formatting is a versatile tool for working colors, so we’ll use it in this example.

Example 1: Shade alternate rows using ISODD function

Here is our raw data table as you can see in the image below:

initial dat aset for alternate rows

In this example, you are shading every other row in a range (for example D3: F17) in this data set.

You want quickly highlight every alternate row in Excel. To do this follow these steps:

1. Select the range which contains data.

select the data range

2. Save your time using keyboard shortcuts! Use the Alt + O + D shortcut to open the conditional formatting rules manager window.

If you want to use the usual steps, click on the Home tab, select Conditional Formatting, and choose New Rule.

add new rule step 3

3. Apply a custom formula! As first, select the ‘Use a Formula to determine which cells to format’ option in the ‘New Formatting Rule’ dialogue box.

4. Now apply the formula to a range of cells! Select the ‘Edit the Rule Description’ field, and type the formula:

=ISEVEN(ROW())
how to highlight every other row in Excel

5. Click on the Format button and choose the fill color as the format.

6. Click OK. That’s it! You have the alternate rows highlighted.

alternate rows highlighted

Example 2: Combine ISEVEN() and ISODD() functions to apply unique Zebra Stripes

Add a second rule to Conditional Formatting Rules Manager. In this case, you can create unique styles based on two colors.

Use the formula below:

=ISODD(ROW())
highlight every other row using ISODD furmula

Explanation

Both methods are simple. If you run a nested formula using ISEVEN and ROW, the expression will return TRUE if the number is even. The result will be FALSE is the row number is odd. This method highlights the 1st, 3rd, 5th, (and so on) row in a range.

Apply the ISODD and ROW combination. The result of the formula will TRUE if the row number is odd. Otherwise, you’ll get FALSE as a result of the expression.

Example 3: Highlight every other row using the MOD function

It’s worth knowing that MOD function can be useful if you are working with colors and rows.

You have to replace the formula in the Rules Manager.

Enter

=MOD(ROW(),2)=1

expression and click OK.

highlight every other row alternate method

Explanation of the MOD formula

Use the formula to get the remainder from the division. You can apply the combination of MOD and ROW functions to create a custom formula.

In this section, we’ll explain how the MOD(ROW(),2)=1 formula works. Check the first cell in a range. Because the ROW function returns 3, the

=MOD(ROW()),2)=1

will be compiled into

MOD(3,2) = 1

You are waiting for 1 to shading the row. The first row highlighted because the result meets our initial criteria.

Go to the next row. If you are using the formula for D4, the =MOD(4,2) expression will get 0. It does not meet our criteria, and the row remains unchanged.

Example 4: Shading every nth row or column

In some cases, you want to highlight, for example, every 5th row. If you change the parameters of the ROW formula, you can work without limits.

Examples to alternate every nth row:

To highlight every third row in Excel us the formula below:

=MOD(ROW)),3 = 0
How to highlight every third row in Excel

Tip: To shade every fourth row enter the =MOD(ROW(),4)=1 formula.

Formatting Excel Tables for highlighting rows

Let us see the lightspeed solution for shading every other row using Excel tables.

Example 1: Using built-in table styles

  1. Select the range which contains data.
  2. Choose the Home tab and click to the Format as Table
  3. Click on the predefined styles window and choose one of them.
built in table style formatting

Example 2: Create your custom style for alternate shading rows

The first two steps are the same as above.Click on the ‘New Table Style.’

1. Click on the ‘New Table Style.’

new table style

2. On the ‘New Table Style’ dialogue box, select ‘First Row Stripe.’

3. Click Format and select the color for shading. The preview window will show your chosen color scheme. If you want to apply this style frequently, you have to click to ‘Set as default table style for this document.

4. Click OK.

steps to create zebra stripes in excel

You can modify the stripe size using the drop-down menu:

modify stripe sizes

VBA macro to Highlight Every Other Row in Excel

You are on the right track. Now, you’ll learn the fastest way.

To insert a macro open Visual Basic Editor, on the Developer tab, and click Visual Basic. If you prefer keyboard shortcuts, press Alt + F11 to open the VBE window.

  1. Right-click on the workbook name.
  2. Select Insert -> Module from the context menu.
  3. Copy and paste the code to the right pane.
vba editor

To start the macro, do these steps:

  1. Go to Developer Tab (if it does not appear, enable it)
  2. Click Macros
  3. The Macro dialogue box appears. Select the macro and click Run.
how to run a simple macro

Don’t forget to check the source code too:

Sub ShadeEveryOtherRow()
    Dim Counter As Integer
   'For every row in the current selection...
    For Counter = 1 To Selection.Rows.Count
        'If the row is an odd number (within the selection)...
        If Counter Mod 2 = 1 Then
            'Set the cell color to grey
            Selection.Rows(Counter).Interior.Color = RGB(200, 200, 200)
        End If
    Next
End Sub

Download the sample Workbook!

You may also be interested in: