5 Ways to Add Alternate Row Colors Without Table in Excel

Color Banding or alternate coloring rows makes the data in the worksheet easier to scan. A preset table format can quickly add the color format to alternate rows or columns. When you use this method, alternate row or column shading is applied automatically when you add rows. However, sometimes table format is unavailable or not required for the dataset. So, we will demonstrate 5 simple approaches to alternate row colors in Excel without a table.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Learn to Add Alternate Row Colors Without Table in Excel with These 5 Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the name of the salesperson and the product name sold by them. Now, we want to highlight alternate rows in this dataset. Hence, we will use this dataset to demonstrate 5 simple approaches to alternate row colors in Excel without any table.

This method uses the Fill Color option in the ribbon to alternate row colors in Excel without a table. Fill Color highlights the value in cells by changing the background color or pattern of the cells. This method selects alternate cells manually. However, you can select alternate rows differently and click here to see how to select every other row in Excel.

Now, let us see the steps to apply the Fill Color option.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the alternate rows. We selected rows 7, 9, 11, 13, and 15 by clicking on them while holding down the Ctrl key.

• Then go to the Home tab and click on the Fill Color option from the Font. There are Theme Colors and Standard Colors. You can choose any color according to your desire. We chose Gold, Accent 4, and Lighter 80% from the Theme Colors.

• Lastly, we will have the alternate rows colored in Excel without using any table.

This method applies the Cell Styles option in the ribbon to alternate row colors in Excel without a table. Cell Style is a predefined collection of formatting elements such as cell borders, fonts and font sizes, cell shading, and number formats, and Excel includes several cell styles that you can use or modify. A cell style applies multiple formats in one step, ensures that cells have persistent formatting, and locks the cells to prevent others from making changes to specific cells.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the alternate rows. We selected rows 7, 9, 11, 13, and 15 by clicking on them while holding down the Ctrl key.

• Then go to the Home tab and click on the Cell Styles option from the Styles group. There are different ranges of Cell Styles. You can choose any style according to your desire. We chose 20%-Accent4 from the Themed Cell Styles.

• Finally, we will have the alternate rows colored in Excel without a table.

This method employs the Conditional Formatting option in the ribbon to alternate row colors in Excel without a table. Conditional formatting highlights specific values or makes specific cells obvious. Depending on a condition, this modifies how a cell range looks (or criteria). Cells with values that satisfy a particular requirement can be highlighted using conditional formatting. Alternatively, you can format an entire cell range and change the format precisely as the value of each cell changes.

We can combine MOD and ISEVEN functions with ROW function as the condition to alternate color rows. By employing Conditional Formatting, we can save time manually selecting alternate rows. Moreover, this method is more practical in the case of huge datasets.

Now, let’s follow the steps now.

This method combines MOD and ROW functions to alternate color rows. MOD function divides a number and returns the remainder after the division. On the other hand, the ROW function returns the corresponding cell’s row number.

Now, let us follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the range B6:C15. Then select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

• Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type, enter the formula in “Format values where this formula is true”, and click on Format.

• Choose the format according to your desire from the Format Cells box and press OK.

• Lastly, we will have the alternate rows colored in Excel.

This method combines ISEVEN and ROW functions to alternate color rows. ISEVEN function checks if a number is even or odd and returns TRUE if even otherwise, FALSE. On the other hand, the ROW function returns the corresponding cell’s row number.

Now, let us follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the range B6:C15. Then select New Rule from the Conditional Formatting of the Home tab in the Ribbon.

• Next, New Formatting Rule named box will pop up. Select “Use a formula to determine which cells to format” as the rule type, enter the formula in “Format values where this formula is true” and click on Format.

• Choose the format according to your desire from the Format Cells box and press OK.

• Lastly, we will have the alternate rows colored in Excel.

This method utilizes the Filter option in the Data tab to alternate row colors in Excel without a table. The filter applies a drop-down on the header of the selected data. In addition, in the drop-down, there is an option for every type of data in that column. Hence, you can choose any option from there, and the data set will be filtered according to that and will show only the filtered data, and Filter will hide the rest of the data.

Before applying the filter to the data, we used a formula combining MOD, IF, and ROW functions. MOD function divides a number and returns the remainder after the division, and IF function returns a specified value according to if a logical test is TRUE and another value if FALSE. On the other hand, the ROW function returns the corresponding cell’s row number.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell D6 and insert the formula.

• Then, drag or double-click on the Fill Handle.

• After that, select the range B6:D15. Then, select the Filter option from the Data tab in the Ribbon.

• Next, click on the dropdown in column D, and select 1 from the options.

• Then select the visible cells in the range B6:D15, go to the Home tab, and click on the Fill Color option from the Font group. Choose Gold, Accent 4, and Lighter 80% (or any color you prefer) from the Theme Colors.

• After that, click on the dropdown in column D again and select all from the options. All the cells will be visible then.

• Finally, we will have the alternate rows colored in Excel in the normal dataset.

This method will demonstrate how to alternate row colors in Excel without a table using Microsoft Visual Basic Application (VBA).

So, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select Visual Basic from the Code group on the Developer tab.

• After that, a named box for Microsoft Visual Basic for Application will appear. Choose Module from the Insert tab.

• Enter the code and press F5.

Sub AlternatingRowColors()

Dim R As range
Dim row As Long
Dim NoCol As Long
Dim Color As Long

  NoCol = vbWhite
  Color = RGB(204, 255, 204)
  Set R = Selection

  If R.Rows.Count = 1 Then Exit Sub

  For row = 1 To R.Rows.Count
    If row Mod 2 = 0 Then
      R.Rows(row).Interior.Color = Color 'Even Row
    Else
      R.Rows(row).Interior.Color = NoCol 'Odd Row
    End If
  Next row

End Sub

• Now, select range B6:C15.
• After that, press Alt+F8, select AlternatingRowColors, and then Run.

• Finally, we will have the alternate rows colored in Excel without a table.

📝 Takeaways from This Article

📌  This article demonstrates how to alternate row colors in Excel without a table.

📌  In the first method, we used the Fill Color option in Excel.

📌  Then, we applied the Cell Styles to alternate row colors.

📌  Next, we employed the Conditional Formatting option in Excel.

📌  Then, we utilized the Filter option to alternate row colors.

📌  Finally, we embedded a VBA code to alternate row colors in Excel without any table.

Conclusion

This article has demonstrated 5 approaches to alternate row colors in Excel without a table. All these approaches are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.