How to Find Duplicate Data in Excel

Use Conditional Formatting

How to Find Duplicate Data in Excel Picture 1

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

How to Find Duplicate Data in Excel Picture 2

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

How to Find Duplicate Data in Excel Picture 3

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

How to Find Duplicate Data in Excel Picture 4

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.

How to Find Duplicate Data in Excel Picture 5

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.

How to Find Duplicate Data in Excel Picture 6

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

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

How to Find Duplicate Data in Excel Picture 7

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

How to Find Duplicate Data in Excel Picture 8

Click "OK" to see the results.

How to Find Duplicate Data in Excel Picture 9

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.

How to Find Duplicate Data in Excel Picture 10

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.

How to Find Duplicate Data in Excel Picture 11

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.

How to Find Duplicate Data in Excel Picture 12

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

How to Find Duplicate Data in Excel Picture 13

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

How to Find Duplicate Data in Excel Picture 14

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

How to Find Duplicate Data in Excel Picture 15

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

How to Find Duplicate Data in Excel Picture 16

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

How to Find Duplicate Data in Excel Picture 17

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

How to Find Duplicate Data in Excel Picture 18

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

How to Find Duplicate Data in Excel Picture 19

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

How to Find Duplicate Data in Excel Picture 20

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

How to Find Duplicate Data in Excel Picture 21

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.

How to Find Duplicate Data in Excel Picture 22

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.

How to Find Duplicate Data in Excel Picture 23

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