How to Highlight Every Other Row in Excel

In this article, we will see how to highlight every other row in Excel. Some available techniques are applying conditional formatting, using different table style and applying Excel VBA code. The methods are discussed below.

It’s a good practice to highlight different rows in Excel for better readability. It is a quite easy task to highlight different rows manually in a small table. But when you have to deal with a large table in your worksheet you need to impose different approach.

Using Table Styles to Highlight Rows

To apply different row shading in Excel, you can use different table styles. This is the easiest and fastest approach of highlighting rows. The default automatic filtering and color banding makes it easy to highlight different rows in Excel. You need to select a data range and convert it to a table to perform the row highlighting.

How to Choose Different Colors of Row Stripes

There are so many numbers of color stripes available for row and columns highlighting in the format as table option. The row shading can be done following the below procedure for different color stripes.

  • 1st select the data range
  • Now go to the Insert tab and click on the Table

  • You will see a Create Table Box will come up. Press OK. Then you will find that a table has been created from which you can read your data properly

Excel generate blue and white pattern table by default. Now if you wish to create your own color patterns for the table you can also do that. For this, you need to format the table. To do that, just click on the Format as Table option on the Home tab. You will find more patterns and colors.

How to Choose Different Colors of Row Stripes

You can also use the Design option on top of your spreadsheet where you will find the color options with table style option.

Highlighting Rows by Modifying Table Style

If you need to highlight a number of rows by your choice with a different number of colors then you need to modify your table accordingly.

  • 1st format the range of cells in a table like we did before
  • After clicking on Design tab, choose the table that you want to format and right click to choose Duplicate

  • Enter any name in the Name option of the Modify Table Style box
  • After Selecting First Row Stripe, choose the Stripe Size that you want. I choose 2 for this.

  • Then select Second Row stripe and continue the process
  • Press OK to save it.
  • Now from the Table Styles gallery, search the modified table by name and choose it.

if this modification doesn’t give you the satisfaction you can again modify it from Styles Gallery and selecting Modify to the adjacent table. Here you can choose any Border, Font and Fill styles you want. The pictures below show us how the modification is done.

How to Highlight Different Number of Rows in Each Zebra Line

If you want your color bandings to be removed, choose any cell from your table and go to Design tab to uncheck the Banded rows.

How To Highlight Every Other Row in Excel Using Conditional Formatting

Conditional formatting is a good practice for highlighting or shading a specific row. By the help of conditional formatting, you can highlight different rows according to your choice. Here, we will see the use of two formulas in conditional formatting for highlighting rows.

Using the Formula ISEVEN

Using the formula ISEVEN in conditional formatting you can highlight the even rows in a specific range. For example, if you want to highlight the even rows from the range A1: D9, choose the entire range and select the New Rule under Conditional Formatting and use this formula =ISEVEN(ROW()). The procedure is stated below.

  • 1st select the entire range where you want to apply the conditional formatting
  • In the Conditional Formatting option, select the NEW Rule option

  • In  New Formatting Rule box do the following as the picture shows

  • Click on the Format option and apply color to your formatting in the Fill option of Format Cells box

  • After selecting the color press on OK. You will see your selected color is shown in Preview area. Press Ok to apply the formatting

Using the Formula ISEVEN for Row Highlighting

In the following picture, you can see the even rows are highlighted.

Using the Formula ISODD

Using the formula ISODD in conditional formatting you can highlight the odd rows in a specific range. For example, if you want to highlight the odd rows from the range A1: D9, choose the entire range and select the New Rule under Conditional Formatting and use this formula =ISODD(ROW()). The procedure is stated below.

  • 1st select the entire range where you want to apply the conditional formatting
  • In the Conditional Formatting option, select the NEW Rule option

  • In New Formatting Rule box do the following as the picture shows

  • Click on the Format option and apply color to your formatting in the Fill option of Format Cells box

  • After selecting the color press on OK. You will see your selected color is shown in Preview area. Press Ok to apply the formatting

Using the Formula ISODD for Row Highlighting

In the following picture, you can see the odd rows are highlighted.

Formatting in a Group Using Multiple Functions in a Single Formula

Suppose you need to highlight 2 rows in a group. You can apply conditional formatting with a formula based on the SEVEN/ISODD, CEILING and ROW functions to perform this highlighting. The formula that we can use is, =ISEVEN(CEILING(ROW()-1,2/2)

The formula 1st normalized the row numbers for beginning with 1 using the ROW function and an offset. Here, we used the offset as 1. The result then goes to the CEILING function, that rounds the result by a multiplying 2. Then it is divided by 2 to count as a group of 2, which starts 1. Finally, to show TRUE result in the even row groups the ISEVEN function is taken in the formula. We can also use the ISODD function instead of ISEVEN. Based on the formula and numbers stated in the formula the output will be different. The pictures below show the result that we discussed in this example.

Row highliting in a group using ROW, CEILING and ISEVEN/ISODD Functions

Using the MOD and ROW Function to Highlight Rows

Instead of ISEVEN/ISODD function, we can also use the MOD function to highlight different rows. Like the ISEVEN/ISODD function, this formula also determines whether a row is even or odd numbered, and then applies the shading accordingly. The formula for this is, =MOD (ROW (),2) =0. MOD carries a number with a divisor and returns a number as remainder. Here the number is provided by the ROW function which is then divided by 2. If the number is even, MOD returns 0. The following pictures show the highlighted even rows.

Using the MOD and ROW Function to Highlight Rows

If you want to highlight the odd rows using the same formula, you can just use a 1 instead of 0 in the above formula. The result and formula stated in the conditional formatting are shown in the below pictures.

Using the MOD and ROW Function to Highlight Rows

Note: The divisor cannot be zero or one. If zero is used as divisor no shading will be found in the range. If one is used as divisor the whole range will be shaded.

If you want to highlight every 2 rows which start from the 1st group, the formula will be =MOD(ROW()-2,4)+1<=2

Again If you want to highlight every 2 rows which start from the 2nd group, the formula will be =MOD(ROW()-2,4)>=2 and to highlight every 3 rows which start from the 2nd group, the formula will be =MOD(ROW()-3,6)>=3.

Using Excel VBA for row shading

For highlighting different rows in excel we can also use the VBA code. Here in this example, we used a VBA code that highlights the even rows. The code was found at this link https://www.ozgrid.com/forum/forum/help-forums/excel-general/29524-select-range-change-color-of-every-other-row

The code is given below.

 

Sub test()
 
Dim rng As Range, i
 
On Error Resume Next
 
Set rng = Application.InputBox("Selet Range", Type:=8)
 
For i = 1 To rng.Rows.Count
 
    If i Mod 2 = 0 Then
 
        rng.Rows(i).Interior.ColorIndex = 15
 
    End If
 
Next
 
End Sub

To use this code, you need click on the Developer tab and then press on the Visual Basic option located in the left of your spreadsheet. Copy this code in the blank box. After pressing on the Run option, it will ask for the range. Put the range where you want to apply the code and press OK. You will see the even rows in your table are highlighted.

Read More

Conclusion

In this article, we can see different methods of highlighting rows. Shading/Highlighting different rows in excel improve readability and legibility. While working on a big spreadsheet, it is better to highlight rows.

Hopefully, from now you won`t have any problem while applying color banding in different rows of Excel. This article may help you on the question of how to highlight every other row in excel. If you have applied any other approach of highlighting rows, please don’t hesitate to leave a comment.


Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply