highlights duplicate value

[ushaanu]

Hi all,

I have some data with me in excel I need highlights duplicates value but one condition like if data like

Geeta
Neena
Geeta

here Geeta is duplicate now I need only one geeta highlight.

Thanks
Anu

 

[Faseeh]

Goto conditional formatting and put this formula in conditional formatting menu with formula

=COUNTIF($A$1:$A1,A1)=1

assumes your data in col A

 

[teylyn]

Faseeh is on the right track, but the formula as posted will highlight all items that occur only once. To highlight duplicates you want

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

Step by step:

Assuming your data is in column A and starts in row 1 (adjust to your real scenario):
- select column A
- On the Home ribbon click Conditional Formatting > New Rule > Use a formula to determine...
- Copy the formula above into the formula box (and adjust to your column and first row number). The $ signs are important.
- Select a format and OK all dialogs.

cheers, teylyn

 

[Faseeh]

@teylyn

Setting value to greater then one will highlight all subsequent values greater then one. Please check.

 

[teylyn]

Yes, that's how I understand the question: Highlight duplicates, i.e. show me items that appear more than once. If an item appears three or four (or more) times, it will also be highlighted.

 

[ushaanu]

hi ,thanks for reply

it`s working thanks a lot

Anu

 

[ushaanu]

hi ,

can we do one other thing in this data if in cell A value is highlighted then in cell AZ value delete.

Thanks in advance

Anu