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

To demonstrate these methods, let’s consider a dataset of 10 fruit consumers. The fruit item names are in column B, and the purchaser names and quantities are in columns C and D, respectively. Our dataset spans the range of cells B5:D14. In each method, we’ll apply conditional formatting to change the cell colors.


Method 1 – Combining AND with LEN and MOD Functions:

Steps:

  • Start by selecting cell E4 and entering “0.”
  • Press Enter.

Combining AND with LEN and MOD Functions to Alternate Row Color Based on Group

  • Next, select cell E5 and enter the following formula:

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

  • Press Enter.

Combining AND with LEN and MOD Functions to Alternate Row Color Based on Group

  • Double-click the Fill Handle icon to copy the formula down to cell E14.

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

Combining AND with LEN and MOD Functions to Alternate Row Color Based on Group

  • In the New Formatting Rule dialog box, enter the following formula under Format values where this formula is true:

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

  • Click the Format button.

Combining AND with LEN and MOD Functions to Alternate Row Color Based on Group

  • In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) to make the rows distinguishable.
  • Click OK.

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

Combining AND with LEN and MOD Functions to Alternate Row Color Based on Group

  • You’ll see the alternating group of the dataset now shows the color.

Combining AND with LEN and MOD Functions to Alternate Row Color Based on Group

Breakdown of the Formula

We are breaking down the formula in cell 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.


Method 2 – Applying MOD, IF and ROW Functions:

Steps:

  • Start by selecting cell E4 and entering “0.”
  • Press Enter.

Applying MOD, IF and ROW Functions to Alternate Row Color Based on Group

  • Next, select cell E5 and enter the following formula:

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

  • Press Enter.

Applying MOD, IF and ROW Functions to Alternate Row Color Based on Group

  • Double-click the Fill Handle icon to copy the formula down to cell E14.

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

Applying MOD, IF and ROW Functions to Alternate Row Color Based on Group

  • In the New Formatting Rule dialog box, enter the following formula under Format values where this formula is true:

=$E5=1

  • Click the Format button.

Applying MOD, IF and ROW Functions to Alternate Row Color Based on Group

  • In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) for the alternating rows.
  • Click OK.

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

Applying MOD, IF and ROW Functions to Alternate Row Color Based on Group

  • The alternating group of the dataset will now display the color as expected.

Applying MOD, IF and ROW Functions to Alternate Row Color Based on Group

Breakdown of the Formula

We are breaking down the formula in cell 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.


Method 3 – Combining MOD and IF Functions:

Steps:

  • Select cell E4 and enter “0.”
  • Press Enter.

Combining MOD and IF Functions to Alternate Row Color Based on Group

  • Select cell E5 and enter the following formula:

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

  • Press Enter.

Combining MOD and IF Functions to Alternate Row Color Based on Group

  • Double-click the Fill Handle icon to copy the formula down to cell E14.

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

Combining MOD and IF Functions to Alternate Row Color Based on Group

  • In the New Formatting Rule”dialog box, enter the following formula under Format values where this formula is true:

=$E5=1

  • Click the Format button.

Combining MOD and IF Functions to Alternate Row Color Based on Group

  • In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) to make the rows distinguishable.
  • Click OK.

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

Combining MOD and IF Functions to Alternate Row Color Based on Group

  • You’ll see the alternating group of the dataset now shows the color.

Combining MOD and IF Functions to Alternate Row Color Based on Group

Breakdown of the Formula

We are breaking down the formula in cell 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.


Method 4 – Applying ISODD Function:

Steps:

  • Select cell E4 and enter “0.”
  • Press Enter.

Applying ISODD Function to Alternate Row Color Based on Group

  • Select cell E5 and enter the following formula:

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

  • Press Enter.

Applying ISODD Function to Alternate Row Color Based on Group

  • Double-click the Fill Handle icon to copy the formula down to cell E14.

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

Applying ISODD Function to Alternate Row Color Based on Group

  • In the New Formatting Rule dialog box, enter the following formula under Format values where this formula is true:

=ISODD($E5)

  • Click the Format button.

Applying ISODD Function to Alternate Row Color Based on Group

  • In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) for the alternating rows.
  • Click OK.

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

Applying ISODD Function to Alternate Row Color Based on Group

  • The alternating group of the dataset will now display the color as expected.

Applying ISODD Function to Alternate Row Color Based on Group

Breakdown of the Formula

We are breaking down the formula in cell 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.


Method 5 – Using ISEVEN Function:

Steps:

  • Select cell E4 and enter “0.”
  • Press Enter.

Applying ISEVEN Function to Alternate Row Color Based on Group

  • Select cell E5 and enter the following formula:

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

  • Press Enter.

Applying ISEVEN Function to Alternate Row Color Based on Group

  • Double-click the Fill Handle icon to copy the formula down to cell E14.

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

Applying ISEVEN Function to Alternate Row Color Based on Group

  • In the Home tab, click the drop-down arrow of the Conditional Formatting > New Rules option from the Styles group.
  • In the New Formatting Rule dialog box, enter the following formula under Format values where this formula is true:

=ISEVEN($E5)

  • Click the Format button.

Applying ISEVEN Function to Alternate Row Color Based on Group

  • In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) for the alternating rows.
  • Click OK.

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

Applying ISEVEN Function to Alternate Row Color Based on Group

  • The alternating group of the dataset will now display the color as expected.

Applying ISEVEN Function to Alternate Row Color Based on Group

Breakdown of the Formula

We are breaking down the formula in cell 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.


Method 6 – Utilizing ISEVEN, CELLING and ROW Functions:

Steps:

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

Utilizing ISEVEN, CELLING and ROW Functions to Alternate Row Color Based on Group

  • In the New Formatting Rule dialog box, enter the following formula under Format values where this formula is true:

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

  • Click the Format button.

  • In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) to make the rows distinguishable.
  • Click OK to close the Format Cells dialog box.

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

  • You’ll get the alternating group of the dataset showing the color.

Utilizing ISEVEN, CELLING and ROW Functions to Alternate Row Color Based on Group

Breakdown of the Formula

We are breaking down the formula for 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

Keep in mind that we input the formula directly in the conditional formatting rule box without performing any numerical grouping. So, ensure that the number of group entities remains equal for accurate results.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Alternating Row ColorsHighlight Row | Highlight in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo