Highlight alternate rows in Excel

When we have a large amount of data on our sheet, it becomes difficult to distinguish the information that belongs to the same row.

To avoid this problem we can highlight the alternate rows of a different color and that will allow us to distinguish in a better way the information in each of the rows.

There are some Excel users who make this change manually by first selecting the alternate rows and then changing the fill color, but there are better methods to do this and we will review them below.

Create table to highlight alternate rows

The quickest method to highlight alternate rows is to convert our cell range to a Excel table.

To do so, just activate any cell of the data and in the Home tab we click the button and we choose one of the available styles.

Create table to highlight alternate rows

By converting our data into a table we will obtain additional benefits such as filters or the row of totals. However, there will be times when we do not want to create a table and in this case we must use one of the alternate methods that I describe below.

Highlight alternate rows with Conditional Formatting

We start by selecting the data on which we will apply the conditional format and then we go to the tab and press the button and we choose the option .

Highlight alternate rows in Excel

Within the dialog box you must select the option and in the text box you must enter the following formula:


The ROW function will return the number of the current row and the WASTE function it will divide the number in the row by 2 and return the remainder. The even rows will have a remainder equal to zero and therefore, when making the comparison with zero we will obtain the true value.

Highlight alternate rows with conditional formatting

We already have the formula that will determine the rows to which we will apply the rule and we only need to define the format and for that we must click on the button which is below the text box where we just entered the formula.

A new dialog box called and this is where we will choose a different fill color. For that, I will activate the tab and I will choose one of the available colors.

Apply shading to alternate rows in Excel

We click on the button to close the dialog and we click the button again to close the dialog .

Our conditional formatting rule will be created and the fill color will immediately be applied to the even rows of our sheet.

How to highlight alternate rows in Excel

The formula that we enter in our rule causes the format to be applied in the even rows, but in case you want to apply the format to the odd rows then you must modify the formula as follows:


If you don’t want to use conditional formatting to highlight alternate rows, then you can use a different technique that will help us filter the data in order to apply the appropriate formatting to the rows of our interest.

Highlight alternate rows using a filter

For this technique we will need an auxiliary column in our data that will be in charge of distinguishing the even rows from the odd ones.

I’ll add a new column and enter a formula very similar to the one we use in our conditional formatting rule:


This formula will return the number zero for the even rows and the number one for the odd rows. We copy the formula in the entire column and we will have the result for each of the rows of our data.

How to shade alternate rows in Excel

To activate the filter on the data, you must go to the tab and within the group open button menu to choose the option . Then you must open the filter menu for the auxiliary column and choose only the number zero.

Shade alternate rows in Excel

Applying the filter will show only the even rows and we can select the range of cells and apply the fill color of our preference.

Color alternate rows in Excel

At the time of removing the filter we will have highlighted the alternate rows of our data. Since the format has been applied to the cells, you can remove the filter and the auxiliary column if you wish.

With any of the three methods described above you will be able to highlight the alternate rows of your data in a simple and fast way.

Download job file

Leave a Comment