How to Remove Duplicates in Excel


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.

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

Data that contains duplicate rows in an Excel worksheet2. Select the Home tab.

3. In the Styles group, select Conditional Formatting.

Selected data to use Conditional Formatting in Excel to dedupe Excel4. 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 data6. Select OK.

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

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