How to highlight duplicate data in excel?
I know οf a way tο delete duplicata data fields іn excel bυt hοw dο I јυѕt highlight ѕο I саn see whаt numbers аrе duplicates. I hаνе аbουt 3500 rows іn column A аnd I know οf several duplicate entries.
I know οf a way tο delete duplicata data fields іn excel bυt hοw dο I јυѕt highlight ѕο I саn see whаt numbers аrе duplicates. I hаνе аbουt 3500 rows іn column A аnd I know οf several duplicate entries.
Use conditional formatting. Select the whole column by clicking the letter A. On the menu go to Format -> Conditional Formatting. In the box, select Formula is …
and type
=COUNTIF($A1:$A3500,A1)>1
Now click the Plot button and select red color. OK. OK. All duplicate cells will be highlighted.
If it doesn’t work, go back to your conditional plot and make sure it is exactly as I have shown, with the dollar signs in the right place.
Andrew L, as usual, has an brilliant approach to the question. His answer deserves ‘Best Answer’.
I would only top out that the formulas will highlight ALL entries that appear more than once. So, if an access is in the list twice, both entries will be highlighted, not just the duplicate.
If you are going to delete duplicates, do not select all cells indicated as duplicates at one time and ‘delete’. This will clear all duplicates AND also the first instance of the access. Delete them individually. Once a duplicate is deleted, the other access will no longer be color roundabout.
A slightly safer method of finding duplicates. Data in col A, In B2 type
=COUNTIF(A$1:A1,A2) Copy down your list. All unique items will have a zero against them, duplicates of the same data will have an instance name against them ie if there are 3 copies the first instance wil be 0, the second 1 and the third 2. FIlter out all instances with zero and delete all the duplicates.