Conditional Formatting

[MarkyBoy]

Hi there, this is my first post so sorry if its been asked before but I couldnt find anything anywhere else.

I am trying to conditionally format cells if any value in a range of cells appears more than twice.

so I have a list of values in a column and I want to highlight any where the values is listed 3 or more times

I have tried using countif but have been unsuccessful.

Please help

Thanks in advance

 

[Lewiy]

For the range A1:A20 use the following conditional format formula on A1 and then copy&paste formats to the rest of the range:

=COUNTIF($A$1:$A$20,A1)>2

 

[barry houdini]

Assuming your column is A, select that column and use Coditional formatting with "formula is" option

=COUNTIF(A:A,A1)>2

choose required format > OK

 

[MarkyBoy]

thanks for the swift responses, however I think I have missed a major point, the values in question are words not numbers so maybe saying value was a bad choice of words.

this is why I am having the problem

I want to highlight the cells if any word is repeated in a column more than twice

sorry for confusion

 

[Peter_SSs]

Same solution:

Excel Workbook
	A
1	Tim
2	Tom
3	Ted
4	Tim
5	Ted
6	Joe
7	Tim
8	Tom
9	Ted
10
Conditional Format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =COUNTIF(A:A,A1)>2	Abc
A2	1. / Formula is =COUNTIF(A:A,A2)>2	Abc
A3	1. / Formula is =COUNTIF(A:A,A3)>2	Abc
A4	1. / Formula is =COUNTIF(A:A,A4)>2	Abc
A5	1. / Formula is =COUNTIF(A:A,A5)>2	Abc
A6	1. / Formula is =COUNTIF(A:A,A6)>2	Abc
A7	1. / Formula is =COUNTIF(A:A,A7)>2	Abc
A8	1. / Formula is =COUNTIF(A:A,A8)>2	Abc
A9	1. / Formula is =COUNTIF(A:A,A9)>2	Abc
A10	1. / Formula is =COUNTIF(A:A,A10)>2	Abc

 

[Lewiy]

Doesn’t matter, it will still do the same thing whether it’s numbers or text.

 

[MarkyBoy]

Many thanks, I now see that working although I need to do this for every cell individually, is there no way to format the whole range in one go?

also another little niggle following this, the words that it is lokking up are a result from a formula in the cell, normally this cell shows as blank as the query is returning no result, however this means that if the field in question has no entry then it looks up the column and sees other cells with no result and formats the cells which I do not want, do I want it to ignore blank cells or do I need to make it ignore cells with no current results?

sorry for such nuisance

 

[MarkyBoy]

anyone?

 

[Lewiy]

Copy the formatted cell and paste special>formats to the rest of the range.

 

[MarkyBoy]

Lewiy, that's great, any idea on how to stop if formatting when the cells are empty?