Forum Discussion
Excel table
Using that helper column idea you can also use a built-in gradient coloring to color those cells so all Joe Smiths would have same index so have the same color but other people/groups would have slightly different color. That said it would be at most a 3 color gradient I think so if you have a 100 different groups then numbers 99 and 100 will have very similar colors (different but very similar). and it would be only that cell/column not the whole row.
As for the 'banding' idea where every other group has a color you can use a custom formula like this:
=ISEVEN(ROWS(UNIQUE($A$1:$A1)))
where column A is the column with the unique (and sorted) value. Various variation of this can be used but basically looks like this:
I was hoping that the table would have an option for this. Since my data list has about 1000 rows.
thanks
If you have few hundred groups and would like unique color for each that doesn't work. Alternatively you may add helper index column for each group (how to do that's another story) and change color each time when index is changed from odd to even. Thus you have only two rules with two colors which separate one group from another.
Using that helper column idea you can also use a built-in gradient coloring to color those cells so all Joe Smiths would have same index so have the same color but other people/groups would have slightly different color. That said it would be at most a 3 color gradient I think so if you have a 100 different groups then numbers 99 and 100 will have very similar colors (different but very similar). and it would be only that cell/column not the whole row.
As for the 'banding' idea where every other group has a color you can use a custom formula like this:
=ISEVEN(ROWS(UNIQUE($A$1:$A1)))
where column A is the column with the unique (and sorted) value. Various variation of this can be used but basically looks like this:
