1. Use the Remove Duplicates Button
Excel comes with a native button to remove duplicate values.
2. Find Duplicate Data Using Conditional Formatting
This method of removing duplicates is based on conditional formatting and will help you find duplicate values without deleting them.
3. Remove Duplicates Using Conditional Filter
With the duplicates highlighted, you can either use filters to remove them or use the method shown above. Now your sheet will only show unique values. Be careful as this method removes all the duplicate values including the first one. To copy the unique values, select the data set and use the Alt + ; keyboard shortcut to select only the visible rows, then use the Ctrl + C shortcut to copy the visible rows and paste them wherever required.
4. Find Duplicate Values Using a Formula
Duplicates in a column can also be found using Excel formulas. The most basic formula for detecting duplicate entries is =COUNTIF(Range, Criteria) >1, where the range can be the entire column or a subset of rows. Create a new column and enter one of the below commands in the new column.
Use the formula =COUNTIF(D:D, D2) >1 for a full column, where D is the column name and D2 is the topmost cell.For selected rows, use the formula =COUNTIF($D$2:$D$10, $D2) >1, where the first set represents the selected rows, and D2 is the topmost cell. Please note that the range must be preceded by the dollar ($) sign, otherwise the cell reference will change when you drag the formula.
Drag the fill handle to use the command in the other rows. The formula will show “True” for duplicate values and “False” for unique values. If you want to display some other text than True or False, you must enclose the COUNTIF formula in the IF function. So the formula will become =IF(COUNTIF($D$2:$D$10, $D2) > 1, “Duplicate”, “Single”). If you want unique values to show a blank cell, use the formula =IF(COUNTIF($D$2:$D$10, $D2) > 1, “Duplicate”, “”).
5. Count Number of Duplicates Using a Formula
You can use the above formula without the > 1 text to count the number of duplicates in a column. The two formulas will become =COUNTIF($D$2:$D$10, $D2) and =COUNTIF(D:D, D2). Enter in the new column for it to display how many times each item appears in the data.
6. Remove Duplicate Values Using a Formula
Once you have found the duplicate values or the duplicate count, use the filter method to remove duplicates and retain unique values.
7. Remove Duplicate Data Using Advanced Filters
8. Remove Excel Duplicates Using Power Query
Power Query also helps to remove duplicate values in Excel as shown below.
9. Using Pivot Tables
You can use Pivot tables to display only the unique values in your data, thus removing the duplicate entries. You will need to format the PivotTable to show it in a tabular form. For that, go to the Data tab and perform the following steps: You will get a Pivot table with unique values in a tabular form.
Excel in Excel
Read on to learn how to merge cells, columns, and rows to create a new set of data. And in case your worksheet contains important data, find out how to password protect your Excel workbook.
