Dataset Overview
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:
- Select cell E4 and enter “0.”
- Press Enter.
- Select cell E5 and enter the following formula:
=IF(B5=B4,E4,E4+1)
- Press Enter.
- 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 and select the 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:
=AND(LEN($B5)>0,MOD($E5,2)=0)
- 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.
- Click OK to close the New Formatting Rule dialog box.
- You’ll see the alternating groups of the datasets now shows the selected color.
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:
- Select cell E4 and enter “0.”
- Press Enter.
- Select cell E5 and enter the following formula:
=MOD(IF(ROW()=2,0,IF(B5=B4,E4,E4+1)),2)
- Press Enter.
- 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 and select the 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:
=$E5=1
- Click the Format button.
- In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) for the alternating rows.
- Click OK.
- Click OK to close the New Formatting Rule dialog box.
- The alternating groups of the datasets will now display the selected color as expected.
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.
- Select cell E5 and enter the following formula:
=MOD(IF(B5=B4,E4,E4+1),2)
- Press Enter.
- 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 and select the 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:
=$E5=1
- 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.
- Click OK to close the New Formatting Rule dialog box.
- You’ll see the alternating groups of the datasets now shows the selected color.
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.
- Select cell E5 and enter the following formula:
=IF(B4=B5,E4,SUM(E4,1))
- Press Enter.
- 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 and select the 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:
=ISODD($E5)
- Click the Format button.
- In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) for the alternating rows.
- Click OK.
- Click OK to close the New Formatting Rule dialog box.
- The alternating groups of the datasets will now display the selected color as expected.
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.
- Select cell E5 and enter the following formula:
=IF(B4=B5,E4,SUM(E4,1))
- Press Enter.
- 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 and select the 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.
- In the Format Cells dialog box, choose a color (e.g., Green, Accent 6, Lighter 60%) for the alternating rows.
- Click OK.
- Click OK to close the New Formatting Rule dialog box.
- The alternating groups of the datasets will now display the selected color as expected.
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 and select the 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(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.
- Cick OK to close the New Formatting Rule dialog box.
- You’ll get the alternating groups of the datasets showing the selected color.
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 enter 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
- How to Color Alternate Row Based on Cell Value in Excel
- How to Alternate Row Colors in Excel Without Table
<< Go Back to Highlight Row | Highlight in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
