Search code examples
excelcolorsrepeat

Colour repeated rows by groups in Excel

I have a table like this one:

Column 1
111
111
100
100
100
XX6
XX6
L1
PPP
PPP

And I would like to colour it in just two colours (colour 1 and colour 2) in a way that the repeated rows are easy two distinguish, so for example the rows with 111 would be colored in colour 1, 100 in colour 2, colour XX6 in colour 1, L1 in colour 2 and so on.

There are more columns in the real table, but this one is the key to make this.

Is there a way to do that in Excel?

Solution

  • Conditional Formatting By Group With Two Colors

    Here's how you can conditionally format by groups

    • In cell B1 (same row as the header) enter Helper,

    • In cell B2 enter the below formula,

    =IF(A2=A1,B1,N(B1)+1)

    • Select the data range, as shown in image below A2:A11,

    • Press ALT H + L + N to show the New Formatting Rule dialog box

    • Now enter a new rule formula — use a formula to determine which cells to format — with the below formula

    =AND(LEN($A2)>0,MOD($B2,2)=1)

    • Click the Format button --> Fill Tab --> Choose desired color --> Press Ok.

    Repeat from the Bullet Point 3 for the Color 2 and enter the below formula

    =AND(LEN($A2)>0,MOD($B2,2)=0)

    Side Note: Hide the column (column B) where you have the formula.

    CONDITIONAL_FORMATTING