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.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
6 Quick Ways to Alternate Row Color Based on Group in Excel
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.
Read More: How to Color Alternate Row for Merged Cells in Excel
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.
Read More: How to Color Alternate Row Based on Cell Value in Excel
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.
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.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!