Alok
Duplicates in Excel worksheets are a big issue for the users. It is very tiresome work to find duplicates and remove them. In this tutorial, we will show some easy methods about how to remove duplicates from Excel sheet.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Methods to Remove Duplicates in Excel Sheet
Before removing duplicates, we need to find the duplicates first. Then we will remove those duplicates. We will explain all the processes of removing duplicates in the following methods.
1. Conditional Formatting to Remove Duplicates in Excel Sheet
By conditional formatting, we will identify the duplicate first. Then remove those duplicates. The following data set will be considered to find and remove duplicates.
Step 1:
- First, select all cells containing data from our dataset.
Step 2:
- In the Home tab, select the Conditional Formatting tool from the Styles group.
- Choose Highlight Cells Rules from the given options.
- Click on Duplicate Values finally.
Step 3:
- A new dialog box appears. Choose Duplicate values in the box.
- You can also change the highlighting color. Then press OK.
We find two duplicate rows in our data. Look at the following image.
Step 4:
- Now, enable the filter by pressing Ctrl+Shift+L.
Step 5:
- Apply the Filter option now. Choose the Filter by Color option.
- Select the default color as we choose in the previous step.
Now, the identical rows are showing only.
Step 6:
- Select any of the rows. If we have more than two duplicate rows, then we must select all the rows leaving one single row.
Step 7:
- Now, press the right button of the mouse.
- Choose the Delete Row option.
Step 8:
- A pop-up will show for permission. Press OK there.
One row remains in the data set. Look at the image below.
Step 9:
- Now, disable the Filter feature again by pressing Ctrl+Shift+L.
We removed all the duplicates from our data set.
Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)
2. Remove Duplicate Values Using the Remove Duplicates Command
Using the Remove Duplicates command, we can remove duplicate values easily. We get this tool in the Data tab.
Step 1:
- First, select all the cells from where to remove replicates.
Step 2:
- Go to the Data tab now.
- Choose the Remove Duplicates command from the Data Tolls group.
Step 3:
- Remove Duplicates dialog box will appear.
- Select the Columns from the list. We select both columns.
Step 4:
- Now, click OK.
- A pop-up will show mentioning the duplicate value removing and unique values remaining. Press OK there.
Look at the image below.
Replicated values are removed from both columns.
We can also remove replicated values from a single column.
Step 5:
- Again, go to the Remove Duplicates window.
- Choose the Fruit column and press OK.
Step 6:
- Press OK on the pop-up.
2 duplicates and 7 unique values are found here.
Notice the Fruit column. No duplicate values are present here. But in the Customer column, duplicate values may exist.
Read More: Fix: Excel Remove Duplicates Not Working (3 Solutions)
3. Apply Excel Advanced Filters to Withdraw Duplicates
The Advanced filter is another cool path to remove duplicates from Excel sheets.
Step 1:
- Select the cells first.
Step 2:
- Go to the Data tab.
- Choose Advanced from the Sort & Filter group.
A new dialog box will appear. In the List Range, our selected range is shown. We have two options. One is to remove the duplicate and show it in the present list. And the other is copying the unique data to a new location.
Step 3:
- Choose Filter the list, in-place option.
- Put a tick mark on the Unique records only.
- Then press OK.
Look at the data set.
The duplicate data is not showing here. It hides the duplicate rows.
But when we copy data in a new location, the duplicate data is purely removed from the list.
Step 4:
- Again, go to the Advanced Filter option.
- Select Copy to another location option.
- Choose the new location on the Copy to box.
- Also, tick on the Unique records only.
- Then, press OK.
Notice the following image.
All unique data are copied to Cell E5 and the rest.
Duplicate data was absolutely removed from here.
Read More: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
4. Extract Duplicates Using Excel Formula
We can use a formula to extract duplicates from Excel sheets.
Step 1:
- We add both columns in the Combine column.
Step 2:
- Go to Cell D5 and apply the simple formula using the ampercent sign(&).
=B5&C5
Step 3:
- Then, press Enter.
See, data from both columns are combined.
We can apply another TEXJOIN function to perform this.
Step 4:
- Apply the formula based on the TEXTJOIN function on Cell D5.
=TEXTJOIN("",FALSE,B5,C5)
Step 5:
- Again, press Enter.
Step 6:
- Pull the Fill Handle icon towards the last cell.
Step 7:
- Now, add a column to count the number of objects in the Combine column using the COUNTIF function.
- Put the formula below on Cell E5.
=COUNTIFS($D$5:D5,D5)
Step 8:
- Press the Enter button.
Step 9:
- Drag the Fill Handle icon again.
In the count column, we counted the object that how many times found on the Combine column. If the value is 1 means that is unique. But it that is 2 or more means that object is repeated.
Step 10:
- Now, press Ctrl+Shift+L and enable the filter mood.
Step 11:
- Now, click on the arrow of the Count column.
- Leave 1 from the filter list.
- Then press OK.
Now, the presented values are the duplicates values.
Step 12:
- Now, delete the duplicate values and disable the Filter mood by pressing Ctrl+Shift+L.
Here, only the unique values are presented.
Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)
Similar Readings
- How to Remove Duplicate Rows in Excel (3 Ways)
- Remove duplicate rows based on two columns in Excel [4 ways]
- Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)
- Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)
- How to Remove Both Duplicates in Excel (5 Easy Ways)
5. Delete Duplicates Using the Pivot Table
The Pivot table is an interesting tool. We can delete duplicates using the Pivot Table also.
Step 1:
- Go to the Insert tab first.
- Select PivotTable from the Table group.
- Click on From Table/Range option from the list.
A new dialog box of PivotTable input options will appear.
Step 2:
- In the Table/Range, select our desired data range.
- We can place the newly from PivotTable in a new sheet or within this sheet. We select Existing Worksheet and put the location here.
Step 3:
- Then, press OK.
We can see PivotTable1 with its properties.
Step 4:
- Put a tick on Customer and Fruit options and set them on the Rows field.
Step 5:
- Now, go to the Design tab.
- Then click on Report Layout from the Layout group.
Step 6:
- From the list first, click on Show in Tabular Form.
- Again repeat this process and click on Repeat All Item Labels.
Step 7:
- Now, go to Subtotals in the Layout group.
- Choose the Do Not Show Subtotals option.
Look at the PivotTable1 now.
Now, in PivotTable1 no duplicate values were presented.
Read More: How to Remove Duplicate Rows in Excel Table
6. Eliminate Duplicates Using the Power Query from Excel Sheet
The Power Query is a Data tool by which we can remove duplicates. Power Query has a default option to remove duplicates.
Step 1:
- Go to the Data tab first.
- Choose From Table/Range from the Get and Transform Data group.
Step 2:
- Input the cell reference in the Create Table window.
- Tick on My table has headers option.
- Then press OK.
Now, a new window will appear with the data in Power Query mood.
Step 3:
- Go to the header of the Fruit column.
- Click the right button of the mouse.
- Choose Remove Duplicates from the list.
See the following image.
Duplicate data from the Fruit column has been removed.
We can remove duplicate data from the whole table also.
Step 4:
- Go to the left upper corner of the table.
- Press the right button of the mouse.
- Choose Remove Duplicates.
Now, duplicate data from the whole table has been removed.
Step 5:
- We have another option to remove duplicates in Power Query.
- Go to the Home tab of the Power Query.
- Click on the Remove Rows tool.
- Now, select Remove Duplicates from the list.
We have the facility to remove duplicates from the single column or from the whole table in Power Query.
7. Apply Excel VBA Macro to Remove Duplicates
We can remove the duplicates with a simple VBA code.
Step 1:
- Go to the Developer mode first.
- Click on Record Macro.
- Put Remove_Duplicate in the Macro name box.
- Then press OK.
Step 2:
- Now, press on Macros.
- Choose Remove_Duplicates and then press the Step Into option.
Step 3:
- Put the VBA code on the command module.
Sub Remove_Duplicate()
Range("B4:C13").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
Here, Columns:=1 defines duplicates that will be removed based on the 1st column of our given range and Header:=xlYes defines we have header cell.
Step 4:
- Click on the marked box to run the code or press the F5.
Now, look at the data set.
Duplicated data are removed from the data set.
Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)
Things to Remember
- When applying the Advanced Filter method, carefully input the cell references.
- In PivotTable take the decision where to place the table. Two options: Existing worksheet or new worksheet.
- You can make the formula with other functions too in removing duplicates. We showed two functions here.
Conclusion
In this article, we described how to find and remove duplicates from Excel sheet. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
