Need to learn how to color alternate row based on cell value in Excel? When we work on a large datasheet, we need to alternate the row color to visualize our dataset better. If you are looking for such unique kinds of tricks, you’ve come to the right place. Here, we will take you through 10 easy and convenient methods of alternating row color based on the cell value in Excel.

## How to Color Alternate Row Based on Cell Value in Excel: 10 Ways

To demonstrate the approaches, we consider a report on the *Daily Sales- Fruits Section* of a certain grocery store. This dataset contains the name of *Products* in **Column B**, and the name of the *Customers* and the *Quantity* of products are in columns **C** and **D**, respectively.

Now, we will show the ways to alternate row colors based on the value in Excel by using the above dataset. In this case, we’ll consider the cell values of **Column B** as the base values. Based on these values, the entire row will get colored.

### 1. Color Alternate Row Manually Based on Cell Value in Excel

Here, we’ll alternate the row color by selecting those rows and then selecting our desired background color. Let’s go through the steps below.

**📌**** Steps**

- As the alternate rows are not adjacent rows, we have selected them by pressing
**CTRL**.

- After the selection procedure, go to the
**Home**tab. - Then, select the
**Fill Color**drop-down on the**Font**group. - Later, choose
**Green, Accent 6, Lighter 60%**from the available colors.

- In this way, you can give alternate row colors based on the cell value in Excel.

### 2. Using Format as Table Option in Excel

To fill the alternative rows with any color you can use the **Format as Table** option easily. The steps of this process are given below:

**📌**** Steps**

- First of all, select the dataset. In this case, we selected cells in the
**B4:D14**range. - Secondly, move to the
**Home**tab. - Then, select the
**Format as Table**drop-down in the**Styles**group. - Later, choose
**Light Green, Table Style Light 7**from the options.

- Instantly, the
**Create Table**dialog box will appear. - At this point, ensure to check the
**My table has headers**option. - Then, click
**OK**.

- Now, select the whole table. In this case, we selected cells in the
**B4:D14**range. - After that, go to the
**Table Design**tab. - Next, select Convert to Range in the
**Tools**group.

- At this moment, click on
**Yes**in the warning box.

- Finally, we will get back our previous data range but with alternate row colors based on cell values.

### 3. Creating Table to Color Alternate Row Based on Cell Value in Excel

We will use the **Table** option to alternate the row colors based on the cell values of the following dataset. Follow the steps below.

**📌**** Steps**

- At first, select cells in the
**B4:D14**range. - Then, go to the
**Insert**tab. - After that, select the
**Table**option on the**Tables**group.

- Suddenly, the
**Create Table**dialog will pop up. - Then, click
**OK**.

- Thus, we can convert our data range into the following table with alternate row colors.

- Again, to convert the table into the range you can follow the steps of Method 2.

**Read More:** How to Alternate Row Colors in Excel Without Table

### 4. Using Format Painter Option

In this section, we will use the Format Painter option to alternate the row colors based on the value of the following dataset. Let’s go through the procedure below.

**📌**** Steps**

- After selecting the second row of the range, go to the
**Home**tab. - Then, select the
**Fill Color**drop-down on the**Font**group. - Later, choose
**Green, Accent 6, Lighter 60%**from the available colors.

So we have now created the format which contains one row with no color and another row with a color. Now, using the **Format Painter** option we will copy this format to the remaining rows.

- At the very beginning, select the first two rows i.e rows
**5**and**6**. - Secondly, select the
**Format Painter**option on the**Clipboard**group. - Then the
**Format Painter**sign will appear, drag it down and to the right side.

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

### 5. Applying IF and MOD Functions

In this approach, we will use the **IF** and **MOD** functions to get the binary grouping of our dataset. The steps of this approach are given below:

**📌**** Steps**

- Initially, select cell
**E4**and write down**Zero (0)**into the cell.

`0`

- Then, press
**ENTER**.

