How to Find and Remove Duplicates in Excel

Duplicate values in Excel can be annoying, but fortunately, there are several methods for finding and removing them. We recommend making a backup copy of your Excel sheet before removing the duplicates. Let’s look at how to count, find, and remove duplicate values in Excel.

1. Use the Remove Duplicates Button

The quickest method to find and remove duplicates in Excel is to use Excel’s “Remove Duplicates” button. This method lets you search for duplicates based on data in one or more columns. It removes entire rows when duplicates are found.

Click any cell with data in it on your spreadsheet. This doesn’t have to be the cell, column, or row in which you would like to remove duplicates.

Select the “Data” tab, and click the “Remove Duplicates” button on the toolbar.

Check the column(s) in which you wish to remove duplicates. All columns are checked by default. Also, if your columns don’t have headings, uncheck the “My data has headers box” so that your first row is included. Click “OK” after you’ve selected the desired columns.

Excel gives you a message stating how many rows were deleted and how many rows remain. This method removes the entire row, not just the value. Press “OK” to accept the results and return to your spreadsheet.

If you don’t like the results, press Ctrl + Z to return the deleted values to your sheet.

2. Find Duplicate Data Using Conditional Formatting

If you prefer to review duplicates in Excel before deleting anything, try using conditional formatting. This finds duplicates in Excel but doesn’t delete them.

Select the column(s) or the entire sheet where you want to search for duplicate values.

Open the “Home” tab, and click the “Conditional Formatting” button.

Select “Highlight Cell Rules,” and choose “Duplicate Values.”

Choose the color you want duplicate values to appear in. You can also switch the “Rule Type” from “Duplicate Values” to “Unique Values” if you’d rather see unique values highlighted. Click “Done” when you’re finished.

Review the highlighted cells, and delete duplicates that you don’t want. In my example, I only want to delete rows where every value is duplicated.

3. Remove Duplicates Using Conditional Filter

If you want to make it easier to remove duplicates in Excel after using conditional formatting, use an Excel filter based on the color of the cell to only display duplicates or unique values. With this option, you’ll remove the entire row(s) manually or just the duplicate cells.

Set up conditional formatting based on the previous section.

Open the “Home” tab, select the “Sort & filter” button, and choose “Filter.”

Drop-down arrows will appear on the header of each column. Click the arrow, and select “Filter by Color.” Select the color of the duplicate cells to only view those. Or, select “No fill” to select only the unique values.

Please note that no duplicates (including the original value) are shown when you choose “No fill.” If you want to still see all values, select “Filter by Color” instead. This places either the colored cells or the cells with no fill at the top of the list.

For my example, I am sorting by the color of the duplicate cells. This allows you to review the duplicates and delete any rows or values you don’t want to keep. If you choose to show only the unique values, you can copy or move all the visible data into a new sheet. This will leave only duplicates behind.

When you are finished with data editing, click the drop-down arrow again, and choose “Clear Filter.”

If you want to remove the drop-down arrows, select “Sort & filter” from the toolbar, and choose “Filter.” This clears all filters.

Tip: you can do much more with Excel filters. For example, you can eliminate blank cells or remove extra spaces from a cell.

4. Find Duplicates in Excel With a Formula

Excel formulas can accomplish most any task on a spreadsheet, including finding duplicates. There are several different formulas you can use based on what you want to see. These are all based around COUNT functions. These formulas don’t remove duplicates. You must do that manually once they’re found. No matter which formula you use, the process is the same:

Create a new column on the same sheet. I labeled mine “Duplicates.”

Select the first empty cell in your new column. Enter your desired formula (see the various formulas below the steps) in the function/formula bar or in the cell itself.

If you want to extend the formula to other cells in the column, click and hold the small square at the bottom right of the cell that contains the formula. Pull downward to fill as many cells as you want.

Now that you know how to insert the formula, these are the best options to use:

• =COUNTIF(Range, Criteria) >1 – The range is your column, and the criteria is the topmost cell. This works well for finding duplicates in a single column. For example, to see duplicates based on column D only, you’d use =COUNTIF(D:D, D2) >1 . A result of “True” means duplicate, while “False” means unique.

• If you don’t want the cell reference to change as you drag it, use absolute cell references instead. For instance, I’d use =COUNTIF($D$2:$D$105, $D2) >1 to check the entire D column.
• If you want to show something other than True or False, enclose COUNTIF inside an IF function: =IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicate", " "). This will show “Duplicate” for duplicates and a blank cell for unique values. You can easily use any words you want, such as “Duplicate” and “Unique.”
• If you want to compare multiple columns at once, you’ll need to use absolute cell references and an IF statement, such as: =IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicate", "Unique") This formula will only show “Duplicate” if the values in D, E, and F are the same. If any of the three is unique in a row, “Unique” will display instead.
• If you want to show only the duplicate values and not the first occurrence of the value, try this instead: =IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique") This only shows “Duplicate” for the second or more occurrences.

