Mastering the Art of Identifying Replicated Data Points in Excel Tutorial

Quick Links

Do you want to find and optionally delete the duplicate entries in your spreadsheets? If so, use Microsoft Excel’s conditional formatting option to highlight duplicates and then select an option to remove those items. We’ll show you how.

To find the duplicates, use conditional formatting that checks the duplicate values in your cells and highlights them in your chosen formatting . Then, optionally, you can ask Excel to remove all duplicates from your dataset, keeping only the unique values in it.

Highlight Duplicate Values in Excel

To highlight non-unique values, first, open your spreadsheet with Microsoft Excel.

In the spreadsheet, select the dataset which you want to check for duplicates. Make sure to include the column headers in your selection .

From Excel’s ribbon at the top, select the “Home” tab. Then, in the “Styles” section, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values.

You’ll see a “Duplicate Values” box. Here, click the first drop-down menu and choose “Duplicate.” Select the second drop-down menu and pick the formatting you want to use to highlight the duplicate entries. To use custom formatting, choose “Custom Format.”

Then, click “OK.”

On your spreadsheet, you’ll find that Excel has highlighted the duplicate entries in your selected formatting style.

And that’s how you easily spot non-unique records in your datasets.

Remove Duplicate Values in Excel

Excel offers a feature that automatically removes duplicate values from your datasets .

To use it, first, select your dataset in your Excel spreadsheet. Make sure to select your column headers as well.

In Excel’s ribbon at the top, click the “Data” tab. Then, from the “Data Tools” section, choose “Remove Duplicates” (it’s an icon with an “X” on it).

On the “Remove Duplicates” box, if you’ve included column headers in your selection, then enable the “My Data Has Headers” option. In the “Columns” section, make sure the correct column(s) are listed.

Then choose “OK.”