# How to Alternate Row Colors in Excel Without Table (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

For the purpose of attracting your data, you can alternate Row colors in Excel without even making a Table. In this article, I will explain how to alternate Row colors in Excel without Table.Â

Here, I will describe 5 methods to alternate Row colors in Excel without Table. Also, for your better understanding, Iâ€™m going to use sample data which has 4 columns. These are Product, Sales, Profit, and Status.

## 1. Using Fill Color Option to Alternate Row Colors in Excel Without Table

You can use the Fill Color feature to alternate Row colors in Excel without Table. This is absolutely a manual process. So, when you have so much data then it will be quite time-consuming. The steps are given below.

Steps:

• Firstly, you have to select the Rows which you want to color. Here, I have selected Rows 6, 8, 10, 12, and 14.

• After that, you need to go to the Home tab.
• Now, from the Fill Color feature >> you have to choose any of the colors. Here, I have chosen Green, Accent 6, Lighter 60%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.

Finally, you will see the result with alternate Row colors.

## 2. Applying Cell Styles Feature to Alternate Row Colors in Excel

You can use the Cell Styles feature to alternate Row colors in Excel without Table. This is absolutely a manual process. So, when you have so much data then it may be quite time-consuming. The steps are given below.

Steps:

• Firstly, you have to select the Rows which you want to color. Here, I have selected Rows 6, 8, 10, 12, and 14.
• Secondly, from the Home tab >> you need to go to the Cell Styles feature.
• Thirdly, choose your preferred colors or styles. Here, I have chosen the Calculation.

Finally, you will see the following result with alternate Row colors.

## 3. Applying Conditional Formatting with Formula to Alternate Row Colors in Excel Without Table

You can apply Conditional Formatting with the formula. Here, I will use two different formulas with the ROW function. In addition, Iâ€™m going to use the MOD and ISEVEN functions.

### 3.1. Use of MOD and ROW Functions

Letâ€™s start with the MOD and ROW functions to alternate Row colors in Excel without Table. The steps are given below.

Steps:

• Now, from the Home tab >> you must go to the Conditional Formatting command.
• Then, you need to choose the New Rule option to apply the formula.

At this time, a dialog box named New Formatting Rule will appear.

• Now, from that dialog box >> you have to select Use a formula to determine which cells to format.
• Then, you need to write down the following formula in the Format values where this formula is true: box.
`=MOD(ROW(),2)`
• After that, go to the Format menu.

Formula Breakdown

• Here, the ROW function will count the number of Rows.
• The MOD function will return the remainder after division.
• So, MOD(ROW(),2)â€“> Becomes 1 or 0 because the divisor is 2.
• Finally, if the Output is 0 then there will be no fill color.

At this time, a dialog box named Format Cells will appear.

• Now, from the Fill option >> you have to choose any of the colors. Here, I have chosen Green, Accent 6, Lighter 40%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.
• Then, you must press OK to apply the formation.

• After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

Finally, you will get the result with alternate Row colors.

### 3.2. Use of ISEVEN and ROW Functions to Alternate Row Colors in ExcelÂ

Now, I will show you the use of ISEVEN and ROW functions to alternate Row colors in Excel without Table. The steps are similar to the previous method.

• Â Firstly, you have to follow method-3.1 to open the New Formatting Rule window.
• Secondly, from that dialog box >> you have to select Use a formula to determine which cells to format.
• Thirdly, you need to write down the following formula in the Format values where this formula is true: box.
`=ISEVEN(ROW())`
• Finally, go to the Format menu.

Formula Breakdown

• Here, the ISEVEN function will return True if the value is an even number.
• The ROW function will count the number of Rows.
• So, if the Row number is odd then the ISEVEN function will return FALSE. As a result there will be no fill color.

At this time, a dialog box named Format Cells will appear.

• Now, from the Fill option >> you have to choose any of the colors. Here, I have chosen Gold, Accent 4, Lighter 60%. Also, you can see the formation below in the Sample box. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.
• Then, you must press OK to apply the formation.

• After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

Lastly, you will see the result with alternate Row colors.

## 4. Using Excel Formula with Sort & Filter Command to Alternate Row Colors Without Table

You can use a formula with the Sort & Filter command to alternate Row colors in Excel without Table. Furthermore, I will use the MOD, IF, and ROW functions in the formula. The steps are given below.

