This tutorial teaches you how to find duplicates in MS Excel and how to delete duplicate values in MS Excel. An easy way to find duplicate rows in Microsoft Excel and remove duplicates in MS Excel.
How to find duplicates in MS Excel
1. Select the cell range with data. To select, click the top left cell of the range, and then drag to the bottom right cell.
2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values…
3. Select a formatting style and click OK. Please note that in the Duplicate Values box, Duplicate is selected in the first drop-down box. Select the format for duplicate values in the other drop-down box.
4. Click OK.
All the duplicate numbers or text are highlighted. MS Excel highlights the duplicates, triplicates and quadruplicates value within the selected cell range.
How to delete duplicates in MS Excel
Delete duplicate rows from a list in Excel
1. Click any single cell inside the data set.
2. On the Data tab, click Remove Duplicates.
3. Make sure all check boxes checked and click OK.
4. Excel removes all identical rows.
Second Method: Delete duplicate Values in MS Excel
1. Select all the rows which you want to filter. To select, click the top left cell of the range, and then drag to the bottom right cell.
2. Click Filter and select Advanced Filter (in the Data menu).
3. Click Filter the list, in place (in the Advanced Filter dialog box).
4. Select the Unique records only check box.
5. Click OK.
6. Click Office Clipboard, on the Edit menu. Make sure the filtered list is still selected.
7. Click Copy
8. On the Data menu, point to Filter, and then click Show All.
9. Press the Delete key.
10. In the Clipboard, click on the filtered list item.
Find duplicate values by applying conditional formatting
1. Select the range of cells you think might contain duplicates.
2. On the Home tab, click Conditional Formatting and go to Highlight Cells Rules and select Duplicate Values.
3. In the Duplicate Values box, make sure that Duplicate is selected in the first drop-down box.
4. Then select the format for duplicate values in the other drop-down box.
5. Click OK.
6. All the duplicate numbers or text will be highlighted.