How to Color Alternate Rows in Excel (8 Ways)

Get FREE Advanced Excel Exercises with Solutions!

For decorating a dataset, you can color alternate rows in Excel. In this article, you will get to know several easiest and shortest methods to do this job properly. So, let’s get into the main article.


How to Color Alternate Rows in Excel: 8 Ways 

We will show the ways to color alternate rows in Excel by using the following dataset of a company containing the sales records of different products.

Excel color alternate rows

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.


Method-1: Color Alternate Rows Manually in Excel

Here, we will fill the alternate rows with color by selecting those rows and then selecting our desired background color. So, this method is helpful for a small dataset.

Excel color alternate rows

Steps:
➤ As the alternate rows are not adjacent rows, we have selected them by pressing CTRL.

Manually selection

➤ After the selection procedure, go to Home Tab >> Font Group >> Fill Color Dropdown >> Select any color from the list.

Manually selection

Result:
In this way, you will be able to fill the alternate rows with your desired color.

Excel color alternate rows


Method-2: Using Format as Table Option in Excel to Color Alternate Rows

To fill every other row with any color you can use the Format as Table option easily.

Excel color alternate rows

Step-01:
➤ Go to Home Tab >> Styles Group >> Format as Table Dropdown >> select any table style.

Format as Table

Then the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

Format as Table

After that, you will get the following table with colored alternate rows.

Excel color alternate rows

Step-02:
If you want to keep only the color of the rows excluding the table format, then follow this step.
➤ After selecting the table, go to Table Design Tab >> Tools Group >> Convert to Range Option.

Format as Table

Then, a message box will pop up saying “Do you want to convert the table to a normal range?”
➤ Click Yes in the message box.

Format as Table

Finally, we will get back our previous data range but with colors in the alternate rows.

Excel color alternate rows


Method-3: Creating a Table to Color Alternate Rows in Excel

We will use the Table option to color the alternate rows of the following dataset.

Excel color alternate rows

Steps:
➤ Go to Insert Tab >> Table Option.

using Table option

After that, the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

Excel color alternate rows

Then, our data range will be converted into the following table with colored alternating rows.

using Table option

To convert the table into the previous range you can follow Step-02 of Method-2.
Afterward, we get the following data range which has the banded rows.

Excel color alternate rows


Method-4: Using Power Query to Color Alternate Rows in Excel

You can use the Power Query also to apply color to the alternate rows easily.

Excel color alternate rows

Steps:
➤ Go to Data Tab >> From Table/Range Option.

Power Query

Afterward, the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

Excel color alternate rows

Then, you will be taken to the Power Query Editor window.

Power Query

➤ Go to Home Tab >> Close & Load Dropdown >> Close & Load Option.

Power Query

So, we have closed the Power Query Editor window and loaded the table to a new sheet named Table 3.

Power Query

After that, you can remove the gridlines of this sheet and follow Step-02 of Method 2 to revert back to the data range again with the banded rows.

Excel color alternate rows


Method-5: Using Format Painter Option

In this section, we will use the Format Painter option to apply color to every other row of the following dataset.

Excel color alternate rows

Steps:
➤ After selecting the second row of the range, go to Home Tab >> Font Group >> Fill Color Dropdown >> Select any color from the list.

Format Painter

Then the selected color will appear in the second row, so we have now created the format which contains one row with no color and another row with a color.
Using the Format Painter option we will copy this format to the remaining rows.

Format Painter

➤ Select the first two rows and go to Home Tab >> Clipboard Group >> Format Painter Option.

Excel color alternate rows

Then the Format Painter sign will appear, drag it down and to the right side.

Format Painter

Result:
As a result, we can see that our data range has banded rows now.

Excel color alternate rows


Method-6: Using Conditional Formatting to Color Alternate Rows in Excel

You can use Conditional Formatting to fill the alternate rows with your desired color.

Excel color alternate rows

Steps:
➤ Select the cell range on which you want to apply the Conditional Formatting. (Here, we have selected the range B4:E14)
➤ Go to Home Tab >> Conditional Formatting Dropdown >> New Rule Option.

