How to Remove Duplicates in Excel

Keep an accurate ledger with these easy tricks

Erasing zeros with correction fluid on white background

voineatudor/Getty Images

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.

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

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.

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

    Data that contains duplicate rows in an Excel worksheet

    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.

  2. Select the Home tab.

  3. In the Styles group, select Conditional Formatting.

    Selected data to use Conditional Formatting in Excel to dedupe Excel
  4. Select Highlight Cells Rules > Duplicate Values.

    Conditional formatting to highlight cells that contain duplicate rows in Excel
  5. In the Duplicate Values dialog box, select the values with drop-down arrow and choose the fill and text color to highlight duplicate rows.

    Duplicate Values dialog box in Excel to select highlight for conditional formatting of duplicate data
  6. Select OK.

    Duplicate Values dialog box in Excel showing how to delete duplicates in Excel
  7. Cells that have a duplicate value in other cells are highlighted.

    Highlighting used in conditional formatting showing how to get rid of duplicates in Excel
  8. 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.

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

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

  1. Select any cell inside the dataset.

    Excel worksheet with duplicate rows
  2. Select the Data tab.

  3. In the Data Tools group, select Remove Duplicates.

    The Data tab in Excel showing the Remove Duplicates tool
  4. In the Remove Duplicates dialog box, choose Select All.

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

  6. Select OK.

    The Remove Duplicates dialog box showing how to delete duplicates in Excel
  7. In the message that shows the number of duplicate values that were removed and the number of unique values that remain, select OK.

    Excel showing the number of duplicates values that were found and deleted
  8. All identical rows are removed except for the first instance of a duplicate row.

    Result of the Remove Duplicates tool in Excel
  9. 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.

  1. Select any cell inside the dataset.

    Data selected to remove duplicates found only is specified columns in Excel
  2. Select the Data tab.

  3. In the Data Tools group, select Remove Duplicates.

    The Remove Duplicates tool in Excel to dedupe an Excel worksheet
  4. In the Remove Duplicates dialog box, choose Unselect All.

  5. 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.

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

  7. Select OK.

    The Remove Duplicates dialog box to remove duplicate rows in Excel
  8. In the confirmation dialog box, select OK.

    Microsoft Excel confirmation of the number of duplicates removed in Excel
  9. Excel removes all rows containing the same information in the selected columns except for the first instance of a duplicate record.

    The result of the Remove Duplicates tool in Excel to delete rows based on duplicates in selected columns

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.

To filter an Excel worksheet to display only unique values:

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

    Selected data to filter for duplicates in Excel
  2. Select the Data tab.

  3. In the Sort & Filter group, select Advanced.

    Using Advanced Filter to get rid of duplicates in Excel
  4. 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.

  5. Select OK.

    The Advanced Filter dialog box showing how to filter for unique values only in Excel
  6. The duplicates are removed.

    The result of the Advanced Filter to remove duplicate rows in Excel
  7. To clear the filter and display the original data, select the Home tab > Sort & Filter > Clear.