How to delete duplicates in excel

How to Find and Remove Duplicate in Excel with these 3 Simple Ways?

In Microsoft Office Excel 2007, Excel 2010, Excel 2013, Excel 2016, you have several ways to filter for unique values or find duplicates in Excel.

Now, you will learn how to find & remove duplicate values in excel.

  1. Use the Remove Duplicates command in the Data Tools group on the Data
  2. To filter for unique values, use the Advanced Filter command in the Sort & Filter group on the Data
  3. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home

➡ Steps on How to Remove Duplicates Or Same Value in Excel:

  1. Click any single cell inside the data set.

Select the data, Remove duplicate in excel

  1. On the Data tab, click Remove Duplicates.

how to remove duplicate

  1. The following dialog box appears. Click On all checkboxes and click OK.

column-selection-in-remove-duplicate

Result: Excel removes all identical rows (red) i.e it removes all duplicate data from duplicate rows & column.

Image-4-Effect-of-remove-duplicate

To remove rows with the same values in certain columns in excel, execute the following steps:

  1. For example, remove rows with the same Airport Name. Select any single cell inside the data set.

Image-5-Remove-Duplicate

  1. Check Airport Name and click OK.

Image-6-Remove-Duplicate

➡ Steps on how to do Advanced Filter in Excel:

  1. Select the range of cells or select the column.
  2. On the Data tab, in the Sort & Filter group, click Advanced.

Advanced-Filter in Excel

  1. In the Advanced Filter dialog box, do one of the following:
  2. Click Copy to another location.
  3. In the Copy to box, enter a cell reference.
  4. Select the Unique records only check box, and click OK.

Image-8-Advance-Filter

The unique values from the selected range are copied to the new location.

This was the complete step by step tutorial for doing Advanced Filter in Excel

➡ Steps to Conditional Formatting in Excel:

This function is used to find the duplicate values in excel and also further may remove it by using filter option.

  1. Select the range of cells you wish to test.
  2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, and Duplicate Values.

conditional-formatting-in-excel

  1. Select a formatting style and click OK.
  2. Excel highlights the duplicate names.

Image-10-Duplicate-Value

As you can see, Excel highlights duplicates, triplicates, etc.

Execute the following steps to highlight triplicates only.

  1. First, clear the previous conditional formatting rule.

Image-11-Clear-Confitional-Formatting

  1. Select the range of cells you wish to test.
  2. Select “Use a formula to determine which cells to format”.
  3. Enter the formula “=COUNTIF(A:A,A5)=3”.
  4. Select a formatting style and click OK.
  5. Excel highlights the triplicate names.

Image-12-Countif-find-duplicate-value

Now, after completing the process, you can check for duplicate values in another sheet. Finally, you can see no duplicate values and all same value got permanently deleted.

Conditional Formatting

Use When:

  • You have a data set that is small enough to be cleansed visually
  • You want to visually identify, analyze, and confirm your duplicates before removing them
  • Your data is complex enough that highlighting of duplicates will help identify them
Related text  How to write a reference letter

One of the most useful conditional formatting functions in Excel is the ability to highlight duplicate values.  To use this function, simply highlight the data in question.  Within our sample data set, since we are only concerned about duplicate values in the Unique ID field, we’ll highlight that specific column.  Then under the “Home” tab of the Excel ribbon, click:

  • Conditional Formatting
  • Highlight Cell Rules
  • Duplicate Values

 Remove Duplicates 04

If you use the default setting, your cells will be highlighted in red.  Since conditional formatting doesn’t automatically remove duplicates for you, you’ll have to manually delete them.  Because of this limitation, the conditional formatting approach is primarily useful for smaller data sets, where you can reasonably scan through the entire data set to make edits.

Remove Duplicates 05

Sort the Data

Use When:

  • You have a data set that is small enough to be cleansed visually
  • You want to visually identify, analyze, and confirm your duplicates before removing them
  • Your data is simple enough that you can easily tell the values apart

Sorting your data table is one of the fastest ways to find duplicates in Excel.  Assuming your data set is small and has simple values, a simple sort should allow you to see if any duplicates exist.

However, if you have complex values in your column, such as a ten digit alpha numeric code, it’ll be very difficult for you to see the repeating values visually.  In that case, you should use conditional formatting.

To sort the data, go to the “Data” tab of the Excel ribbon and click any one of the sort buttons.

Related text  How to use a percolator

Remove Duplicates 08

Additionally, to use the shortcut function, you can just select the column in question and type either:

ALT + A + S + D (for sort descending)ALT + A + S + A (for sort ascending)

Like this post? Please share to your friends: