Duplicate values in a data set may be quite inconvenient! It can result in significant mistakes and overestimating or underestimating your results.
It is often important to Remove Duplicates in Excel, and Excel makes detecting and deleting these duplicates from your data a breeze.
This article will explore my proven seven ways of locating and removing repeating values from an Excel data set.
As a side note, if an alternative tool is Google’s, spreadsheet. While you would pay to access Excel, the Google counterpart is cheap and may be a good option to explore.
That said, let’s get right into it!
What Are Duplicate Values?🤷♂️
You have duplicate values when an identical value or collection of values appears in your data. These values can be defined in various ways for a given data collection.
In the example shown above, there is a simple piece of data for a list of automobiles with three columns for Make, Year, and Model.
The first graphic shows all duplicates based only on the car’s make.
The second picture shows all duplicates based on the car’s Make and Model. As a result, there is one less duplication.
The Third picture shows all the duplicates based on the table’s columns. As a result, fewer values are deemed duplicates.
Duplicates based on a single column vs. the full table might provide drastically different outcomes. You should know the version you want and what Excel is doing.
How to Use The Remove Duplicates Command to Remove Duplicate in Excel
Duplicate value removal in data is a typical activity. It’s so prevalent that the ribbon has a specific command for it.
Go to the Data tab and click the “Remove Duplicates command” on a cell within the data from which you wish to remove duplicates.
The “Remove Duplicates window” will appear when Excel selects the complete data set.
Then you must inform Excel if the first row of data has column headers. If this option is selected, the data on the first row will be ignored while looking for and deleting duplicate entries.
After that, you may choose which columns to utilize to find duplicates. You may utilize the “Select All and Unselect All buttons” above if you have a large list of columns in your data.
When you hit OK, Excel will delete all duplicate values it discovers and provide you with a total count of the eliminated number and how many remain.
Because this command will change your data, running it on a duplicate of your data set is preferable to keep the original data unchanged.
How to Use Advanced Filters to Find And Remove Duplicates in Excel
Using the ribbon, there’s also a technique to remove duplicate values from your data. The sophisticated filters make this feasible.
Navigate to the Data tab and select the option for the “advanced filter” command on a cell inside the data.
Doing this opens up a window for “Advanced Filter.
You may either Filter the list in its current position or Copy it to a different location. If the list is filtered in its current position, it will hide any duplicate rows, but moving the data to another location will duplicate it.
Excel will make an educated guess about the data range, but you may change it in the List range. If the Copy to another place option was selected, the Criteria range can be left blank, but the Copy to the box must be filled.
Select “Unique records only” from the drop-down menu.
If you click OK, the duplicate values will be removed.
Advanced filters may be a useful tool for eliminating duplicate values while making a copy of your data. Advanced filters, however, can only do this on the full table.
How to Use Pivot Table to Find And Remove Duplicates in Excel
Isn’t the sole purpose of pivot tables to analyze data? You may also utilize them to get rid of duplicate data!
With this approach, you won’t eliminate pre-existing values from your data; instead, you’ll utilize a pivot table to present only the data set’s unique values.
Create a pivot table using your data first. Pick a cell or the whole range of data in your data, then go to the Insert tab, select PivotTable, and hit OK in the Create PivotTable dialog box option.
Add all fields to the pivot table’s Rows area using the new empty pivot table.
After that, you’ll need to alter the layout of the resultant pivot table to make it tabular. Select Report Layout from the Design tab while the pivot table is chosen. You’ll need to alter two choices in this section.
Choose “Show in Tabular Form” from the drop-down menu.
Select the option to “repeat all item labels”.
You’ll also want to eliminate any subtotals in the pivot table. Select Subtotals from the Design tab, then Do Not Show Subtotals.
You’ve created a pivot table that looks like a table of data!
Because pivot tables only show distinct values for elements in the Rows section, duplicates values in your data will be immediately removed.
How to Use Power Query to Find And Remove Duplicates in Excel
Because Power Query deals a lot with data transformation, you can trust it to discover and eliminate duplicate values.
Select the table containing values from which duplicates should be removed, then navigate to the Data tab and select a From Table/Range query.
How to Remove Duplicates in Excel Using One Column or More
You may use Power Query to eliminate duplicates in a table based on one or more columns.
You must choose which columns will be used to eliminate duplicates. To select several columns, hold down the Ctrl key during selection.
Remove Duplicates from the menu by right-clicking on the specified column header and choosing the remove duplicate heading.
To get this command, you may also navigate Remove Rows / Remove Duplicates from the Home tab.
= Table.Distinct(#”Previous Step”, {“Make”, “Model”})
When you investigate the formula produced, you’ll notice that it uses the Table.
The second option specifies which columns should be used in the function.
How to Remove Duplicates in Excel Using One Or More Columns
You may pick all the columns in the table and then delete duplicates to eliminate duplicates across the board. However, there is a quicker technique that does not need to choose all the columns.
In the upper left corner of the data preview, there is a button with a list of instructions that may be applied to the full table.
Select Remove Duplicates from the table button positioned at the top left corner.
= Table.Distinct(#”Previous Step”)
By looking at the formula that’s been produced, you’ll notice that it utilizes the same Table.
There is no second parameter in this function. If the second parameter is omitted, the formula will act on the entire table.
How to Keep Duplicates in Excel Using a Single Columns or The Whole Table
There are other instructions in Power Query for retaining duplicates for specified columns or the whole table.
Follow the same procedures as before, except instead of deleting duplicates, use the Keep Rows / Keep Duplicates command. This will display all of the information with a value that is duplicated.
How to Remove Duplicates in Excel Using A Formula
To identify duplicate values in your data, you can use a formula.
To begin, create a helper column that combines the data from any columns on which your duplicate definition will be based.
= [@Make] & [@Model] & [@Year]
All three columns will be concatenated into one column using the abovementioned technique. Each column is joined using the ampersand operator.
= TEXTJOIN(“”, FALSE, CarList[@[Make]:[Year]])
The method above can be used if you have a lengthy list of columns to merge. This allows you to refer to all columns with a single range.
After that, you must create a new column to count the duplicate data. Later on, this will filter away data rows that appear many times.
= COUNTIFS($E$3:E3, E3)
You can count the frequency of appearances for the current value in the list of values by copying the above formula along the column.
If the count is 1, the value is the first time it appears in the data, and it will be added to your list of unique values. If the count is two or more, the value has previously occurred in the data and is a duplicate that may be deleted.
Filters should be added to your data set.
Choose the Filter command option from the Data tab.
Ctrl + Shift + L is a keyboard shortcut.
You may now use the Count column to filter your results. Filtering on 1 returns all unique values and eliminates any duplicates.
The viewable cells from the resultant filter can then be copied and pasted elsewhere. To select only the visible cells, use hotkey Alt + ;.
How to Remove Duplicates in Excel Using Conditional Formatting
You may use conditional formatting to identify duplicate values in datasets.
You’ll need to create a helper column to combine the data from the columns, like the formula approach. If you wish to identify repetition based on more than one column, you’ll need this combined column since conditional formatting doesn’t function with data across rows.
You must choose the merged data column.
To construct conditional formatting, navigate Conditional Formatting / Highlight Cells Rules / Duplicate Values from the Home tab.
This will bring up the Duplicate Values window for conditional formatting.
You have the option of highlighting Duplicate or Unique data.
To highlight the data, you may choose from several standard cell forms or design your unique format.
Caution: The previous techniques for finding and removing duplicates treat the first value’s instance as a duplicate and ignores it. Conversely, this technique will just emphasize the first instance and will not distinguish between them.
Once the values have been highlighted, it is possible to use the filter by the color option to filter on duplicate or unique values.
Don’t forget to apply filters to your data. Select the Filter command from the Data tab; alternatively, the keyboard shortcut Ctrl + Shift + L works fine.
How to remove duplicates in Excel based on one column?
To remove duplicates in Excel based on one column, follow these steps:
Step 1: Select the data range from which you want to remove duplicates.
Step 2:– Go to the “Data” tab on the Excel ribbon.
Step 3: Click the “Remove Duplicates” button in the “Data Tools” group.
Step 4:– In the “Remove Duplicates” dialog box, make sure that the checkbox next to the column that you want to base the removal on is selected.
Step 5:– Click on the “OK” button.
How to remove duplicates but keep first instance in Excel?
To remove duplicates but keep the first instance in Excel, follow these steps:
Step 1:– Select the data range that you want to remove duplicates from.
Step 2:- Go to the “Data” tab on the Excel ribbon.
Step 3:– Click on the “Remove Duplicates” button in the “Data Tools” group.
Step 4:- In the “Remove Duplicates” dialog box, make sure that the checkbox next to the column that you want to base the removal on is selected.
Step 5:- Uncheck all the other columns except the one that you want to base the removal on.
Step 6:– Click on the “OK” button.
You can also explore our “3 EASY Ways to Find and Remove Duplicates in Excel” video.
📗FAQ
What is the fastest way to remove duplicates in Excel?
The fastest way to remove duplicates in Excel is to use the “Remove Duplicates” feature in the Data tab.
To access this feature, select the range of cells you want to remove duplicates from, go to the “Data” tab, and click on “Remove Duplicates.” Excel will automatically identify and highlight the duplicate rows in your selected range.
You can then choose which columns to check for duplicates and remove them quickly and easily.
How do I remove duplicates in Excel but keep one?
You can use the “Remove Duplicates” feature in the Data tab to remove duplicates in Excel but keep one. Select the range of cells you want to remove duplicates from, go to the “Data” tab, and click on “Remove Duplicates.”
In the pop-up window, select the columns you want to check for duplicates and uncheck the columns you want to keep. Then, click on “OK,” and Excel will remove all duplicates from the selected columns except for the first occurrence.
How do I remove duplicates from a spreadsheet?
You can use the “Remove Duplicates” feature in the Data tab to remove duplicates from a spreadsheet. Select the range of cells from which you want to remove duplicates, go to the “Data” tab, and click “Remove Duplicates.”
In the pop-up window, select the columns you want to check for duplicates and click “OK.” Excel will remove all duplicates from the selected columns.
Does removing duplicates in Excel remove both?
Removing duplicates in Excel removes all occurrences of the duplicate rows except for the first occurrence. So, if you have three identical rows, Excel will remove two, leaving only one.
How do I remove duplicates from a list?
You can use the “Remove Duplicates” feature in the Data tab to remove duplicates from a list. Select the range of cells you want to remove duplicates from, go to the “Data” tab, and click on “Remove Duplicates.”
In the pop-up window, select the columns you want to check for duplicates and click “OK.” Excel will remove all duplicates from the selected columns.
What is the formula for duplicates in Excel?
The formula to identify duplicates in Excel is =COUNTIF(range,criteria)>1. This formula counts the number of times a value appears in a range of cells and returns “TRUE” if the count is greater than 1, indicating that the value is a duplicate.
How do I remove duplicates only if all columns match in Excel?
You can use the “Remove Duplicates” feature in the Data tab to remove duplicates only if all columns match in Excel.
Select the range of cells you want to remove duplicates from, go to the “Data” tab, and click on “Remove Duplicates.” In the pop-up window, select all the columns you want to match and click on “OK.” Excel will remove all duplicates where all columns match.
Why can’t I use remove duplicates in Excel?
You may not be able to use the “Remove Duplicates” feature in Excel for several reasons. One possible reason is that the range of cells you have selected contains merged cells or table headers, which can cause Excel to return an error.
Another possible reason is that the range of cells you have selected contains formulas, which can cause Excel to remove rows that appear to be duplicates but are actually different due to the formulas.
Does Excel remove duplicates first or last?
Removing duplicates in Excel removes all occurrences of the duplicate rows except for the first occurrence. So, Excel removes duplicates first and keeps the first occurrence of the duplicate rows.
How do I short duplicate data in Excel?
To sort duplicate data in Excel, you can select the range of cells you want to sort, and then go to the “Data” tab and click on “Sort.”
In the “Sort” dialog box, select the column you want to sort by, and then select “Values” in the “Sort On” drop-down list. Choose either “A to Z” or “Z to A” in the “Order” drop-down list. Excel will sort the range of cells based on the selected column, and duplicate data will appear next to each other.
How do I find duplicates in a large Excel file?
You can use the “Conditional Formatting” feature to find duplicates in a large Excel file.
Select the range of cells you want to check for duplicates, and then go to the “Home” tab and click on “Conditional Formatting” > “Highlight Cells Rules” > “Duplicate Values.” Excel will highlight all duplicate values in the selected range, making finding and removing them easy.
Should you remove duplicates?
It depends on the situation. In some cases, duplicates may be intentional and necessary; in others, they may result from data entry errors or system glitches.
In general, removing duplicates if they are unnecessary is a good practice, as they can create confusion and inaccuracies in data analysis and reporting.
How do I remove the same thing from every cell in Excel?
To remove the same thing from every cell in Excel, you can use the “Find and Replace” feature. Select the range of cells you want to modify, and then go to the “Home” tab and click on “Find and Select” > “Replace.”
In the “Find and Replace” dialog box, enter the text you want to remove in the “Find what” field, and leave the “Replace with” field blank. Click on “Replace All,” and Excel will remove the specified text from every cell in the selected range.
How do I remove duplicates from multiple columns in Excel?
To remove duplicates from multiple columns in Excel, you can use the “Remove Duplicates” feature in the Data tab. Select the range of cells you want to remove duplicates from, go to the “Data” tab, and click on “Remove Duplicates.”
In the pop-up window, select all the columns you want to check for duplicates and click “OK.” Excel will remove all duplicates where all columns match.
How do I compare two columns in Excel to Delete duplicates?
To compare two columns in Excel to delete duplicates, you can use the “Remove Duplicates” feature in the Data tab. Select the two columns you want to compare, go to the “Data” tab, and click on “Remove Duplicates.”
In the pop-up window, select both columns and click “OK.” Excel will remove all duplicates where both columns match.
What is trim function in Excel?
The TRIM function in Excel removes all leading and trailing spaces from a text string. It’s useful for cleaning up data imported or copied from other sources. The syntax for the TRIM function is =TRIM(text), where “text” is the text string you want to trim.
Does removing duplicates remove rows?
Yes, when you remove duplicates in Excel, it removes entire rows that contain duplicate values. Excel removes the entire row if you have duplicate values in multiple columns.
Where is remove duplicates in Excel 365?
To find the “Remove Duplicates” feature in Excel 365, select the range of cells you want to remove duplicates from, go to the “Data” tab, and look for the “Data Tools” group.
Click on “Remove Duplicates” in this group, and Excel will open the “Remove Duplicates dialog box. In this dialog box, you can select the columns you want to check for duplicates and customize the removal process.
How do I find duplicates in Excel without first occurrence?
You can use a combination of formulas and conditional formatting to find duplicates in Excel without the first occurrence.
First, create a helper column next to the column you want to check for duplicates. In this helper column, enter the formula =IF(COUNTIF(A:A,A2)>1,”Duplicate”,”Unique”), where “A” is the column you want to check for duplicates. This formula will identify all duplicate values in the column.
Next, go to the “Home” tab and click on “Conditional Formatting” > “Highlight Cells Rules” > “Duplicate Values.” In the “Duplicate Values” dialog box, select “Duplicate” in the “Format cells with” drop-down list and choose the formatting you want to apply to the duplicate values.
Excel will highlight all duplicate values in the column except for the first occurrence.
How do you deal with duplicate data?
There are several ways to deal with duplicate data in Excel. The most common method is to use the “Remove Duplicates” feature in the Data tab to remove all duplicate rows.
Another method is to manually sort the data by a specific column to identify and deal with duplicate values. You can also use conditional formatting to highlight duplicate values and make them easier to identify.
If you need to keep the duplicate data, you can create a new column and use formulas or functions to combine the data from the duplicate rows. Alternatively, you can use pivot tables or other data analysis tools to group and summarize duplicate data.
Final Thoughts on how to Remove Duplicates in Excel
I have gone through several hacks to Remove Duplicates in Excel. Most of these are easy-to-follow methods, others are a bit advanced. Whatever your need is, you would be able to find a hack that suits your Excel level or needs.
I am curious to know what other hacks and shortcuts you have found, so please leave these in the comments section.
As a rule of thumb, there is no rule on the thumb with regards to which of these you should use. Think of what you want to achieve, your dataset, and which methods you feel the most comfortable using. These should be the only things informing the decision you make.
You should also note that, with formulas, it is really easy to make a mistake and ruin it all. You must check the formulas, character by character, and word by word to make sure you do not have bugs that will cause errors in your result.
If you have further questions on how to Remove Duplicates in Excel, let me know in the comment section and I will probably leave you a few workarounds or explanations.
That is it. Enjoy working on your data set!
