How to highlight different numbers on a list with different colors

[Excel Peppy]

Hi Chandoo friends,

I have a list of numbers e.g.
1
1
1
3
3
3
5
5
5
5
....

I would like to highlight all 1 in red, all 3 in blue and all 5 in green. Do you guys know what is the easiest and quickest way to do that? My goal is to differentiate each number chunk so that they are easier to find and read.

Thank you and have a nice weekend.

 

[Smallman]

Hi Excel Peppy

The best way to do this sort of thing IMO is with conditional fomatting. Here is an example of the CF formatting

=if(A2=1,1,0) ....... Red
=if(A2=3,1,0) ....... Blue
=if(A2=5,1,0) ....... Green

File attached to show workings.

Take care

Smallman

 

[Somendra Misra]

Hi Excel Peppy,

I completely agree with @Smallman that CF is a way to achieve it, but the problem with this method that I see is if you have 100 or 1000 number chunks than you have to apply that many rules, which is of-course tedious and will make your worksheet slow as CF are volatile in nature.

May be if you can explain the whole purpose of what you are trying to achive we can come up with a more friendly solution.

Such as apply filters on them that way you can filter the number which you want to study on.

Regards,

 

[NARAYANK991]

Hi ,

If you just wish to highlight chunks of data for easier readability , it is not necessary to use different colours ; just 2 colours are enough. If you have , say 6 colours , not only is it cumbersome to implement , it is going to be hard on the eyes , especially if every row is going to be a different colour !

Use just 2 light colors to highlight the chunks , alternating between them with every change of data. All you will need is 1 rule.

The second color can come from a general formatting of the entire data range.

Otherwise , going by what you have mentioned about finding data , you can have a data validation dropdown to select which values you want to highlight , and then highlight all rows which have that data. Again , just 1 rule.

See the attached file.

Narayan

 

[Excel Peppy]

Hi Chandoo Friends,

Thanks for the quick reply. My purpose is to get the data easier to read, so I agree with Narayan that 2 colors are enough.

By applying the rules Narayan provided, I am able to differentiate different number chucks in separate colors:

1
1
1---all red
2
2
2---all blue
3
3
3---all red
4
4
4---all blue

Then these data are easier to read.

Thank everyone helping in this thread. Hope you all have a wonderful Sunday.

See you soon.

Excel Peppy