4+ Methods to Filter Duplicate Values in Excel
Having duplicate values in a worksheet can lead to some inconveniences. It can take you a lot of time to find out the identical values manually. Microsoft Excel has great tools to find duplicate values in various ways. After that, the duplicates can be removed to clean up the dataset. I am going to show you 4+ easy methods to filter duplicate values in Excel.
Introduction to the Dataset
Let me introduce you to the datasheet used throughout this article. The dataset I have used for showing the methods has 4 columns about some of the best music albums, artist names, years, and their sales records.
Use the Remove Duplicates Command to Filter Duplicates Values in Excel
The quickest and easiest way to filter out the duplicate values from a dataset is to use the Remove Duplicates command.
Usage Guide
Step_1: Select the entire data table.
Step_2: Click on the Data tab from the ribbon.
Step_3: Then select the Remove Duplicates command from the Data Tools group.
The Remove Duplicates dialog box will show up on the screen.
Step_4: Make sure all the Column boxes are selected. Also, check the tick mark on My data has headers box.
Step_5: Hit OK to take away all the similar values.
Final Result
A window will show up. it will give you a short description of how many values are erased and how many values are kept.
Out of the 14 rows, 4 duplicate rows are found. The identical values are removed and 9 unique rows remain.
Use ADVANCED FILTER to Filter Duplicate Values in Excel
Another way to filter the duplicates is using the Advanced Filter. The Advanced Filter can filter out data on different criteria.
Usage Guide
Step_1: Select a particular range of values that you want to filter.
I am choosing the Artist (Column B) and Year (Column C) columns for that.
Step_2: Click on the Data tab from the ribbon.
Step_3: Under the Sort & Filter group, hit the Advanced option.
You will see the Advanced Filter Dialog box will appear.
Step_4: Choose either Filter the list, in-place, or Copy to another location as an Action type.
- Filter the list, in-place will filter out the duplicate values in that table.
I have chosen the Filter the list, in-place.
- The Copy to another location is going to keep a copy of the values in an alternative location.
Excel will automatically mention the cell range in the List range bar.
Step_5: Keep the Criteria range empty.
Step_6: The Copy to box will seem default if you choose the Filter the list, in-place option. But you have to fill up the Copy to box if you choose the Copy to another location option.
Step_7: Give a tick mark on the Unique record only check box.
Step_8: Hit OK.
Final Result >
Even if I chose two columns, you can see the duplicate valued rows are gone now. It’s because my selected columns contained repeated values. So, the whole dataset had to filter out.
5 Ways to Find Matching Values in Two Worksheets in Excel
How to Find Similar Text in Two Columns in Excel (5 Ways)
Apply PIVOT TABLE to Filter Duplicate Values in Excel
Pivot Table is sort of an analytics tool. One of its main functions is to count the number of items and tell how many similar items belong inside the data range.
First, I am going to show you using Pivot Table to find duplicate values for a single-column dataset. And then I will show the instruction steps for a multiple-column dataset.
Here is a list of a company’s sales records. The table is one-columned.
Usage Guide
Step_1: Click on the Insert tab from the ribbon.
Step_2: Under the Tables group, select the PivotTable drop-down menu.
Step_3: From there, click on From Table/Range command.
A dialog box named PivotTable from table or range will pop up.
Step_4: Click on Column A to select the range.
Step_5: Choose the Existing Worksheet option.
You will see the Location box will become active.
Step_6: Take your mouse cursor on a suitable spare cell for the Pivot Table’s Location and click on it.
I chose the C5 cell.
Step_7: Hit OK.
A window called PivotTable Fields will come up on the right side of your worksheet.
Step_8: Mark the Sales check box.
The Sales column will automatically shift in the Rows section.
Step_9: Drag the Sales check box inside the Values section from above.
The Values command will add the total numbers of identical values.
Final Result
All the repeated values are gone now in a separate table with the cell repetition counts.
5+ Formulas to Find Duplicates in One Column in Excel
How to Compare Rows in Excel for Duplicates (7 Ways)
Usage Guide
Step_1: Select the entire data table.
Step_2: Click on the Insert tab.
Step_3: Under the Tables group, select the PivotTable command.
A dialog box named PivotTable from table or range will pop up.
Step_4: Choose the Existing worksheet point from Choose where you want the Pivot Table to be placed bar.
Step_5: Click on the Locations bar, and now select an empty cell beside the table.
I selected the F2 cell for Pivot Table Location.
Step_6: Press OK from the box.
The PivotTable Fields window will come up on the right side of your worksheet.
Step_7: Mark all the checkboxes (Album, Artist, Year, Sales) to add them inside the Rows section.
Step_8: Drag the checkboxes inside the Values section one by one from above.
Step_9: Now hit the Design tab.
Step_10: Under the Layout group, click on the Report Layout drop-down.
Step_11: Select the Show in Tabular Form, and the Repeat All Item Labels command.
Step_12: Under the Layout group, hit the Subtotals drop-down menu.
Step_13: Select the Do Not Show Subtotals command.
Final Result >
The Pivot Table can filter out the duplicate values from both the single-column data table and the multiple-columned data tables. It will show the results in a separate table.
Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]
How to Remove Duplicates in Excel [13 + Different Methods]
Use POWER QUERY EDITOR to Filter Duplicate Values in Excel
Power Query is known for transforming data differently. Power Query Editor can also do the work of filtering and erasing identical values.
Usage Guide
Step_1: Select the entire data table (CTRL+A).
Step_2: Click on the Data tab from the ribbon.
Step_3: Under the Get & Transform group, hit the From Table option.
The Create Table dialog box will come along on the screen. As I have already selected the cell range, so ‘Where is the data for your table?’ box is filled up already.
Remember to tick mark the My data has headers box.
Step_4: Hit OK.
The Power Query Editor window will show up.
Step_5: Take the mouse cursor on a column header and select it.
The selected column will turn green color.
Step_6: Put the cursor on the column header (Album) again and right-click on it.
Step_7: Select Remove Duplicates.
Now the identical data are gone from the table.
Step_8: Close the Power Query Editor window. Click on Keep in the window.
Usage Guide
Step_1: Click on the small icon on the top-left corner of the table in the Power Query Editor window.
Step_2: Select Remove Duplicates.
If you want to keep only the identical values from the table, you can choose the Keep Duplicates option from the drop-down list.
Final Result
You will see the resulting table has no similar values now and got a new decorative Cell Style automatically. You can change the cell colors later if you like.
Note: You may have noticed the Table.Distinct function when you entered the Power Query Editor window. So, you can filter out the duplicates by applying the Table.Distinct function straight away. But personally, I prefer the followed steps I showed you above.
Use CONCATENATE Function and COUNTIFS Function to Filter Duplicate Values in Excel
You will need two additional columns to use the CONCATENATE function and COUNTIFS functions to extract the unique values from a list.
I am going to put together only two columns in the formula. Two columns are enough to be assured of the similarities of values. But you can compile all the columns in the formula if you wish. In this method, I will be using the CONCATENATE and COUNTIFS functions.
Syntax
=CONCATENATE(text1, text2, …)=COUNTIFS(criteria_range1, criteria1, [criteria_range2, …)
Usage Guide
Step_1: Insert two new columns beside the data table (Column E, Column F).
I named the columns Artist & Year and Counts.
Step_2: Then copy the following formula in the top cell (E2) of Column E:
=CONCATENATE(B2&C2)
Formula Breakdown
- B2 is the lookup value which is the top cell of the column Artist.
- C2 is the lookup value which is the top cell of the column Year.
Step_3: Press ENTER.
Step_4: Now take the cursor to the bottom-right corner of cell E2. The Fill Handle icon will appear.
Step_5: Double-click on the icon to copy the formula up to cell E14.
Step_6: Then copy the following formula in the top cell (F2) of Column F:
=COUNTIFS(E2:E14,E2)
Formula Breakdown
- E2:E14 is the selected range of cells.
- E2 is the criteria cell.
Step_7: Press ENTER.
Step_8: Put the mouse cursor on the Fill Handle icon and double-click on the icon to copy the formula up to cell F14.
Step_9: Click on the Data tab.
Step_10: Under the Sort & Filter group, click on the Filter command.
A small arrow, Filter drop-down icon, will come along beside all the headers.
Step_11: Click on the Filter drop-down icon of the Counts header (F1).
You will see all the checkboxes of values are selected.
Step_12: Unmark the box of value:2.
Step_13: Hit OK.
Final Result >
Removing all the cells having value:2 has erased the duplicate cells. Having a value of 1 means, they are all unique values.
How to Find Duplicates in Two Columns in Excel (7 Methods)
Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]
Conclusion
I have discussed all the easy ways to find duplicate values in Excel. I think now you can smoothly filter out the identical values and remove them. Feel free to share your comments about this article. Also, check our other articles to learn more about Microsoft Excel.
