# Conditional Formatting

#### MarkyBoy

##### New Member
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Lewiy

##### Well-known Member
For the range A1:A20 use the following conditional format formula on A1 and then copy&paste formats to the rest of the range:
Code:
=COUNTIF(\$A\$1:\$A\$20,A1)>2

#### barry houdini

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP, Moderator
Same solution:

Excel Workbook
A
1Tim
2Tom
3Ted
4Tim
5Ted
6Joe
7Tim
8Tom
9Ted
10
Conditional Format
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =COUNTIF(A:A,A1)>2Abc
A21. / Formula is =COUNTIF(A:A,A2)>2Abc
A31. / Formula is =COUNTIF(A:A,A3)>2Abc
A41. / Formula is =COUNTIF(A:A,A4)>2Abc
A51. / Formula is =COUNTIF(A:A,A5)>2Abc
A61. / Formula is =COUNTIF(A:A,A6)>2Abc
A71. / Formula is =COUNTIF(A:A,A7)>2Abc
A81. / Formula is =COUNTIF(A:A,A8)>2Abc
A91. / Formula is =COUNTIF(A:A,A9)>2Abc
A101. / Formula is =COUNTIF(A:A,A10)>2Abc

#### Lewiy

##### Well-known Member
Doesn’t matter, it will still do the same thing whether it’s numbers or text.

#### MarkyBoy

##### New Member
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

anyone?

#### Lewiy

##### Well-known Member
Copy the formatted cell and paste special>formats to the rest of the range.

#### MarkyBoy

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

Replies
9
Views
437
Replies
1
Views
888
Replies
0
Views
490
Replies
3
Views
425
Replies
0
Views
1K

1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

### 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.

### Which adblocker are you using?

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

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