If you have ever worked with data in Excel, you know that duplicate values can be a major headache. Sometimes it is easy to accidentally copy and paste the same value into a column multiple times.
To help you with this, we have explained here how to remove or get rid of duplicates in Excel using several different methods.
We will also provide some tips on how to avoid creating duplicate values in the first place. Let’s get started.
5 Different Methods to Remove or Get Rid of Duplicates In Excel Files
we are going to look at 5 different methods to locate and remove duplicate values from your data set in Excel.
We will start with the simplest method and work our way up to a bit more advanced methods.
Method #01: Remove Duplicates Using the Filter Tool
The first method we will look at is using the built-in Filter tool in Excel.
This tool can be found under the Data tab in the ribbon.
Click on the Filter button and a drop-down menu will appear.
From here, you can select which columns you want to filter by.
For our purposes, we want to select all of the columns that contain data.
Once you have selected your columns, click on OK.
Excel will now display a little filter icon next to each column header.
Click on the icon for the column that contains duplicates and select Filter by Color > Duplicates. This will filter out all of the cells that are not duplicates.
To remove the duplicates, select the cells that are highlighted and press the Delete key on your keyboard.
You can also right-click on the selection and choose Delete from the menu.
Method #02: Remove Duplicates Using Formula
The next method we will look at is using a formula to identify duplicates.
For this method, we will use the COUNTIF function.
This function takes two arguments – a range and criteria. The range is the cells that you want to count and the criteria are what you want to count them by.
In our case, we want to count all of the cells in column A that contain the same value.
To do this, we will use the following formula: =COUNTIF(A:A, A23)
This formula will return a count of all the cells in column A that contain the same value as cell A23.
If there are no duplicates, it will return a value of “0”. Only if there is one duplicate, it will return a value of “0”. If there are two or more duplicates, it will return a higher number.
We can use this formula to identify which cells contain duplicates by adding them to a new column next to our data.
To do this, enter the formula into the first cell of an empty column and then drag it down to the last cell of our data set.
Once you have added the formula, you can filter by column B to only show cells that contain duplicates.
To remove the duplicates, select the cells that are highlighted and press the Delete key on your keyboard. You can also right-click on the selection and choose Delete from the menu.
Method #03: Remove Duplicates Using Pivot Tables
Pivot tables are a powerful tool that can be used to analyze data sets. They can also be used to identify and remove duplicates.
To create a pivot table, click on the Insert tab in the ribbon and then select Pivot Table from the drop-down menu.
Excel will prompt you to choose where you want to place your pivot table. For our purposes, we will choose to place it on a new worksheet.
Once you have placed your pivot table, you will need to select the data that you want to use. To do this, click on the cells that contain your data and then press the Enter key on your keyboard.
Excel will now display a PivotTable Fields pane on the right side of the screen. In this pane, you will see a list of all of the fields in your data set.
Drag the field that contains duplicates into the Values area.
Excel will now display a count of all values in that field.
To remove duplicates, select any cell in the pivot table and then click on the Filter icon that appears at the top of the pivot table.
From here, you can select to only show values that are duplicates.
Select the cells that are highlighted and press the Delete key on your keyboard to remove the duplicates.
You can also right-click on the selection and choose Delete from the menu.
Method #04: Remove Duplicates Using VBA
The fourth method we will look at is using a VBA macro to remove duplicates.
This method is more complex than the previous methods but it offers more flexibility.
To create a macro, click on the Developer tab in the ribbon and then select Visual Basic from the drop-down menu.
This will open up Microsoft Visual Basic for Applications. From here, you will need to create a new module. To do this, click on the Insert menu and then select Module from the drop-down menu.
Once you have inserted a new module, copy and paste the following code into the editor:
Sub RemoveDuplicates()
‘Declare variables Dim lr As Long Dim i As Long ‘Find last row in column A lr = Range(“A” & Rows.Count).End(xlUp).Row ‘Loop through each row For i = lr To i Step -lr + i ‘Check if value is duplicated If Application.WorksheetFunction.CountIf(Range(“A” & I), Range(“A” & I).Value) > 0 Then ‘Delete row if duplicated Range(“A” & I will).EntireRow.Delete End If Next i End Sub
This code loop through each row in column A and checks if the value is duplicated. If it is, the row will be deleted.
To run the macro, press the F-12 key on your keyboard or click on Run from the Debug menu.
You should now have a list of data without any duplicates.
You can save this new data set by clicking on File and then selecting Save As from the drop-down menu. Choose a location to save your file and then click on the Save button.
Method #05: Remove Duplicates Using An Advanced Filter
The fifth and final method is to use the Advanced Filter. An Advanced Filter is a tool that allows you to filter data based on the criteria you specify.
To use the Advanced Filter, go to the Data tab and click on Advanced.
This will open up the Advanced Filter dialog box. Select the range of cells that you want to use and then click OK.
You will then be asked to select the criteria for your filter.
In this example, we are going to select all of the values in column A that appear more than once in column A.
To do this, click on the drop-down menu next to Operator and choose “greater than.” Then, enter “0” next to Value and click OK.
Excel will then filter your data and only show the values that meet your criteria.
Final Thoughts on getting Rid of Duplicate in Excel
There you have it! Five different methods that you can use to remove duplicates in Excel. Try out each method and see which one works best for you. Do you have a preferred method?
Let us know in the comments section below. Thanks for reading!