Conditional Formatting - Colour Scale Based on Count

[marka87uk]

What would be the best method to add a colour scale to a set of data with a colour scale to indicate how often the value appears in the dataset?

Thanks

 

[AliGW]

You would need a helper column somewhere to do the count, and then use this to determine the colour scale output.

 

[marka87uk]

Thanks. I've added a helper section below to count the number of times the values above appear.

I thought I could use the two colour scale, specifying the type as formula and then referencing the helper cells, but I get the error "You cannot use relative references in Conditional Formatting criteria for colour scales, data bars, and icon sets."

It seems like I'm out of luck unless I use a macro to copy the cell colours across on every update?

 

[marka87uk]

In the end I settled for making my own colour scale - it's a bit time consuming but at least now I can use the count of cell values without the need for helper cells.

For reference, knowing the maximum count would be around 10, I made a new rule for each value (B1:K31 contain the cells I wanted to use the conditional formatting on) like so:

=COUNTIF($B$1:$K$31,B1)=1 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=2 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=3 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=4 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=5 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=6 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=7 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=8 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=9 - Applies to =$B$1:$K$31
=COUNTIF($B$1:$K$31,B1)=10 - Applies to =$B$1:$K$31

I then used a website to generate the RGB values for a colour scale and applied the corresponding gradient colour to each rule (RGB Color Gradient Maker).