How to Remove Duplicate Values From Excel Database.

Contact Us

Bhandup (W): Dreams Mall, Opp BMC Market, Nr Bhandup Station, Mumabi 400078.
Thane (W): G-03, SAI Sagar App, Beside Sai Babab Mandir, Kalher Thane.

+91 9870419968

info@advancedexcelcourse.com






How to Remove Duplicate Values From Excel Database.


In Excel there are various ways to remove duplicate values, let's have a look at some of those with examples:

Consider below table as a database for all the methods explained below

A B C
1 Id Stock Price
2 1008 Copper 68K
3 1006 Gold 60K
4 1003 Silver 51K
5 1010 Steel 62K
6 1002 Bronze 59K
7 1012 Brass 59K
8 1004 Pewter 65K
9 1008 Copper 68K
10 1009 Mercury 54K
11 1012 Brass 59K
12 1008 Copper 68K
13 1008 Copper 68K
14 1012 Brass 59K
15 1015 Aluminium 54K
16 1013 Tin 63K
17 1014 Sodium 65K

Remove Duplicate with Excel Built in option

Method 1:

  1. Select the data from Cell A1:C17
  2. Click on Remove Duplicates from Data Tab
  3. Uncheck Stock and Price and hit ok
  4. You will find that excel had removed 5 duplicate entries

Method 2:

  1. Click on Advanced Filter form Data Tab
  2. From Advanced Filter window select copy to another location
  3. In the list range select the range from A1:C17
  4. In a Criteria range select the range from A1:A17
  5. In a Copy to range select cell E1
  6. Check the Unique records only option and hit ok
In the method 1 & 2 given above, we are able to remove duplicate entries, but are not able to know which those entries were.

Remove Duplicate Using Excel Functions

Method 3:

  1. In cell D1 write Duplicate
  2. In cell D2 write the formula as =COUNTIF(A$2:A17,A2) and then drag the same till D17
  3. Now Select the entire data from cell A1:D17 and click on filter from a Data tab
  4. From cell D1 filter, uncheck all the options apart from 1 to get the unique records and vica-versa for other records.

Method 4:

  1. Select the data from Cell A1:C17
  2. Click on Sort option from Data tab
  3. Under Sort by option select ID , Under Sort on option select Values, and select Smallest to Largest under Order
  4. In cell D1 write Duplicate
  5. In cell D2 write the formula as =A2=A3 and then drag the same till D17
  6. Now Select the entire data from cell A1:D17 and click on filter from a Data tab
  7. From cell D1 filter False for unique records and True for other records.
In the method 3 & 4 given above, we are able to remove duplicate entries, and also able to know which those entries were.





  • facebook
  • google+
  • Youtube
  • skype
  • whatsapp
  • mobile
  • mail