5. Count the 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$105, $D2) and =COUNTIF(D:D, D2). Enter the new column for it to display how many times each item appears in the data.

A value of “1” means it’s a unique value. Anything else equals duplicates.

6. Remove Duplicate Values Using a Formula

Once you have found the duplicate values (Section 4) or the duplicate count (Section 5), use the filter method to remove duplicates and retain unique values.

Click anywhere in the column with your duplicate values or count. Go to “Home tab -> Sort & Filter -> Filter” to enable the filter drop-down box on the column header.

Click the drop-down arrow in your duplicate column.

Check the value(s) you want to keep, uncheck what you don’t want, and click “Apply.” Hide all duplicates by only selecting “Unique,” “False,” or whatever your label for unique values was for the duplicate values method, and “1” for Counting duplicates. View just the duplicates by unchecking the unique identifiers.

Select the visible rows by pressing Ctrl + C. If, for any reason, this selects all rows, including hidden rows, use the Alt + ; shortcut instead.

Copy the unique values or duplicates (whichever you chose to show) to another sheet. Delete those values from the original sheet.

Go to “Sort & Filter -> Filter.” Clicking “Filter” will remove the filter and show all the remaining duplicates or unique values.

From here, go through the duplicates and remove the cells or rows you no longer want. Remember, the original occurrence is listed with the duplicate values.

7. Remove Duplicate Data Using Advanced Filters

So far, we’ve only used basic filters, but you can also find and remove duplicates in Excel using advanced filters.

Select the column you want to filter. Open the “Data” tab, and click “Advanced.”

3. In the “Advanced Filter” box, choose “Filter the list, in-place.” This hides the duplicates in the same data set. Later, you can manually copy-paste the unique values to a different place in the same sheet or to a different sheet.

If you haven’t already selected your column(s), select them. They will automatically show up in the “List range” field. Leave “Criteria range” blank.

Check the box next to “Unique records only,” and hit “OK.”

That will show unique values in your data. Use the Alt + ; shortcut to select visible rows, only if you want to perform any action on them, such as copying to another location.

On the contrary, select “Copy to another location” in the Advanced Filter box if you want Excel to automatically copy the unique values to a different place in the same sheet.

Select the “List range” first. You can keep the “Criteria range” blank.

Click once on the “Copy to” field, and select the rows on your sheet where you want to copy the unique data.

Make sure the box next to “Unique records only” is checked.

This method hides the entire duplicate row, not just values. It also hides the original occurrence of the duplicate value, not just the duplicate versions. You’ll want to view the duplicates to extract the original values.

8. Remove Excel Duplicates Using Power Query

Power Query can remove duplicate values in Excel, as shown below. This method removes the entire duplicate row.

Open the “Data” tab, and select “From Table/Range.”

All your data should automatically be selected. If not, enter the entire sheet’s range in the “Create Table” box. Click “OK” when you’re done.

The “Power Query” editor will open. Select the columns, and right-click on the selected column header. Choose “Remove duplicates” from the menu. This removes all duplicates in the Power Query editor. In this tool, the original occurrence remains in the list.

If you want to remove duplicates from the entire table, click on the “Table” button in the top-left corner, and choose “Keep duplicates” to show only the duplicate entries and remove the rest.

This shows all duplicates, including the original entries. To remove the extras and leave just the original entries, click “Table” again, and select “Remove duplicates.”

Click on “Close and Load” at the top to open the table in a new tab of the same workbook. Only the results of your query will load. This doesn’t remove anything from the original sheet.

9. Using a PivotTable

Use PivotTables to display only the unique values in your data, thus removing the duplicate entries. This doesn’t actually remove any rows or values from your original data; it just shows you the unique values.

Open the “Insert” tab, and select “PivotTable.” Select “From Table/Range.”

Select the table or range where you want to hide duplicate values. Your entire set of values should be selected by default. If not, press Ctrl + A to select everything. Or, use your mouse to select a custom range.

Select whether the PivotTable should be placed in the same worksheet or in a new worksheet. If it’s an existing worksheet, enter or select the cell you want to use in the “Location” box. Click “OK.”

In the PivotTable sidebar, check or drag the columns you want to extract unique values from to the “Rows” section.

You will need to format the PivotTable to show it in a tabular form. For that, go to the “Design” tab, and perform the following steps:

Select “Report Layout -> Show in Tabular Form.”

Select “Subtotals -> Do not show subtotals.”

Select “Report Layout -> Repeat All Item Labels.”

Select “Grand Totals -> Off for Rows and Columns.”

You’ll get a PivotTable with unique values in a tabular form.

With so many ways to find and remove duplicates in Excel, there’s no reason to do it again manually. Pick your favorite method, and run with it. While you’re saving time with duplicate issues, try these Microsoft Excel tips and tricks to save yourself even more time. Also, learn how to further clean up your data in Excel.

Image credit: Pixabay. All screenshots by Crystal Crowder.