When we work on a large datasheet, we need to alternate the row color to visualize our dataset better. We can do the color-altering task as a band or as a group. In this article, we will demonstrate 6 easy methods to alternate the row color based on the group in Excel. If you are also interested in it, download our practice workbook and follow us.

**Table of Contents**Expand

## How to Alternate Row Color Based on Group in Excel: 6 Ways

To demonstrate the approaches, we consider a dataset of **10** consumers of fruits. The name of the fruit items is in column **B**, and the name of the purchaser and the amount of food is in column **C** and **D**, respectively. So, we can say that our dataset is in the range of cells **B5:D14**. In each method, we have to apply **conditional formatting** to change the cell color.

### 1. Combining AND with 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:**

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

`0`

- Press
**Enter**.

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

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

- Similarly, press
**Enter**.

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

- After that, select the range of cells
**B5:E14**. - In the
**Home**tab, click on the**drop-down arrow**of the**Conditional Formatting > New Rules**option from the group**Styles**.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Now, 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)`

- 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. - Finally, click
**OK**.

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

- You will see within a second the alternating group of the dataset shows the color.

Thus, we can say that our formula worked perfectly, and we were able to alternate the row color based on the group in Excel.

**ðŸ”Ž Breakdown of the Formula**

We are breaking down the formula **E5**.

`ðŸ‘‰`

**LEN($B5)**: This 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 check 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.

### 2. Applying MOD, IF and ROW Functions

In this process, the **MOD**, **IF**, and **ROW** functions will help us to get the numerical grouping of our dataset. The steps of this approach are given as follows:

**ðŸ“Œ Steps:**

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

`0`

- Press
**Enter**.

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

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

- Again, press
**Enter**.

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

- Then, select the range of cells
**B5:E14**. - Afterward, in the
**Home**tab, click on the**drop-down arrow**of the**Conditional Formatting > New Rules**option from the group**Styles**.

- A small dialog box called
**New Formatting Rule**will appear. - Next, write down the following formula into the
**empty box**below**Format values where the formula is true**text.

`=$E5=1`

- After that, click on the
**Format**option.

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

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

- You will notice within a second the alternating group of the dataset shows the color.

So, we can say that our formula worked precisely, and we were able to alternate the row color based on the group in Excel.

**ðŸ”Ž Breakdown of the Formula**

We are breaking down the formula **E5**.

`ðŸ‘‰`

**ROW()**: This 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 check 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 function will divide the result of the **IF** function by **2** and show the value of the **remainder**.

### 3. Combining MOD and IF Functions

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

**ðŸ“Œ Steps:**

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

`0`

- Press
**Enter**.

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

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

- Similarly, press
**Enter**.

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

- Now, select the range of cells
**B5:E14**. - In the
**Home**tab, click on the**drop-down arrow**of the**Conditional Formatting > New Rules**option from the group**Styles**.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - 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.

- You will get the alternating group of the dataset showing the color.

Finally, we can say that our formula worked effectively, and we were able to alternate the row color based on the group in Excel.

**ðŸ”Ž Breakdown of the Formula**

We are breaking down the formula **E5**.

`ðŸ‘‰`

**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 function will divide the result of the **IF** function by **2** and show the value of the **remainder**.

### 4. Applying ISODD Function

In this procedure, we are going to use the **IF** and **SUM** functions first, to get the numerical grouping of our dataset. Then, we have to use **conditional formatting** using **the ISODD function** to alter 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`

- Press
**Enter**. - Now, select cell
**E5**and write down the following formula into the cell.

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

- Again, press
**Enter**.

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

- Then, select the range of cells
**B5:E14**. - In the
**Home**tab, click on the**drop-down arrow**of the**Conditional Formatting > New Rules**option from the group**Styles**.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Now, write down the following formula into the
**empty box**below**Format values where the formula is true**

`=ISODD($E5)`

- Click on the
**Format**option.

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

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

- You will see the alternating group of the dataset showing the color.

Finally, we can say that our formula worked successfully, and we were able to alternate the row color based on the group in Excel.

**ðŸ”Ž Breakdown of the Formula**

We are breaking down the formula **E5**.

`ðŸ‘‰`

**SUM(E4,1)**: The 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.

### 5. Using ISEVEN Function

Like the** ISODD** function, we can use **the ISEVEN function** to alter the row color. However, we have to use first use the **IF** and **SUM** functions, to get the numerical grouping of our dataset. The procedure of this case is shown below:

**ðŸ“Œ Steps:**

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

`0`

- Press
**Enter**.

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

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

- Again, press
**Enter**.

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

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

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Write down the following formula into the
**empty box**below**Format values where the formula is true**text.

`=ISEVEN($E5)`

- Afterward, 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. - Click
**OK**.

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

- You will notice the alternating group of the dataset showing the color.

At last, we can say that our formula worked perfectly, and we were able to alternate the row color based on the group in Excel.

**ðŸ”Ž Breakdown of the Formula**

We are breaking down the formula **E5**.

`ðŸ‘‰`

**SUM(E4,1)**: The 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.

### 6. Utilizing ISEVEN, CELLING and ROW Functions

In this case, we will use the **ISEVEN**, **CEILING****, **and **ROW** functions to alter the row color. The procedure of this method is shown below step-by-step:

**ðŸ“Œ Steps:**

- At first, select the range of cells
**B5:E14**. - Now, in the
**Home**tab, click on the**drop-down arrow**of the**Conditional Formatting > New Rules**option from the group**Styles**.

- As a result, a small dialog box called
**New Formatting Rule**will appear. - Then, write down the following formula into the
**empty box**below**Format values where the formula is true**text.

`=ISEVEN(CEILING(ROW()-4,2)/2)`

- After that, click on the
**Format**option.

- As a result, another dialog box called
**Format Cells**will appear. - Afterward, in the
**Fill**tab, choose a color according to your desire to make the rows distinguishable. We choose**Green, Accent 6, Lighter 60%**color. - Click
**OK**to close the**Format Cells**dialog box.

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

- You will get the alternating group of the dataset showing the color.

Finally, we can say that our formula worked effectively, and we were able to alternate the row color based on the group in Excel.

**ðŸ”Ž Breakdown of the Formula**

We are breaking down the formula row **5**.

`ðŸ‘‰`

**ROW()**: This function returns the row number. In this case, the value is **5**.

`ðŸ‘‰`

**CEILING(ROW()-4,2)**: This function deducts **4** from the result of the **ROW** function and then multiplies the value with **2**. Here, the result is **2**.

`ðŸ‘‰`

**ISEVEN(CEILING(ROW()-4,2)/2)**: In this formula, the **ISEVEN** function checks whether the division value of the result of the **CEILING** function and **2**. If the value is **even**, the row will show our selected color. Otherwise, it shows as usual.

**ðŸ’¬ Things You Should Know**

You may notice that we input the formula directly in the **conditional formatting rule box**. We donâ€™t perform any numerical grouping either. So, in this case, you have to keep the number of group entities *equal to each other*. Otherwise, the formula wonâ€™t work accurately.

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

## Conclusion

Thatâ€™s the end of this article. I hope that this article will be helpful for you and you will be able to alternate the row color based on the group in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Keep learning new methods and keep growing!

## Related Articles

- How to Color Alternate Row Based on Cell Value in Excel
- How to Color Alternate Row for Merged Cells in Excel
- How to Alternate Row Colors in Excel Without Table

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