Before data in an Excel worksheet can be analyzed and visualized, it must be cleaned up. One of these cleanup tasks is to find and remove duplicate data. There are several ways to accomplish this cleanup task. Learn how to delete duplicates in Excel using the Remove Duplicates and Filter tools. Then, choose the one that works best for you and the data in your worksheet.

Highlight Duplicate Data in an Excel Worksheet

If you only want to view duplicate data in a worksheet, highlight the data using conditional formatting. Then, if you decide you don’t need the data, delete the duplicate rows.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013.

  • Highlight the data you want to search for duplicates. Do not include headers.
  • To highlight data in a worksheet, select the upper-left cell of data, press and hold Shift, then select the lower-right cell of data.
  • Select the Home tab.
  • In the Styles group, select Conditional Formatting.
  • Select Highlight Cells Rules > Duplicate Values.
  • In the Duplicate Values dialog box, select the values with drop-down arrow and choose the fill and text color to highlight duplicate rows.
  • Select OK.
  • Cells that have a duplicate value in other cells are highlighted.
  • To remove duplicate rows in Excel, select a highlighted row, select the Home tab, then select Delete > Delete Sheet Rows. Or, use the Remove Duplicates tool or the Filter tool to dedupe Excel.

Remove Duplicate Rows in Excel Quickly

Excel can automatically remove rows of data that have identical information in each column. This is a quick way to clean up a worksheet.

Highlight the data you want to search for duplicates. Do not include headers.

To highlight data in a worksheet, select the upper-left cell of data, press and hold Shift, then select the lower-right cell of data.

Select the Home tab.

In the Styles group, select Conditional Formatting.

Select Highlight Cells Rules > Duplicate Values.

In the Duplicate Values dialog box, select the values with drop-down arrow and choose the fill and text color to highlight duplicate rows.

Select OK.

Cells that have a duplicate value in other cells are highlighted.

To remove duplicate rows in Excel, select a highlighted row, select the Home tab, then select Delete > Delete Sheet Rows. Or, use the Remove Duplicates tool or the Filter tool to dedupe Excel.

To use the Remove Duplicates data tool to remove duplicates rows from an entire worksheet:

Removing duplicate rows permanently deletes the data. Make a copy of the worksheet before making any changes.

  • Select any cell inside the dataset.
  • Select the Data tab.
  • In the Data Tools group, select Remove Duplicates.
  • In the Remove Duplicates dialog box, choose Select All.
  • Select the My data has headers check box if the worksheet has column labels.
  • Select OK.
  • In the message that shows the number of duplicate values that were removed and the number of unique values that remain, select OK.
  • All identical rows are removed except for the first instance of a duplicate row.
  • If the duplicate rows weren’t deleted as you expected, press Ctrl+Z to undo the changes to the worksheet.

Eliminate Duplicates in Excel with the Same Value in Specified Columns

You can also use the Remove Duplicates tool to delete rows with the same value in specified columns.

Select any cell inside the dataset.

Select the Data tab.

In the Data Tools group, select Remove Duplicates.

In the Remove Duplicates dialog box, choose Select All.

Select the My data has headers check box if the worksheet has column labels.

In the message that shows the number of duplicate values that were removed and the number of unique values that remain, select OK.

All identical rows are removed except for the first instance of a duplicate row.

If the duplicate rows weren’t deleted as you expected, press Ctrl+Z to undo the changes to the worksheet.

  • Select any cell inside the dataset.
  • Select the Data tab.
  • In the Data Tools group, select Remove Duplicates.
  • In the Remove Duplicates dialog box, choose Unselect All.
  • Select the check box next to each column you want to search for duplicates. The data in all the selected columns must be duplicated for the row to be deleted.
  • If your worksheet has column headings, select the My data has headers check box.
  • Select OK.
  • In the confirmation dialog box, select OK.
  • Excel removes all rows containing the same information in the selected columns except for the first instance of a duplicate record.

How to ‘Delete’ Duplicates in Excel with Filters

Another way to remove duplicate data is to filter the data for unique values. Using this method doesn’t delete the duplicate rows, the duplicate values are temporarily hidden.

In the Remove Duplicates dialog box, choose Unselect All.

Select the check box next to each column you want to search for duplicates. The data in all the selected columns must be duplicated for the row to be deleted.

If your worksheet has column headings, select the My data has headers check box.

In the confirmation dialog box, select OK.

Excel removes all rows containing the same information in the selected columns except for the first instance of a duplicate record.

To filter an Excel worksheet to display only unique values:

  • Select any cell inside the dataset to filter the entire worksheet. Or, select the data to be filtered.
  • Select the Data tab.
  • In the Sort & Filter group, select Advanced.
  • In the Advanced Filter dialog box, select the Unique records only check box.
  • To save the filtered results to another worksheet, select Copy to another location.
  • Select OK.
  • The duplicates are removed.
  • To clear the filter and display the original data, select the Home tab > Sort & Filter > Clear.

Select any cell inside the dataset to filter the entire worksheet. Or, select the data to be filtered.

In the Sort & Filter group, select Advanced.

In the Advanced Filter dialog box, select the Unique records only check box.

To save the filtered results to another worksheet, select Copy to another location.

The duplicates are removed.

To clear the filter and display the original data, select the Home tab > Sort & Filter > Clear.

Get the Latest Tech News Delivered Every Day