Steps:

• Firstly, you have to select a cell, where you want to keep the output. I have selected the F5 cell.
• Secondly, use the corresponding formula in the F5 cell.
`=MOD(IF(ROW()=2,0,IF(E5=E4,F4, F4+1)), 2)`

Formula Breakdown

• Here, IF(E5=E4,F4, F4+1)â€“> This is a logical test where if the value of E5 cell is equal to E4 cell then it will return the value of F4 cell otherwise it will give 1 increment with F4 cell value.
• Output: 1
• Then, the ROW() function will count the number of Rows.
• Output: 5
• IF(5=2,0,1)â€“> This logical test says that if 5 is equal to 2 then it will return 0 otherwise it will return 1.
• Output: 1
• The MOD function will return the remainder after division.
• Finally, MOD(1,2)â€“> becomes.
• Output: 1

• After that, you have to press ENTER to get the result.

• Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F14.

At this time, you will see the following result.

• Now, select the data range. Here, I have selected B4:F14.
• Then, from the Home ribbon >> go to the Editing tab.
• Then, from the Sort & Filter feature >> you have to choose the Filter option. Here, you can apply the Keyboard technique CTRL+SHIFT+L.

At this time, you will see the following situation.

• Now, you should click on the Drop-Down Arrow on the F column.
• Then, select 1Â and uncheck 0.
• Finally, press OK.

Subsequently, you will see the following filtered output.

• After that, you need to select the filtered data.
• Then, you have to go to the Home tab.
• Now, from the Fill Color feature >> you have to choose any of the colors. Here, I have chosen Green, Accent 6, Lighter 60%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.

• Now, to remove the Filter feature, from the Home ribbon >> go to the Editing tab.
• Then, from the Sort & Filter feature >> you have to choose again the Filter option.
• Otherwise, Â you can press CTRL+SHIFT+L to remove the Filter feature.

Lastly, you will see the result with the same Row colors for the same Status.

## 5. Applying VBA Code to Alternate Row Colors in Excel Without Table

You can employ a VBA code to alternate Row colors in Excel without Table. The steps are given below.

Steps:

• Firstly, you have to choose the Developer tab >> then select Visual Basic.

• Now, from the Insert tab >> select Module.

• Write down the following Code in the Module.
``````Sub ChangeRowColors()
Dim range As range
Dim chr As Long
Dim NoColor As Long
Dim Colored As Long
'I will Define Color as Input
Â Â NoColor = vbWhite
Â Â Colored = RGB(0, 255, 255)
'Select a range as variable
Â Â Set range = Selection
'You should select more than 1 Row
Â Â If range.Rows.Count = 1 Then Exit Sub
'Loop for Color Changing
Â Â For chr = 1 To range.Rows.Count
Â Â Â Â If chr Mod 2 = 0 Then
Â Â Â Â Â Â range.Rows(chr).Interior.Color = Colored 'Even Row
Â Â Â Â Else
Â Â Â Â Â Â range.Rows(chr).Interior.Color = NoColor 'Odd Row
Â Â Â Â End If
Â Â Next chr
End Sub``````

Code Breakdown

• Here, I have created a Sub Procedure named ChangeRowColors.
• Next, declare some variables range as Range to call the range; chr as Long; NoColor as Long; Colored as Long.
• Here, RGB (0, 255, 255) is a light color called Aqua.
• Then, the Selection property will select the range from the sheet.
• After that, I used a For Each Loop to put Color in each alternate selected Row using a VBA IF Statement with a logical test.

• Now, Save the code then go back to Excel File.

• After that, select the range B5:E14.
• Then, from the Developer tab >> select Macros.

• At this time, select Macro (ChangeRowColors) and click on Run.

Finally, you will see the result with alternate Row colors.

Read More: How to Make VBA Code Run Faster (15 Suitable Ways)

## ðŸ’¬ Things to Remember

• When you have lots of data then you should use method 3 (Conditional Formatting) or method 5 (VBA Code). This will save your time to alternate Row colors.
• In the case of a tiny dataset, you can easily use method 1 (Fill Color) or method 2 (Cell Styles).
• Furthermore, when you want to color similar data or something sorted then you should use method 4 (Sort & Filter).

## Related Articles

Musiha Mahfuza Mukta

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF