How to Color Alternate Rows in Excel (8 Ways)

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.

Download Workbook

8 Ways to Color Alternate Rows in Excel

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.

We have used Microsoft Excel 365 version here, you can use any other versions 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.

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

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

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

Read More: How to Color Alternate Row Based on Cell Value in Excel

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.

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

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

After that, you will get the following table with colored 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.

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.

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

Read More: How to Alternate Row Colors in Excel Without Table (5 Methods)

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.

Steps:
➤ Go to Insert Tab >> 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.

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

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

Read More: How to Highlight Active Row in Excel (3 Methods)

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.

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

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

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

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

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

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.

Read More: How to Color Alternate Row for Merged Cells in Excel

Similar Readings

• How to Freeze Rows in Excel (6 Easy Methods)
• Lock Rows in Excel (6 Easy Methods)
• How to Shade Every Other Row in Excel (3 Ways)
• How to Collapse Rows in Excel (6 Methods)

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.

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

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.

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

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

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

Related Content: How to Highlight Row If Cell Is Not Blank (4 Methods)

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.

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.

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 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.

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

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

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

Read More: How to Group Rows in Excel with Expand or Collapse (5 Methods)

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.

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

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

➤ Press ENTER and drag down the Fill Handle tool.

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.

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.

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

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

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

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

Finally, we are getting the rows highlighted.

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

Eventually, we are getting our desired colored alternate rows.

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.

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

Related Content: How to Resize All Rows in Excel (6 Different Approaches)

Method-8: Using VBA Code

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

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

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

After that, a Module will be created.

➤ 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.

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

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

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).

Read More: VBA to Hide Rows in Excel (14 Methods)

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.

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

• [Fix]: Unable to Unhide Rows in Excel (4 Solutions)
• How to Group Rows by Cell Value in Excel (3 Simple Ways)
• How to Hide and Unhide Rows in Excel (6 Easiest Ways)
• Shortcut to Unhide Rows in Excel (3 Different Methods)
• How to Hide Rows Based on Cell Value in Excel (5 Methods)
• Data Clean-up Techniques in Excel: Randomizing the Rows
• How to Unhide All Rows in Excel (All Possible Ways)
• Highlight Row If Cell Contains Any Text