Highlighting Plot Numbers

New Contributor



I am trying to get Excel to recognise a column I have called Plot Numbers which sometimes have duplicate values.  I want excel to highlight in a different colour each plot number.  So for example there may be 3 people that live in Plot 3 so I want plot 3 to be highlighted in 1 colour.  Then say if just 1 person lives at Plot 12 I want plot 12 highlighted in a different colour.



2 Replies


For text values, you'd need to create a separate rule for each plot number. That's not too bad if you have a small number of different plot numbers, but it quickly becomes tedious if you have more of them.

If you could use numbers such as 1, 2, 3 etc. in the cells, you can use the custom number format

"Plot "0

to display 1 as "Plot 1" etc.

You can then create a conditional formatting rule with a 2-color scale or 3-color scale, as in the screenshots below:



@Hans Vogelaar 

Thank you.  This has worked nicely.