In this article, we will Learn different ways to remove duplicates in Excel
What Is A Duplicate Value?
Duplicate values happen when the same value or set of values appear in your data.
For a given set of data We can define duplicates in many different ways.
In the above example, there is a simple set of data with 3 columns for the Make, Model and Year for a list of cars.
- The first example highlights all the duplicates based only on the Make of the car.
- The second example highlights all the duplicates based on the Make and Model of the car. This results in one less duplicate.
- The second example highlights all the duplicates based on all columns in the table. This results in even less values being considered duplicates.
The results from duplicates based on a single column vs the entire table can be very different. We should always be aware which version We want and what Excel is doing.
All of these might be confusing to understand. Let's understand how to use the function using some examples.
Find And Remove Duplicate Values With The Remove Duplicates Command
Removing duplicate values in data is a very common task. It’s so common, there’s a dedicated command to do it in the ribbon.
Select a cell inside the data which We want to remove duplicates from and go to the Data tab and click on the Remove Duplicates command.
Excel will then select the entire set of data and open up the Remove Duplicates window.
We then need to tell Excel if the data contains column headers in the first row. If this is checked, then the first row of data will be excluded when finding and removing duplicate values. we can then select which columns to use to determine duplicates. There are also handy Select All and Unselect All buttons above We can use if you’ve got a long list of columns in your data.
When We press OK, Excel will then remove all the duplicate values it finds and give We a summary count of how many values were removed and how many values remain.
This command will alter your data so it’s best to perform the command on a copy of your data to retain the original data intact.
Find And Remove Duplicate Values With Advanced Filters
There is also another way to get rid of any duplicate values in your data from the ribbon. This is possible from the advanced filters.
Select a cell inside the data and go to the Data tab and click on the Advanced filter command.
This will open up the Advanced Filter window.
We can choose to either Filter the list in place or Copy to another location. Filtering the list in place will hide rows containing any duplicates while copying to another location will create a copy of the data.
Excel will guess the range of data, but We can adjust it in the List range. The Criteria range can be left blank and the Copy to field will need to be filled if the Copy to another location option was chosen.
Check the box for Unique records only.
Press OK and We will eliminate the duplicate values.
Advanced filters can be a handy option for getting rid of your duplicate values and creating a copy of your data at the same time. But advanced filters will only be able to perform this on the entire table.
Find And Remove Duplicate Values With A Pivot Table
Pivot tables are just for analyzing your data, right?
We can actually use them to remove duplicate data as well.
We won’t actually be removing duplicate values from your data with this method, We will be using a pivot table to display only the unique values from the data set.
First, create a pivot table based on your data. Select a cell inside your data or the entire range of data > go to the Insert tab > select PivotTable > press OK in the Create PivotTable dialog box.
With the new blank pivot table add all fields into the Rows area of the pivot table.
We will then need to change the layout of the resulting pivot table so it’s in a tabular format. With the pivot table selected, go to the Design tab and select Report Layout. There are two options We will need to change here.
Select the Show in Tabular Form option. Select the Repeat All Item Labels option.
We will also need to remove any subtotals from the pivot table. Go to the Design tab > select Subtotals > select Do Not Show Subtotals.
We now have a pivot table that mimics a tabular set of data. Pivot tables only list unique values for items in the Rows area, so this pivot table will automatically remove any duplicates in your data.
Find And Remove Duplicate Values With Power Query
Power Query is all about data transformation, so We can be sure it has the ability to find and remove duplicate values.
Select the table of values which We want to remove duplicates from > go to the Data tab > choose a From Table/Range query.
Remove Duplicates Based On One Or More Columns
With Power Query, We can remove duplicates based on one or more columns in the table.
We need to select which columns to remove duplicates based on. We can hold Ctrl to select multiple columns.
Right click on the selected column heading and choose Remove Duplicates from the menu.
We can also access this command from the Home tab > Remove Rows > Remove Duplicates.
= Table.Distinct(#"Previous Step", {"Make", "Model"})
If We look at the formula that’s created, it is using the Table.Distinct function with the second parameter referencing which columns to use.
Remove Duplicates Based On The Entire Table
To remove duplicates based on the entire table, We could select all the columns in the table then remove duplicates. But there is a faster method that doesn’t require selecting all the columns.
There is a button in the top left corner of the data preview with a selection of commands that can be applied to the entire table.
Click on the table button in the top left corner ? then choose Remove Duplicates.
= Table.Distinct(#"Previous Step")
If We look at the formula that’s created, it uses the same Table.Distinct function with no second parameter. Without the second parameter, the function will act on the whole table.
Keep Duplicates Based On A Single Column Or On The Entire Table
In Power Query, there are also commands for keeping duplicates for selected columns or for the entire table.
Follow the same steps as removing duplicates, but use the Keep Rows ? Keep Duplicates command instead. This will show We all the data that has a duplicate value.
Find And Remove Duplicate Values With Conditional Formatting
With conditional formatting, there’s a way to highlight duplicate values in your data.
Just like the formula method, We need to add a helper column that combines the data from columns. The conditional formatting doesn’t work with data across rows, so you’ll need this combined column if We want to detect duplicates based on more than one column.
Then We need to select the column of combined data.
To create the conditional formatting, go to the Home tab > select Conditional Formatting > Highlight Cells Rules > Duplicate Values. This will open up the conditional formatting Duplicate Values window. We can select to either highlight Duplicate or Unique values. We can also choose from a selection of predefined cell formats to highlight the values or create your own custom format.
With the values highlighted, We can now filter on either the duplicate or unique values with the filter by color option. Make sure to add filters to your data. Go to the Data tab and select the Filter command or use the keyboard shortcut Ctrl + Shift + L.
- Click on the filter toggle.
- Select Filter by Color in the menu.
- Filter on the color used in the conditional formatting to select duplicate values or filter on No Fill to select unique values.
We can then select just the visible cells with the keyboard shortcut Alt + ;.
Find And Remove Duplicate Values Using VBA
There is a built-in command in VBA for removing duplicates within list objects.
Sub RemoveDuplicates() Dim DuplicateValues As Range Set DuplicateValues = ActiveSheet.ListObjects("CarList").Range DuplicateValues.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes End Sub
The above procedure will remove duplicates from an Excel table named CarList.
Columns:=Array(1, 2, 3)
The above part of the procedure will set which columns to base duplicate detection on. In this case it will be on the entire table since all three columns are listed.
Header:=xlYes
The above part of the procedure tells Excel the first row in our list contains column headings.
Note : The previous methods to find and remove duplicates considers the first occurrence of a value as a duplicate and will leave it intact. However, this method will highlight the first occurrence and will not make any distinction.
Hope this article about different ways to remove duplicates in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.
Related Articles:
Excel REPLACE vs SUBSTITUTE function: The REPLACE and SUBSTITUTE functions are the most misunderstood functions. To find and replace a given text we use the SUBSTITUTE function. Where REPLACE is used to replace a number of characters in string…
Replace text from end of a string starting from variable position: To replace text from the end of the string, we use the REPLACE function. The REPLACE function use the position of text in the string to replace.
How to Check if a string contains one of many texts in Excel: To find check if a string contains any of multiple text, we use this formula. We use the SUM function to sum up all the matches and then perform a logic to check if the string contains any of the multiple strings.
Count Cells that contain specific text: A simple COUNTIF function will do the magic. To count the number of multiple cells that contain a given string we use the wildcard operator with the COUNTIF function.
Popular Articles :
50 Excel Shortcuts to Increase Your Productivity : Get faster at your tasks in Excel. These shortcuts will help you increase your work efficiency in Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
