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:**

- Firstly, you should select the data up to which you want to apply the
**Conditional Formatting**to alternate the row colors**.**Here, I have selected the data range**B5:E14**.

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

## 💬 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)**.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained 5 methods to *Alternate Row Colors in Excel Without Table*. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

- How to Alternate Row Color Based on Group in Excel
- How to Color Alternate Row Based on Cell Value in Excel
- How to Color Alternate Row for Merged Cells in Excel

**<< Go Back to Alternating Row Colors | Highlight Row | Highlight in Excel | Learn Excel**