- Then, select cell
**E5**and write down the following formula into the cell.

`=MOD(IF(B5=B4,E4,E4+1),2)`

**Formula Breakdown**

**IF(B5=B4, E4, E4+1):**The

**IF**function checks the value of cell

**B5**with

**B4**. If both values match each other, the function returns the value of cell

**E4**. Otherwise, it will add

**1**with the value of cell

**E4**and return that.

**MOD(IF(B5=B4, E4, E4+1),2):**The

**MOD**function will divide the result of the

**IF**function by

**2**and show the value of the remainder.

- Simply, press the
**ENTER**key.

- After that, double-click on the Fill Handle icon to copy the formula up to cell
**E14**.

- As a result, the remaining cells of
**Column E**get their results.

- Now, select the range of cells
**B5:E14**. - After that, go to the
**Home**tab - Then, click on the
**drop-down arrow**of the Conditional Formatting on the**Styles**group. - Later, select the
**New Rule**option from the drop-down list.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Then, select
**Use a formula to determine which cells to format**under the section of**Select a Rule Type**. - Afterward, write down the following formula into the empty box below
**Format values where the formula is true**text.

`=$E5=1`

- Then, click on the
**Format**option.

- Another dialog box called
**Format Cells**will appear. - In the
**Fill**tab, choose a color according to your desire to make the rows distinguishable. We have chosen**Green, Accent 6, Lighter 60%**color. - In the end, click
**OK**.

- Again, click
**OK**to close the**New Formatting Rule**dialog box.

- Then, right-click on
**Column E**. - After that, select the
**Hide**option on the**context menu**.

- As a result, the extra column is hidden now. And, the result looks like the image below.

### 6. Combining IF, MOD, and ROW Functions

In this process, the **IF**, **MOD**, and **ROW** functions will help us to get the binary grouping of our dataset. Let’s explore the method step by step.

**📌**** Steps**

- Primarily, select cell
**E4**and write down**Zero (0)**into the cell.

`0`

- Then, press
**ENTER**.

- Then, select cell
**E5**and write down the following formula into the cell.

`=MOD(IF(ROW()=2,0,IF(B5=B4,E4,E4+1)),2)`

**Formula Breakdown**

**ROW():**The

**ROW**function returns the row number. In this case, the value is

**5**.

**IF(B5=B4, E4, E4+1):**The

**IF**function checks the value of cell

**B5**with

**B4**. If both values match each other, the function returns the value of cell

**E4**. Otherwise, it will add

**1**with the value of cell

**E4**and return that.

**IF(ROW()=2,0,IF(B5=B4,E4, E4+1)):**In this formula, the

**IF**function checks whether the row number is equal to

**2**. If the logic is True, the function returns

**0**. Or, if the logic is False the function returns the result of the second

**IF**function.

**MOD(IF(ROW()=2,0,IF(B5=B4,E4, E4+1)),2):**The

**MOD**function will divide the result of the

**IF**function by

**2**and show the value of the remainder.

- Simply, press the
**ENTER**key.

- Now, select the range of cells
**B5:E14**. - After that, go to the
**Home**tab - Then, click on the
**drop-down arrow**of the**Conditional Formatting**on the**Styles**group. - Later, select the
**New Rules**option from the drop-down list.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Then, select
**Use a formula to determine which cells to format**under the section of**Select a Rule Type**. - Afterward, write down the following formula into the empty box below
**Format values where the formula is true**text.

`=$E5=1`

- Then, click on the
**Format**option.

- Another dialog box called
**Format Cells**will appear. - In the
**Fill**tab, choose a color according to your desire to make the rows distinguishable. We choose**Green, Accent 6, Lighter 60%**color. - In the end, click
**OK**.

- Again, click
**OK**to close the**New Formatting Rule**dialog box.

- In this way, you can give alternate row colors based on the value in Excel.

