How to remove duplicate values in different excel sheets?
Excel Remove Duplicates (Table of Contents)
Remove Duplicates in Excel
Remove Duplicates in excel is used for removing the duplicate cells of one or multiple columns. This is very easy to implement. To remove duplicates from any column first select the column/s from where we need to remove duplicate values, then from the Data menu tab, select Remove Duplicate under data tools. We will get the window for the same, where we can select and unselect the columns from where we want to remove duplicate values.
Excel functions, formula, charts, formatting creating excel dashboard & others
There are three methods of finding and removing duplicate data:
- Using Conditional Formatting followed by Filter feature of Excel.
- Using Remove Duplicate feature in Excel.
- Using the 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 to excel.
#1 – Using Conditional Formatting followed by Filter feature
For Conditional Formatting
- There are duplicate occurrences of data in the below table.
- 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 DuplicateValues.
- A duplicate Values dialog box will appear. Select either Duplicate or Unique option that you want to highlight. In this example, we have selected the Duplicate option.
- Choose color and font of the highlighted cells in values with a drop-down list and then click on OK.
- After using the Conditional Formatting feature, the table will look like below:
- 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.
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.
The table will look like below:
- 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.
This will show duplicated values which you may delete.
The output will be as shown below:
- How to clear Filter?
Select the column header of the table. Click on Filter.
Then the filter will be removed.
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 Clicks on Remove Duplicates.
- 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.
- 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.
- The output will be as shown below:
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
A 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.