How to Find Duplicates in Excel

Using Conditional Formatting

1. How to Find Duplicates in Excel Picture 1
   Open your original file. The first thing you'll need to do is select all data you wish to examine for duplicates.
2. How to Find Duplicates in Excel Picture 2
   Click the cell in the upper left-hand corner of your data group. This begins the selecting process.
3. How to Find Duplicates in Excel Picture 3
   Hold down the ⇧ Shift key and click the final cell. Note that the final cell should be in the lower right-hand corner of your data group. This will select all of your data.

   1. You can do this in any order (e.g., click the lower right-hand box first, then highlight from there).
4. How to Find Duplicates in Excel Picture 4
   Click on "Conditional Formatting." It can be found in the "Home" tab/ribbon of the toolbar (in many cases, under the "Styles" section).^[1] Clicking it will prompt a drop-down menu.
5. How to Find Duplicates in Excel Picture 5
   Select "Highlight Cells Rules," then "Duplicate Values." Make sure your data is still highlighted when you do this. This will open a window with customization options in another drop-down menu.^[2]
6. How to Find Duplicates in Excel Picture 6
   Select "Duplicate Values" from the drop-down menu.

   1. If you instead wish to display all unique values, you can select "Unique" instead.
7. How to Find Duplicates in Excel Picture 7
   Choose your highlight color. The highlight color will designate duplicates. The default is light red with dark red text.^[3]
8. How to Find Duplicates in Excel Picture 8
   Click "OK" to view your results.
9. How to Find Duplicates in Excel Picture 9
   Select a duplicate's box and press Delete to delete it. You won't want to delete these values if each piece of data represents something (e.g., a survey).

   1. Once you delete a one-time duplicate, its partner value will lose its highlight.
10. How to Find Duplicates in Excel Picture 10
    Click on "Conditional Formatting" again. Whether you deleted your duplicates or not, you should remove the highlight formatting before exiting the document.
11. How to Find Duplicates in Excel Picture 11
    Select "Clear Rules," then "Clear Rules from Entire Sheet" to clear formatting. This will remove the highlighting around any duplicates you didn't delete.^[4]

    1. If you have multiple sections of your spreadsheet formatted, you can select a specific area and click "Clear Rules from Selected Cells" to remove their highlighting.
12. How to Find Duplicates in Excel Picture 12
    Save your document's changes. If you're satisfied with your revisions, you have successfully found and deleted duplicates in Excel!