- Besides, you can follow the steps of Method 5 to hide the extra column.

### 7. Utilizing AND, IF, LEN, and MOD Functions

In this method, first, we are going to use the** IF** function in our dataset to get the numerical grouping. Besides that, we have to use the **AND**, **LEN**, and **MOD** functions in the **Conditional Formatting **rules to alternate the row color**.** The steps of this process are given below:

**📌**** Steps**

- Firstly, select cell
**E4**and write down**Zero (0)**into the cell.

`0`

- Then, press
**ENTER**.

- Then, select cell
**E5**and write down the following formula into the cell.

`=IF(B5=B4,E4,E4+1)`

**Formula Breakdown**

**IF(B5=B4, E4, E4+1):**

**The IF function**checks the value of cell

**B5**with

**B4**. If both values match each other, the function returns the value of cell

**E4**. Otherwise, it will add

**1**with the value of cell

**E4**and return that.

- Simply, press the
**ENTER**key.

- Now, select the range of cells
**B5:E14**. - After that, go to the
**Home**tab - Then, click on the
**drop-down arrow**of the**Conditional Formatting**on the**Styles**group. - Later, select the
**New Rules**option from the drop-down list.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Then, select
**Use a formula to determine which cells to format**under the section of**Select a Rule Type**. - Afterward, write down the following formula into the empty box below
**Format values where the formula is true**text.

`=AND(LEN($B5)>0,MOD($E5,2)=0)`

**Formula Breakdown**

**LEN($B5):**The

**LEN**function counts the length of the cell value. In this case, the value is

**5**.

**MOD($E5,2):**This function divides the value of cell

**E5**by

**2**and shows the value of the remainder. Here, the result is

**1**.

**AND(LEN($B5)>0, MOD($E5,2)=0):**In this formula, the

**AND**function checks whether the value of the

**LEN**function is greater than

**5**and the result of the

**MOD**function is equal to

**0**. If both logics are

**True**, the row will show our selected color. Otherwise, it shows as usual.

- Then, click on the
**Format**option.

- Another dialog box called
**Format Cells**will appear. - In the
**Fill**tab, choose a color according to your desire to make the rows distinguishable. We choose**Green, Accent 6, Lighter 60%**color. - In the end, click
**OK**.

- Again, click
**OK**to close the**New Formatting Rule**dialog box.

- In this way, you can give alternate row colors based on the cell value in Excel.

### 8. Applying ISODD Function to Color Alternate Row Based on Cell Value in Excel

In this procedure, we are going to use the **IF** and **SUM** functions first, to get the numerical grouping of our dataset. Then, we will apply **the ISODD function** in **Conditional Formatting** to alternate the row color. The procedure of this process is shown as follows:

**📌**** Steps**

- Firstly, select cell
**E4**and write down**Zero (0)**into the cell.

`0`

- Then, press
**ENTER**.

- Then, select cell
**E5**and write down the following formula into the cell.

`=IF(B4=B5,E4,SUM(E4,1))`

**Formula Breakdown**

**SUM(E4,1):**

**The SUM function**will add

**1**with the value of cell

**E4**. For this cell, the function returns

**1**.

**IF(B4=B5,E4,SUM(E4,1)):**The

**IF**function checks the value of cell

**B5**with

**B4**. If both values match each other, the function returns the value of cell

**E4**. On the other hand, if the logic is

**False**, it returns the result of the

**SUM**function.

- Simply, press the
**ENTER**key.

- Now, select the range of cells
**B5:E14**. - After that, go to the
**Home**tab - Then, click on the
**drop-down arrow**of the**Conditional Formatting**on the**Styles**group. - Later, select the
**New Rules**option from the drop-down list.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Then, select
**Use a formula to determine which cells to format**under the section of**Select a Rule Type**. - Afterward, write down the following formula into the empty box below
**Format values where the formula is true**text.

`=ISODD($E5)`