conditional formatting

Then the New Formatting Rule Wizard will appear.
➤ Select Use a formula to determine which cells to format option and then write the following formula in the Format values where this formula is true box

=MOD(ROW(),2)=0

MOD will return a remainder after dividing the row number by 2 and ROW is giving the row number of a certain row. If the remainder is 0 then the rows will be highlighted and for only even rows the resultant value will be zero.

➤ Finally, click the Format Option.

Excel color alternate rows

After that, the Format Cells Dialog Box will open up.
➤ Select Fill Option.
➤ Choose any Background Color and click on OK.

conditional formatting

Eventually, the Preview Option will be shown as below.
➤ Press OK.

conditional formatting

Result:
As a result, we are getting our alternate rows highlighted with our chosen color.

Excel color alternate rows

Read More: How to Number Rows in Excel


Method-7: Using the ISEVEN Function and Filter Option

In this section, we are going to use the ISEVEN function and the Filter option to apply color to the alternate rows, and for this purpose, we have added the Helper column.

Excel color alternate rows

Step-01:
➤ Write the following formula in the cell F4

=ISEVEN(ROW())

ISEVEN will determine if the corresponding row number is even or not and if it is even then it will return TRUE otherwise FALSE.

ISEVEN function

➤ Press ENTER and drag down the Fill Handle tool.

ISEVEN function

So, we are getting TRUE for the even rows like Row 4, Row 6, Row 8,….. etc, and FALSE for the odd rows like Row 5, Row 7, Row 9,…. etc.

ISEVEN function

Step-02:
Now, it’s time to filter this dataset on the basis of either TRUE or FALSE.
➤ Select the data range and then go to Home Tab >> Editing Group >> Sort & Filter Dropdown >> Filter option.

using Filter

After that, we will get the filtered table.
➤ Click on the dropdown symbol of the Helper column.

Excel color alternate rows

➤ Then click on the FALSE option to unhide it and unclick the TRUE option to hide it and press OK.

using Filter

After that, the rows with TRUE will be hidden and now, you can fill the remaining rows with any color easily.

using Filter

➤ After selecting the rows, go to Home Tab >> Font Group >> Fill Color Dropdown >> Select any color from the list.

using Filter

Finally, we are getting the rows highlighted.

using Filter

Afterward, click on the filter symbol of the Helper column and select the option Clear Filter From “Helper”.

using Filter

Eventually, we get our desired colored alternate rows.

using Filter

If you want to remove the Filter from this dataset then after selecting the data range go through Home Tab >> Editing Group >> Sort & Filter Dropdown >> Unclick Filter option.

using Filter

So, now we are having our normal data range having color in alternate rows.

Excel color alternate rows


Method-8: Using VBA Code

You can use a VBA code also to apply color in the alternate rows.

Excel color alternate rows

Step-01:
➤ Go to Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

VBA Code

➤ Write the following code

Sub alternatecolor()

Dim x As Integer

For x = 1 To Selection.Rows.Count
    If x Mod 2 = 0 Then
       Selection.Rows(x).Interior.Color = vbGreen
    End If
Next

End Sub

Here, we have declared x as Integer and the FOR loop will work for each row of our dataset, and IF will ensure that if the row number is divided by 2 or even then it will only be colored.

Excel color alternate rows

Step-02:
➤ Now, return to the sheet and select the dataset.
➤ Go to Developer Tab >> Macros Option.

VBA Code

Then the Macro wizard will open up.
➤ Select the macro alternatecolor (that we have created in the previous step) and press Run.

VBA Code

Result:
Finally, you will be able to fill every other row with your desired color. One thing is to notice that here Excel is considering the first row of our selection as Row 1 and so it is odd (basically it was Row 4) and the following row as Row 2 and so it is colored because it is even ( actually it was Row 5).

Excel color alternate rows


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Download Workbook


Conclusion

In this article, we tried to cover the ways to color alternate rows in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<< Go Back to Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo