How to Find Duplicates in Excel

When working with a Microsoft Excel spreadsheet with lots of data, you'll probably encounter duplicate entries. Microsoft Excel's Conditional Formatting feature shows you exactly where duplicates are, while the Remove Duplicates feature...

Method 1 of 2:

Using Conditional Formatting

  1. Picture 1 of How to Find Duplicates in Excel
    Open your original file. The first thing you'll need to do is select all data you wish to examine for duplicates.
  2. Picture 2 of How to Find Duplicates in Excel
    Click the cell in the upper left-hand corner of your data group. This begins the selecting process.
  3. Picture 3 of How to Find Duplicates in Excel
    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. Picture 4 of How to Find Duplicates in Excel
    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. Picture 5 of How to Find Duplicates in Excel
    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. Picture 6 of How to Find Duplicates in Excel
    Select "Duplicate Values" from the drop-down menu.
    1. If you instead wish to display all unique values, you can select "Unique" instead.
  7. Picture 7 of How to Find Duplicates in Excel
    Choose your highlight color. The highlight color will designate duplicates. The default is light red with dark red text.[3]
  8. Picture 8 of How to Find Duplicates in Excel
    Click "OK" to view your results.
  9. Picture 9 of How to Find Duplicates in Excel
    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. Picture 10 of How to Find Duplicates in Excel
    Click on "Conditional Formatting" again. Whether you deleted your duplicates or not, you should remove the highlight formatting before exiting the document.
  11. Picture 11 of How to Find Duplicates in Excel
    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. Picture 12 of How to Find Duplicates in Excel
    Save your document's changes. If you're satisfied with your revisions, you have successfully found and deleted duplicates in Excel!
Method 2 of 2:

Using Excel's Remove Duplicates Feature

  1. Picture 13 of How to Find Duplicates in Excel
    Open your original file. The first thing you'll need to do is select all data you wish to examine for duplicates.
  2. Picture 14 of How to Find Duplicates in Excel
    Click the cell in the upper left-hand corner of your data group. This begins the selecting process.
  3. Picture 15 of How to Find Duplicates in Excel
    Hold down the ⇧ Shift key and click the final cell. The final cell is 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. Picture 16 of How to Find Duplicates in Excel
    Click on the "Data" tab in the top section of the screen.
  5. Picture 17 of How to Find Duplicates in Excel
    Find the "Data Tools" section of the toolbar. This section includes tools to manipulate your selected data, including the "Remove Duplicates" feature.
  6. Picture 18 of How to Find Duplicates in Excel
    Click "Remove Duplicates." This will bring up a customization window.
  7. Picture 19 of How to Find Duplicates in Excel
    Click "Select All." This will verify all of your columns have been selected.[5]
  8. Picture 20 of How to Find Duplicates in Excel
    Check any columns you wish to use this tool on. The default setting has all columns checked.
  9. Picture 21 of How to Find Duplicates in Excel
    Click the "My data has headers" option, if applicable. This will prompt the program to label the first entry in each column as a header, leaving them out of the deletion process.
  10. Picture 22 of How to Find Duplicates in Excel
    Click "OK" to remove duplicates. When you are satisfied with your options, click "OK". This will automatically remove any duplicate values from your selection.
    1. If the program tells you that there aren't any duplicates--especially if you know there are--try placing a check next to individual columns in the "Remove Duplicates" window. Scanning each column one at a time will resolve any errors here.
  11. Picture 23 of How to Find Duplicates in Excel
    Save your document's changes. If you're satisfied with your revisions, you have successfully deleted duplicates in Excel!
Update 05 March 2020
clickdataduplicatesselect

You should read it

Maybe you are interested

Category

System

Mac OS X

Hardware

Game

Tech info

Technology

Science

Life

Application

Electric

Program

Mobile