5 Ways to Highlight Every Other Row in Microsoft Excel

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.

Select one row
Select one row

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.

Selected alternate row using Ctrl key
Selected alternate row using Ctrl key

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

Apply a cell fill color
Apply a cell fill color

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.

Format Painter
Format Painter

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.

Highlight the entire dataset
Highlight the entire dataset

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.

Table styles dialog
Table styles dialog

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

Create table
Create table

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.

Highlighting every other row in Excel using Table Styles
Highlighting every other row in Excel using Table Styles

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.

Example dataset
Example dataset

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

Enable Sort & Filter
Enable Sort & Filter

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

Apply a filter
Apply a filter

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

Apply color fill to filtered table
Apply color fill to filtered table

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

Unfilter table
Unfilter table

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

Highlighted every other row in Excel with Sort & Filter
Highlighted every other row in Excel with Sort & Filter

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.

Creating a Helper column
Creating a Helper column

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.

Using fill handle to calculate cells
Using a fill handle to calculate cells

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.

Conditional Formatting more rules
Conditional Formatting more rules

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.

New formatting rule
New formatting rule

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
Format cells dialog
Format cells dialog

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.

Preview of configured Conditional Formatting
Preview of configured Conditional Formatting

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.

Colored every other even rows
Colored every other even rows

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

Second formula to highlight odd-numbered row
The second formula to highlight odd-numbered row

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

=MOD(ROW(),2)=1
Colored every other odd rows
Colored every other odd row

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:

VBA script to highlight rows
VBA script to highlight rows
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

Macro dialog
Macro dialog

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.

Input box for object range
Input box for object range

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

Choose Odd or Even
Choose Odd or Even

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

Choose color code for highlighting
Choose a color code for highlighting

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

Highlighted every other row using VBA
Highlighted every other row using VBA

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