Do you want to learn how to highlight every other row in Excel? Join me in this article as I show you the tried and tested methods with real-world datasets.

If your Microsoft Excel worksheet contains data spread in many rows without any whitespace in between rows, it’s tough to follow the data. Here, you can shade every other row in Excel to make your database highly readable, enhance focus, and facilitate data comparison for different rows.

However, there’s no dedicated Excel ribbon menu to create such data visualizations. There are some features buried deep into multiple context menus and not visible to everyone.

In this article, I shall show you those features as well as secret techniques to make every other row shaded in Excel. Let’s get started then!

Highlight Every Other Row Manually

If your dataset is as simple as the one shown in this tutorial, you should probably go with this method.

Simply select the first row of the dataset that you want to highlight. It could be an odd (the first row) or even (the second row) of the target dataset.

To quickly select a row from start to end, click on the first cell, like A2, press the Shift key, and click on the last cell. Doing so shall instantly select the entire row minus the empty cells after the last cell containing a value.

Now, press the Ctrl key. Skip the immediate row below the selected row. Now, repeat the above steps to select the alternate row.

Don’t forget to apply a cell fill color for the selected alternate rows from the Home > Fill Color drop-down menu.

This method is beneficial over the Table Styles method mentioned below as it doesn’t create a table. Often, many tables in a worksheet can slow down the entire workbook. So, if you don’t want to convert your dataset to a table yet want to color every other row in Excel, this is the best way to go.

Suppose, you’ve got similar datasets in all other worksheets of your Excel workbook. For these small datasets in different sheets, you want to highlight every other row the same way you did above.

In this scenario, you don’t need to manually perform the same steps again for all those databases and worksheets. Instead, you can use the Format Painter button.

Highlight the entire formatted database and click the Format Painter button in the Clipboard block of the Home tab.

Navigate to the target dataset and click on the first cell. For example, if your database is in the A1:E11 range, click on A1. Excel shall instantly reformat the entire database according to the source.

If you wish to apply the same formatting in multiple databases select the source dataset and double-click the Format Painter button.

When using Format Painter, ensure that the database is of the same size. For example, if the source dataset is 10 rows by 5 columns, the target cell range should also be of the same size to achieve accurate results.

Highlight Every Other Row Using Table Styles

If you’re okay with converting your dataset to a table, this is the best method to make every other row shaded in Excel.

Go to the target dataset and click on the first cell. Now, press the Shift key and click the last cell. For example, for a dataset in A1:E11, the first cell is A1 and the last cell is E11.

Now, press the Alt + H + T keys to expand the Format as Table drop-down context menu.

This is the Table Style dialog. Here, click on any of the table formatting styles with contrasting cell fill colors in alternate rows to highlight alternate rows in Excel. Don’t forget to click OK on the Create Table dialog to apply the changes you’ve made so far.

The above is the new formatting of the dataset after using the Table Style element Blue, Table Style Medium 6.

Highlight Every Other Row Using Sort & Filter

The Sort & Filter tool of Excel enables you to filter rows based on a value in the selected cell of the whole dataset.

So, if you could locate an alternative data pattern in your database that repeats after every other row, you can easily use the Filter feature to exclude certain rows.

Then, highlight the filtered data with a cell fill color. Now, release the Filter function to make every other row shaded in Excel.

For example, in the above example dataset, the values in the cells of the Standard column repeats after every other row.

Simply click on the column header of such a dataset and press Ctrl + Shift + L to activate the Sort & Filter tool.

Click on the Filter button of the target column and uncheck all the values except for the one you need to highlight.

Highlight all the rows after filtering the dataset and apply a cell fill color from the Home > Fill Color drop-down menu.

Now, click the Filter button again and click the Select All option to show the whole dataset.

You should see that you’ve colored every other row in Excel in a flash.

Your datasets don’t need to always come with such a column that allows you to use Sort & Filter to highlight every other row.

In that situation, you need to create a new column on the right side of the existing dataset. Name the new column Helper and enter the following formula into the first cell just below the column header cell:

=MOD(ROW(),2)

Hit Enter to calculate the cell. You shall see 0 if you’ve applied the formula in an even row of the dataset, like C2 in the current tutorial.

Select the formula cell and drag the fill handle down the column to calculate the rest of the cells in the Helper column by applying the same formula. Drag down until the cell where relevant data exists in the corresponding cell to the left of the Helper column.

