You are viewing the article How to Find Duplicate Data in Excel at Tnhelearning.edu.vn you can quickly access the necessary information in the table of contents of the article below.
X
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 10,898 times.
When working on Microsoft Excel spreadsheets with lots of data, it is likely that you will encounter duplicate values. Microsoft Excel’s Conditional Formatting feature will correctly display duplicate locations, while the Remove Duplicates action will remove those items. Reviewing and removing duplicates ensures the accuracy of your data and presentation.
Steps
Use Conditional Formatting
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/03/Find-Duplicates-in-Excel-Step-1-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-1-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/03/Find-Duplicates-in-Excel-Step-1- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-1-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Open the original file. The first thing you need to do is select all the data that you want to check for duplicates.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/1b/Find-Duplicates-in-Excel-Step-2-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-2-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/1b/Find-Duplicates-in-Excel-Step-2- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-2-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the box in the upper left corner of the data group to begin the selection process.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/37/Find-Duplicates-in-Excel-Step-3-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-3-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/37/Find-Duplicates-in-Excel-Step-3- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-3-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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).
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/52/Find-Duplicates-in-Excel-Step-5-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-5-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/52/Find-Duplicates-in-Excel-Step-5- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-5-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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). [1] X Research Source A drop-down menu will appear.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/36/Find-Duplicates-in-Excel-Step-6-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-6-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/36/Find-Duplicates-in-Excel-Step-6- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-6-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select “Highlight Cells Rules” and then select “Duplicate Values”. Your data should be highlighted. Another window will open with options in the drop-down menu. [2] X Research Source
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/fa/Find-Duplicates-in-Excel-Step-7-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-7-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/fa/Find-Duplicates-in-Excel-Step-7- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-7-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select “Duplicate Values” from the drop-down menu.
- If you want to display only one value, select “Unique”.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/16/Find-Duplicates-in-Excel-Step-8-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-8-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/16/Find-Duplicates-in-Excel-Step-8- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-8-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select the highlight color. The highlight color will represent duplicate values. By default, the text will be dark red and light red. [3] X Research Sources
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/08/Find-Duplicates-in-Excel-Step-9-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-9-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/08/Find-Duplicates-in-Excel-Step-9- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-9-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click “OK” to see the results.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b8/Find-Duplicates-in-Excel-Step-10-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-10-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b8/Find-Duplicates-in-Excel-Step-10- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-10-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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 unique value that remains will no longer be highlighted.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/9e/Find-Duplicates-in-Excel-Step-11-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-11-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/9e/Find-Duplicates-in-Excel-Step-11- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-11-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4f/Find-Duplicates-in-Excel-Step-12-Version-7.jpg/v4-728px-Find-Duplicates- in-Excel-Step-12-Version-7.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4f/Find-Duplicates-in-Excel-Step-12- Version-7.jpg/v4-728px-Find-Duplicates-in-Excel-Step-12-Version-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select “Clear Rules”, then select “Clear Rules from Entire Sheet”. All markup of duplicate data that you do not delete will be removed. [4] X Research Sources
- If the spreadsheet has a lot of formatted items, you can select a specific area and then click “Clear Rules from Selected Cells” to remove the highlight.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/a5/Find-Duplicates-in-Excel-Step-13-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-13-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/a5/Find-Duplicates-in-Excel-Step-13- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-13-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/56/Find-Duplicates-in-Excel-Step-14-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-14-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/56/Find-Duplicates-in-Excel-Step-14- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-14-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Open the original file. The first thing you need to do is select all the data that you want to check for duplicates.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d2/Find-Duplicates-in-Excel-Step-15-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-15-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d2/Find-Duplicates-in-Excel-Step-15- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-15-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the box in the upper left corner of the data group to begin the selection process.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/ec/Find-Duplicates-in-Excel-Step-16-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-16-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ec/Find-Duplicates-in-Excel-Step-16- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-16-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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).
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/ed/Find-Duplicates-in-Excel-Step-17-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-17-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/ed/Find-Duplicates-in-Excel-Step-17- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-17-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click the “Data” tab at the top of the screen.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f2/Find-Duplicates-in-Excel-Step-18-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-18-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f2/Find-Duplicates-in-Excel-Step-18- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-18-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Find the “Data Tops” item in the toolbar. This section includes tools for managing selected data, including the “Remove Duplicates” feature.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/25/Find-Duplicates-in-Excel-Step-19-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-19-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/25/Find-Duplicates-in-Excel-Step-19- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-19-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click “Remove Duplicates. “ A customization window will open.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/7d/Find-Duplicates-in-Excel-Step-20-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-20-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/7d/Find-Duplicates-in-Excel-Step-20- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-20-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click “Select All”. All columns in the document will be selected. [5] X Research Sources
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b6/Find-Duplicates-in-Excel-Step-21-Version-6.jpg/v4-728px-Find-Duplicates- in-Excel-Step-21-Version-6.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b6/Find-Duplicates-in-Excel-Step-21- Version-6.jpg/v4-728px-Find-Duplicates-in-Excel-Step-21-Version-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Check the columns where you want to apply the tool. By default, all columns will be selected.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/12/Find-Duplicates-in-Excel-Step-22-Version-5.jpg/v4-728px-Find-Duplicates- in-Excel-Step-22-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/12/Find-Duplicates-in-Excel-Step-22- Version-5.jpg/v4-728px-Find-Duplicates-in-Excel-Step-22-Version-5.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b4/Find-Duplicates-in-Excel-Step-23-Version-5.jpg/v4-728px-Find-Duplicates- in-Excel-Step-23-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b4/Find-Duplicates-in-Excel-Step-23- Version-5.jpg/v4-728px-Find-Duplicates-in-Excel-Step-23-Version-5.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
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.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/66/Find-Duplicates-in-Excel-Step-24-Version-5.jpg/v4-728px-Find-Duplicates- in-Excel-Step-24-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/66/Find-Duplicates-in-Excel-Step-24- Version-5.jpg/v4-728px-Find-Duplicates-in-Excel-Step-24-Version-5.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight” :546,”licensing”:”<div class=”mw-parser-output”></div>”}
Save changes to the document. If you are satisfied with the results, then you have succeeded in finding and removing duplicate data in Excel!
Advice
- You can also find duplicates by installing third-party add-ons. Some of these utilities improve on Excel’s conditional formatting and allow you to use multiple colors to identify similar values.
- Duplicating data removal becomes useful when you need to review attendee lists, address books or similar documents.
Warning
- Don’t forget to save the document when you’re done!
X
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 10,898 times.
When working on Microsoft Excel spreadsheets with lots of data, it is likely that you will encounter duplicate values. Microsoft Excel’s Conditional Formatting feature will correctly display duplicate locations, while the Remove Duplicates action will remove those items. Reviewing and removing duplicates ensures the accuracy of your data and presentation.
Thank you for reading this post How to Find Duplicate Data in Excel at Tnhelearning.edu.vn You can comment, see more related articles below and hope to help you with interesting information.
Related Search: