Forum Discussion

BaltaD's avatar
BaltaD
Copper Contributor
Mar 23, 2026

Excel Tables and Conditional Formatting

I am trying to format my data by either creating a table or conditional formatting.

The data has a common Team ID number with different amount of players.

If I create a table, this is what I get:

What I really want is to group the team numbers together so it would look something like this:

I've tried everything I can think of but the only thing I came up with is this formula 

=AND(LEN($B3)>0,MOD($Q3,2)=0), but this means that I have to drag the formula every time new teams join.

 

Any suggestions?

excel

3 Replies

  • BaltaD's avatar
    BaltaD
    Copper Contributor
    Mar 23, 2026

    Thank You IlirU and Riny.

    I tried both solutions and they both worked.  The only difference is that with =MOD(XMATCH($B3, UNIQUE(TOCOL($B$3:$B$32, 1))), 2) = 1 I had to change the $B$32 to something like $B$1000 just in case I add more teams.

    I really appreciate your help!

    Balta

  • IlirU's avatar
    IlirU
    Iron Contributor
    Mar 23, 2026

    Hi BaltaD​,

    Select range A3:G32 and in CF apply below formula:

    =MOD(XMATCH($B3, UNIQUE(TOCOL($B$3:$B$32, 1))), 2) = 1

    Format with the color you want.

    HTH

    IlirU

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 23, 2026

    BaltaD​ 

    Worked on something like this a long time ago and can't remember if I made this up myself or found it somewhere on-line. Anyway, I now found it in my saved example files and share it here.

    The CF formula I would use to color the rows based in the team number in column B is this:

    =MOD(SUM(IF(FREQUENCY($B$2:$B2,$B$2:$B2),1)),2)

    Can't upload the file. The system is a bit screwed lately. Therefore just a picture.