You shall now see alternating 0s (in even rows) and 1s (in odd rows) in the Helper column. Now, follow the steps mentioned earlier for the Sort & Filter tool to filter out odd or even rows.

Then, you need to apply a cell fill color by selecting the filtered table to color every other row in Excel.

Highlight Every Other Row Using Conditional Formatting

If you want to use a formula to highlight every other row you can use Conditional Formatting in Excel.

First, go to the target worksheet and highlight the whole dataset where you’d like to color every other row.

Navigate to the Home tab and click on the Conditional Formatting button in the ribbon menu.

Hover the mouse cursor on the Highlight Cells Rules and click on the More Rules option in the overflow context menu.

You shall now see the New Formatting Rule dialog box. There, select the Use a formula to determine which cells to format option.

Enter the following formula into the Format values where this formula is true field:

=MOD(ROW(),2)=0

Click on the Format button in the same dialog to open the Format Cells dialog. Here, you can add various cell formatting styles from tabs like Number, Font, Border, and Fill tabs. Click OK on Format Cells to save the changes.

You shall see the newly created formatting style in the Preview field of the New Formatting Rule dialog. Click OK to apply the newly set up Conditional Formatting rule.

Excel shall shade every other even-numbered row in your dataset according to the Format you’ve created in the New Formatting Rule dialog.

If you wish to highlight every other odd-numbered row, you can use the following formula in Conditional Formatting:

=MOD(ROW(),2)=1

Find above what happens when I use the odd-numbered row highlighting formula in Conditional Formatting.

Highlight Every Other Row Using Excel VBA

If you frequently need to color every other row in your Excel workbooks, you can use Excel VBA to automate and standardize the project. In this method, you can programmatically instruct Excel to shade every other row by collecting very little information from your end. You also need to make only two to three clicks to get the job done in the shortest possible time.

To use this technique, you need to create a VBA macro by using the following script:

Sub HighlightRows()
    ' Prompt user to select the target dataset
    Set Rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    
    ' Prompt user to choose whether to highlight odd or even rows
    Dim OddOrEven As String
    OddOrEven = InputBox("Enter 'Odd' to highlight odd rows or 'Even' to highlight even rows")
    
    ' Prompt user to choose a color
    Dim ColorChoice As String
    ColorChoice = InputBox("Enter a color (Red, Green, Blue, Yellow, Pink)")
    
    ' Determine the RGB values based on the color choice
    Dim Color As Long
    Select Case ColorChoice
        Case "Red"
            Color = RGB(255, 0, 0)
        Case "Green"
            Color = RGB(0, 255, 0)
        Case "Blue"
            Color = RGB(0, 0, 255)
        Case "Yellow"
            Color = RGB(255, 255, 0)
        Case "Pink"
            Color = RGB(255, 105, 180)
        Case Else
            MsgBox "Invalid color choice. Please run the macro again and enter a valid color."
            Exit Sub
    End Select
    
    ' Apply the chosen format to every other row according to the supplied data
    For Each Row In Rng.Rows
        If OddOrEven = "Odd" And Row.Row Mod 2 = 1 Then
            Row.Interior.Color = Color
        ElseIf OddOrEven = "Even" And Row.Row Mod 2 = 0 Then
            Row.Interior.Color = Color
        End If
    Next Row
End Sub

To find out how to use the script to create a macro, go through this resource:

📒 Read More: How To Use The VBA Code You Find Online

After creating the VBA macro, go to the target worksheet and press Alt + F8 to bring up the Macro dialog.

There, click on the HighlightRows macro and hit the Run button.

The VBA script shall ask you to choose a cell range as the input dataset in a prompt box.

Then, you shall see another input box to choose between the Even and Odd rows highlighting.

Finally, an Excel prompt shall show you to choose a color code from the displayed option.

As soon as you supply these inputs, Excel VBA shall highlight every other odd or even row in Excel.

Conclusions

So these are the best methods to highlight every other row in Excel.

You can use the manual method involving excessive mouse clicks and keyboard key presses for a very short database occasionally.

If the database is medium to large, you can use semi-automatic methods like Table Styles, Sort & Filter, and Conditional Formatting.

finally, if you often need to shade every other row in Excel completely automatically, you can use the Excel VBA-based method.

Which method did you like the most? Do you know a better trick to highlight every other row that I might have missed in this article? Comment below to share your suggestions.