# Conditional Formatting with formula

##### New Member
I am trying to establish a formula (im thinking its best fit is in conditional formatting) that will cause Excel to higlight the values which occur most often within a column (or better yet -variable colors from red to yellow red being the most often and yellow being the least often).

So for example:

Row ___________
1 | Alan |
2 | Alan |
3 | Alan |
4 | Alan |
5 | Alan |
6 | Bob |
7 | Bob |
8 | Bob |
9 | Craig |
10 | Craig |

Since "Alan" occurs 5 times, it would hilight Alan in red,
Bob (3 times) - Orange
Craig (twice) - Yellow

This would also update as i add to the rows, so if, overtime, i add Craig 5 more times in rows 11-15 - Craig would end up being Red.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Matty

##### Well-known Member
What should happen in the case of a draw, i.e. two names that occur the same number of times?

Matty

#### abmati

##### Board Regular
For Range A1:A1000, use the following formula in A1 cell for conditional formatting.
Then copy A1 to A1000 with Paste Special and Format.
=COUNTIF(\$A\$1:A1,A1)+COUNTIF(A2:\$A\$1000,A1)=5 --> Red
=COUNTIF(\$A\$1:A1,A1)+COUNTIF(A2:\$A\$1000,A1)=3 --> Orange
=COUNTIF(\$A\$1:A1,A1)+COUNTIF(A2:\$A\$1000,A1)=2 --> Yellow

Regards,

##### New Member
thanks abmati but the formula needs to be dynamic, so as the numbers increase i would like the formula to continue to capture which is the highest, second highest and third highest in count. I believe your formul would only work for if it equals the static number (5,3,2).

Conditional formatting can currently capture the highest numbers but it would seem logical that they would also build in something to allow for the highest number of occurances as well.

Matty, The draw would result in both numbers being the same color, but again, the formula would essentially rear like, highest count in column A is formatted as red, the second highest count in column A is formatted as orange, and the 3rd highest count in column A is formatted as orange.

The preset conditional formatting allows for standard deviations from the average which gets kind of close to what im looking for but i cant get the second hightest and third highest to work consistantly.

#### Matty

##### Well-known Member
Using a Conditional Formatting formula, try...

For max:

Code:
``=COUNTIF(A\$1:A\$10,A1)=MAX(COUNTIF(A\$1:A\$10,A\$1:A\$10))``

With red fill.

For min:

Code:
``=COUNTIF(A\$1:A\$10,A1)=MIN(COUNTIF(A\$1:A\$10,A\$1:A\$10))``

With yellow fill.

All other values should be in between, so these can be permanently filled with orange (the Conditional Formatting will then over-colour if a condition is met).

To make thing dynamic, use a dynamic Named Range. Plenty on this board about them if you do a search.

Matty

##### New Member
THANKS - your awesome!

I got the Max to work but the MIN wasnt working over the Orange. Ill try to keep playing with it and see if i can get it to over shadow but for now i can work with MAX....

I wish i could have found this without having to create a thread which may have already been covered, the only problem is that i would have never thought to search for Named Range and considering the nature of the problem (typically trying to solve an excel question quickly) its hard to spend the time necessary to find these answers.

Thanks again, a huge help!

#### Matty

##### Well-known Member
THANKS - your awesome!

I got the Max to work but the MIN wasnt working over the Orange. Ill try to keep playing with it and see if i can get it to over shadow but for now i can work with MAX....

I wish i could have found this without having to create a thread which may have already been covered, the only problem is that i would have never thought to search for Named Range and considering the nature of the problem (typically trying to solve an excel question quickly) its hard to spend the time necessary to find these answers.

Thanks again, a huge help!

Glad I could help. And let me know if you need any helps with the dynamic Named Ranges.

Matty

Replies
5
Views
487
Replies
5
Views
482
Replies
1
Views
593
Replies
2
Views
814
Replies
3
Views
1K

1,195,829
Messages
6,011,838
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back