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.
2. Select the Home tab.
3. In the Styles group, select Conditional Formatting.
4. Select Highlight Cells Rules > Duplicate Values.
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.
6. Select OK.
7. Cells that have a duplicate value in other cells are highlighted.
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.