Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Excel Remove Duplicates

Remove Duplicates in Excel

Excel Remove Duplicates (Table of Contents)

  • Remove Duplicates in Excel
  • How to Remove Duplicates in Excel?
  • #1 – Using Conditional Formatting followed by Filter feature
  • #2 – Using Remove Duplicates feature in Excel
  • #3 – Using COUNTIf Function along with Filter feature in Excel

Remove Duplicates in Excel

It is very common to have a Duplicate occurrence of data in a spreadsheet. There are many reasons and ways to remove duplicate dataset before analysis of data.

There are three methods of finding and removing duplicate data:

  1. Using Conditional Formatting followed by Filter feature of Excel.
  2. Using Remove Duplicate feature in Excel.
  3. Using COUNTIF function.

How to Remove Duplicates in Excel?

Let’s understand the working of removing Excel duplicate values from a given dataset with the below examples. You can remove duplicate records in a very simple and easy way in excel.

#1 – Using Conditional Formatting followed by Filter feature

For Conditional Formatting

  • There are duplicate occurrences of data in the below table.

Excel Remove Duplicates Step 1

  • Select a range of cells or table. Go to the Home Tab in the Excel Ribbon. Click on Conditional Formatting drop-down list. Select Highlight Cells Rules and then click on Duplicate Values.

Excel Remove Duplicates Step 1-1

  • A duplicate Values dialog box will appear. Select either Duplicate or Unique option that you want to highlight. In this example, we have selected Duplicate option.

Excel Remove Duplicates Step 1-2

  • Choose color and font of the highlighted cells in values with a drop-down list and then click on OK.

Excel Remove Duplicates Step 1-3

  • After using the Conditional Formatting feature, the table will look like below:

Excel Remove Duplicates Step 1-4

  • To clear Conditional formatting:

Select a range of cells or table. Go to the Home Tab in the Excel Ribbon. Click on Conditional Formatting drop-down list. Select Clear Rules and then click on Clear Rules from selected cells.

 Excel Remove Duplicates Step 1-5

How to use Filter for finding Duplicate values in Excel?

The Filter feature allows a user to filter selected data in the spreadsheet. The Advance filter feature allows copying of resulting filtered list to a new location.

  • Select the columns heading of the table.
  • Go to Data tab in the Excel Ribbon. Click on Filter.

Excel Remove Duplicates Step 2-1

The table will look like below:

Excel Remove Duplicates Step 2-2

  • To remove duplicates from any column, click on the Filter drop-down icon in the column header. Click on Filter by Color and select Filter by Cell Color or Filter by Font Color.

Excel Remove Duplicates Step 2-3

This will show duplicated values which you may delete.

Excel Remove Duplicates Step 2-4

The output will be as shown below:

Excel Remove Duplicates Step 2-5

  • How to clear Filter?

Select the column header of the table. Click on Filter.

Excel Remove Duplicates Step 2-6

Then the filter will be removed.

Excel Remove Duplicates Step 2-7

Points to Remember:

  • The deletion in the above process is done manually.
  • Conditional formatting is used to highlight duplicate cells whereas Filter->Filter by Cell Color or Filter by Font Color feature is used to filter highlighted duplicate cells in excel.

#2 – Using Remove Duplicates feature in Excel

  • Select the range of cells or table. Go to the Data Tab in the Excel Ribbon. In the Data tool group Click on Remove Duplicates.

Excel Remove Duplicates Example 3-1

  • Remove Duplicates dialog box will appear. To delete duplicate values, select one or more columns that contain possible duplicates. Here in the above example, we have selected Name and Code no columns, as both the columns, contain duplicate values and then click OK.

Excel Remove Duplicates Example 3-2

  • A message box will be displayed, indicating a number of duplicate values were found and removed and how many unique values remain. In case of no duplicate value, it will display the message No duplicate values found and then click OK.

Excel Remove Duplicates Example 3-3

  • The output will be as shown below:

Excel Remove Duplicates Example 3-4

Points to be Remember:

  • It is an inbuilt feature in Data tool of MS-Excel.
  • It removes Excel duplicate data permanently.

#3 – Using COUNTIf Function along with Filter feature in Excel

Countif function is an easy method of removal of duplicate values in excel. Depending on whether you want to find duplicate values with or without first occurrences, there will be a slight difference in writing the formula.

  1. A formula to find duplicates in Excel including the first occurrence:

  • Duplicate in words

=COUNTIF(range, criteria)>1

Here, a range is column A and topmost cell for criteria is A3. Input the formula shown below in C3 cell. Select the C3 cell and drag to copy the formula till C11. The result will appear as True or False.

Excel Remove Duplicates Example 4-1-1

  • Duplicate in numbers

=COUNTIF(range,criteria)

Here, Range is column A and topmost cell for criteria is A3. Input the formula shown below in D3 cell. Select the D3 cell and drag to copy the formula till D11. The result will appear in count i.e. number.

Excel Remove Duplicates Example 4-1-2

  1. A formula to find duplicates in Excel without first occurrence:

If you want to filter or remove excel duplicates after finding them, the above formula will create some confusion as it marks True to all the identical records. So, we have to use the following formula to keep unique values in the list and to delete all subsequent repetition of values. Let’s modify our Excel duplicate formula by using absolute (by freezing the cell by using $ sign) and relative cell references wherever appropriate.

  • Duplicate in words

Here the unique value will be displayed as FALSE whereas the subsequent duplicate values will be displayed as TRUE.

Excel Remove Duplicates Example 4-2-1

  • Duplicate in numbers

Here the unique value will be numbered as 1 whereas duplicate values will be numbered as 2.

Excel Remove Duplicates Example 4-2-2

  • Use Filter along with the above cases for finding Duplicate values in excel

Select the columns header of the table. Go to Data tab in the Excel Ribbon. Click on Filter feature.

Excel Remove Duplicates Example 4-3-1

Drop down arrow will appear beside the column header.

Excel Remove Duplicates Example 4-3-2

To remove excel duplicates, click on the filter drop-down icon in the column header. Deselect Select All. Select True and then click on Ok.

Excel Remove Duplicates Example 4-3-3

This will show duplicated values which you may delete.

Excel Remove Duplicates Example 4-3-4

The same procedure to be followed while selecting the drop-down filter arrow of column Duplicates in Numbers. Here the difference is instead of True/False the option will be given in numbers i.e. 1,2.

Excel Remove Duplicates Example 4-3-5

Points to be Remember:

  • Here, data are being removed manually using correct formula.
  • A filter is used to make the task easy.

Conclusion

It is always suggested to copy the original range of data or table to another worksheet or location before deleting or removing duplicates permanently from the worksheet.

You can download this Remove Duplicates Excel Template here – Remove Duplicates Excel Template

Recommended Articles

This has been a guide to Remove Duplicates in Excel. Here we discuss how to Remove duplicates in excel using three different methods along with practical examples and downloadable excel template. You may also look at these useful excel tools –

  1. Data Filter | Useful Basic Tool of Excel
  2. Guide To Excel COUNTIF Function
  3. Examples Of COUNTIF with Multiple Criteria
  4. What is Relative Reference in Excel?

The post Excel Remove Duplicates appeared first on EDUCBA.



This post first appeared on Free Online CFA Calculator Training Course | EduCB, please read the originial post: here

Share the post

Excel Remove Duplicates

×

Subscribe to Free Online Cfa Calculator Training Course | Educb

Get updates delivered right to your inbox!

Thank you for your subscription

×