How to Find Duplicate Data in Excel

Use Conditional Formatting

Open the original file. The first thing you need to do is select all the data that you want to check for duplicates.

Click the box in the upper left corner of the data group to begin the selection process.

Hold down the ⇧ Shift key and click the last cell in the lower-right corner of the data group. All your data will be selected.

You can proceed in any order (such as clicking the bottom right box first, then starting the check).

Click "Conditional Formatting." The option is in the "Home" tab/ribbon of the toolbar (in many cases, you'll find it under the "Styles" section). A drop-down menu will appear.

Select "Highlight Cells Rules" and then select "Duplicate Values". Your data should be highlighted. Another window will open with the options in the drop-down menu.

Select "Duplicate Values" from the drop-down menu.

If you want to display only one value, select "Unique".

Select the highlight color. The highlight color will represent duplicate values. By default, the text will be dark red and light red.

Click "OK" to see the results.

Select the duplicate cell and press the Delete key to delete it. You should not discard if these values ​​represent certain data (such as survey numbers).

After you remove duplicate data, the only remaining value will no longer be highlighted.

Click "Conditional Formatting" again. Whether you decide to remove duplicate data or not, it's a good idea to disable formatting before closing the document.

Select "Clear Rules", then select "Clear Rules from Entire Sheet". All markings of duplicates that you do not delete will be removed.

If the spreadsheet has a lot of formatted entries, you can select a specific area and then click "Clear Rules from Selected Cells" to remove the highlight.

Save changes to the document. If you are satisfied with the results, then you have succeeded in finding and removing duplicate data in Excel!

Use Excel's Remove Duplicates feature

Open the original file. The first thing you need to do is select all the data that you want to check for duplicates.

Click the box in the upper left corner of the data group to begin the selection process.

Hold down the ⇧ Shift key and click the last cell in the lower-right corner of the data group. All your data will be selected.

You can proceed in any order (such as clicking the bottom right box first, then starting the check).

Click the "Data" tab at the top of the screen.

Find the "Data Tools" item in the toolbar. This section includes tools for managing selected data, including the "Remove Duplicates" feature.

Click "Remove Duplicates." A customization window will open.

Click "Select All". All columns in the document will be selected.

Check the columns where you want to apply the tool. By default, all columns will be selected.

Click the "My data has headers" option if you feel the need. The program will label the header for the first item in each column and exclude them from the deletion process.

Click "OK" to remove duplicate data. When you are satisfied with the options, click "OK". All duplicate values ​​will be removed from the selection.

If the program responds that there are no matching cells when you know for sure there are, check the box for each column in the "Remove Duplicates" window. Checking one column at a time fixes the error.

Save changes to the document. If you are satisfied with the results, then you have succeeded in finding and removing duplicate data in Excel!

Lesley Montoya

Update 24 February 2022