Often you may want to alternate row colors in Excel based on group values.
For example, you might want to alternate the row colors in the following dataset in Excel each time the value in the “Player” column changes:
The following step-by-step example shows how to do so in practice.
Step 1: Enter the Data
First, let’s enter the following dataset in Excel that shows the number of points scored by various basketball players:
Step 2: Create Helper Column
Next, we need to create a helper column that assigns numerical values to each unique name in the “Player” column.
First, type the value 0 into cell C1.
Then type the following formula into cell C2:
=IF(A2=A1,C1,C1+1)
We can then click and drag this formula down to each remaining cell in column C:
Step 3: Alternate Row Colors Based on Group
Next, highlight the cells in the range A2:C15, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =MOD($C2,2)=0 in the box, then click the Format button and choose a fill color to use.
Once we press OK, the row colors in the range A2:C15 will alternate each time the value in the “Player” column changes:
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Apply Conditional Formatting if Cell Contains Text
Excel: Apply Conditional Formatting with Multiple Conditions
Excel: Apply Conditional Formatting if Between Two Values