Then, **ISODD** will determine if the corresponding cell value is odd or not, and if it is odd then it will return **TRUE**. If the logic is **TRUE** the row will show our selected color. Otherwise, it shows as usual.

- Then, click on the
**Format**option.

- Another dialog box called
**Format Cells**will appear. - In the
**Fill**tab, choose a color according to your desire to make the rows distinguishable. We choose**Green, Accent 6, Lighter 60%**color. - In the end, click
**OK**.

- Again, click
**OK**to close the**New Formatting Rule**dialog box.

- In this way, you can give alternate row colors based on the cell value in Excel.

### 9. Linking ISEVEN Function with Filter Option

In this section, we are going to use **the ISEVEN function** and the **Filter** option to alternate the row colors based on values. Let’s explore the method step by step.

**📌**** Steps**

- For convenience, we have added the
**Helper**column.

- Secondly, select cell
**E5**and write down the following formula.

`=ISEVEN(ROW())`

The** ROW** function returns the row number. Then, **ISEVEN** will determine if the corresponding row number is even or not, and if it is even then it will return **TRUE** otherwise **FALSE**.

- Simply, press the
**ENTER**key.

- At this moment, select the dataset. In this case, we selected cells in the
**B4:E14**range. - Then, go to the
**Home**tab. - After that, select the
**Sort & Filter**drop-down on the**Editing**group. - Later, choose the
**Filter**option.

- As a result, we will get the filtered table.

- At present, click on the dropdown symbol of the
**Helper**column.

- Then, uncheck the
**TRUE**option to hide it and press**OK**.

- As a result, the rows with
**TRUE**will be hidden.

- Next, select the unhidden rows in the dataset.
- Now, go to the
**Home**tab. - Then, select the
**Fill Color**drop-down on the**Font**group. - Later, choose
**Green, Accent 6, Lighter 60%**from the available colors.

- Finally, we are getting the rows highlighted.
- Again, click on the dropdown symbol of the
**Helper**column.

- At this moment, select the option
**Clear Filter From “Helper”**. - Later, click on the
**OK**button.

- Eventually, we are getting our desired alternate row colors based on the cell values.

- Again, select the whole dataset including the header row.
- Afterward, go to the
**Home**tab. - Next, select the
**Sort & Filter**drop-down on the**Editing**group. - Lastly, unclick the
**Filter**option.

So, now we are having our normal data range having alternate row colors.

### 10. Employing VBA Code to Color Alternate Row Based on Cell Value in Excel

Employing the **VBA **code is always an amazing alternative. Follow the steps below to be able to solve the problem in this way.

**📌**** Steps**

- Firstly, press the
**ALT+F11**key.

- Suddenly, the
**Microsoft Visual Basic for Applications**window will open. - Then, go to the
**Insert**tab. - After that, select
**Module**from the options.

- It opens the code module where you need to paste the code below.

```
Sub Alternate_Row_Color()
Dim y As Integer
For y = 1 To Selection.Rows.Count
If y Mod 2 = 0 Then
Selection.Rows(y).Interior.Color = RGB(198, 224, 180)
End If
Next
End Sub
```

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

- Now, return to the sheet and select the dataset.
- Then, go to the
**Developer**Tab. - Next, select the
**Macros**Option on the**Code**group.

- Suddenly, the
**Macro**wizard will open up. - Afterward, select the macro
**Alternate_Row_Color**(that we have created in the previous step). - Finally, press the
**Run**button.

Finally, you can fill the alternate 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 5**) and the following row as **Row 2** and so it is colored because it is even ( actually it was **Row 6**).

## Practice Section

For doing practice by yourself we have provided a practice section like below in the last sheet named *Practice Section* on the workbook. Please do it by yourself.

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice yourself.

## Conclusion

This article provides easy and brief solutions to color alternate rows based on the cell values in Excel. Don’t forget to download the *Practice* file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

## Related Articles

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

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