Pages

How to Find Duplicates in Excel

8/30/2016

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 will delete them for you. Viewing and deleting duplicates ensures that your data and presentation are as accurate as possible.

EditSteps

EditUsing Conditional Formatting

  1. Open your original file. The first thing you'll need to do is select all data you wish to examine for duplicates.
    Find Duplicates in Excel Step 1 Version 7.jpg
  2. Click the cell in the upper left-hand corner of your data group. This begins the selecting process.
    Find Duplicates in Excel Step 2 Version 7.jpg
  3. Hold down the 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.
    Find Duplicates in Excel Step 3 Version 7.jpg
    • You can do this in any order (e.g., click the lower right-hand box first, then highlight from there).
  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.
    Find Duplicates in Excel Step 5 Version 7.jpg
  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]
    Find Duplicates in Excel Step 6 Version 7.jpg
  6. Select "Duplicate Values" from the drop-down menu.
    Find Duplicates in Excel Step 7 Version 6.jpg
    • If you instead wish to display all unique values, you can select "Unique" instead.
  7. Choose your highlight color. The highlight color will designate duplicates. The default is light red with dark red text.[3]
    Find Duplicates in Excel Step 8 Version 7.jpg
  8. Click "OK" to view your results.
    Find Duplicates in Excel Step 9 Version 7.jpg
  9. Select a duplicate's box and press to delete it. You won't want to delete these values if each piece of data represents something (e.g., a survey).
    Find Duplicates in Excel Step 10 Version 7.jpg
    • Once you delete a one-time duplicate, its partner value will lose its highlight.
  10. Click on "Conditional Formatting" again. Whether you deleted your duplicates or not, you should remove the highlight formatting before exiting the document.
    Find Duplicates in Excel Step 11 Version 7.jpg
  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]
    Find Duplicates in Excel Step 12 Version 7.jpg
    • 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. Save your document's changes. If you're satisfied with your revisions, you have successfully found and deleted duplicates in Excel!
    Find Duplicates in Excel Step 13 Version 6.jpg

EditUsing Excel's Remove Duplicates Feature

  1. Open your original file. The first thing you'll need to do is select all data you wish to examine for duplicates.
    Find Duplicates in Excel Step 14 Version 6.jpg
  2. Click the cell in the upper left-hand corner of your data group. This begins the selecting process.
    Find Duplicates in Excel Step 15 Version 6.jpg
  3. Hold down the 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.
    Find Duplicates in Excel Step 16 Version 6.jpg
    • You can do this in any order (e.g., click the lower right-hand box first, then highlight from there).
  4. Click on the "Data" tab in the top section of the screen.
    Find Duplicates in Excel Step 17 Version 6.jpg
  5. Find the "Data Tools" section of the toolbar. This section includes tools to manipulate your selected data, including the "Remove Duplicates" feature.
    Find Duplicates in Excel Step 18 Version 6.jpg
  6. Click "Remove Duplicates." This will bring up a customization window.
    Find Duplicates in Excel Step 19 Version 6.jpg
  7. Click "Select All." This will verify all of your columns have been selected.[5]
    Find Duplicates in Excel Step 20 Version 6.jpg
  8. Check any columns you wish to use this tool on. The default setting has all columns checked.
    Find Duplicates in Excel Step 21 Version 6.jpg
  9. 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.
    Find Duplicates in Excel Step 22 Version 5.jpg
  10. Click "OK" to remove duplicates. When you are satisfied with your options, click "OK". This will automatically remove any duplicate values from your selection.
    Find Duplicates in Excel Step 23 Version 5.jpg
    • 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. Save your document's changes. If you're satisfied with your revisions, you have successfully deleted duplicates in Excel!
    Find Duplicates in Excel Step 24 Version 5.jpg

EditTips

  • You can also identify duplicate values by installing a third-party add-in utility. Some of these utilities enhance Excel's conditional formatting feature to enable you to use multiple colors to identify duplicate values.
  • Deleting your duplicates comes in handy when reviewing attendance lists, address directories, or similar documents.

EditWarnings

  • Always save your work when you're done!

EditRelated wikiHows

EditSources and Citations


Cite error: <ref> tags exist, but no <references/> tag was found



Source: http://www.wikihow.com/Find-Duplicates-in-Excel