How to Alternate Row Color Based on Group in Excel (6 Methods)

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.

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.

• Press Enter.

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

• 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.

• 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.

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.

• Press Enter.

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

• 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.

• 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.

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.

• Press Enter.

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

• 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.

• 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.

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.

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

• 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

• 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.

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.

• Press Enter.

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

• 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.

• 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.

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.

• 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.

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