ExcelTutorials

How to Remove Duplicates in Excel | Find & Delete Duplicates

2 Mins read

In Microsoft Office Excel you have several ways to filter for unique values or find and remove duplicates in Excel.
Now, you will learn how to find & remove duplicate values in excel.

  1. Use the Remove Duplicates command in the Data Tools group on the Data
  2. To filter for unique values, use the Advanced Filter command in the Sort & Filter group on the Data
  3. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home

Excel Vlookup formula – Picture Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this picture-based guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

#3 Ways to Find & Remove Duplicates in Excel

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.

How to Find and Remove Duplicates Step-by-Step

Step 1: First, click any single cell inside the data set.

Image-1-Select-the-data.

Step 2: On the Data tab, click Remove Duplicates.

Image-2-Remove-duplicate

Step 3: The following dialog box appears. Click On all checkboxes and click OK.

Image-3-column-selection-in-remove-duplicate
Image-4-Effect-of-remove-duplicate

To remove rows with the same values in certain columns in excel, execute the following steps:

Step 4: For example, remove rows with the same Airport Name. Select any single cell inside the data set.

Image-5-Remove-Duplicate

Step 5: Check Airport Name and click OK.

Image-6-Remove-Duplicate

How to do Advanced Filter in Excel Step-by-Step

Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007

  • Step 1: Select the range of cells or select the column.
  • Step 2: On the Data tab, in the Sort & Filter group, click Advanced.
Image-7-Advance-Filter
  • Step 3: In the Advanced Filter dialog box, do one of the following:
  • Step 4: Click Copy to another location.
  • Step 5: In the Copy to box, enter a cell reference.
  • Step 6: Select the Unique records only check box, and click OK.
Image-8-Advance-Filter

Excel Vlookup formula – Picture Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this picture-based guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

How to do Conditional Formatting in Excel Step-by-Step

Excel for Office 365 Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 | How to remove duplicates in excel.

Steps to find duplicate value using condition formatting in Excel

Step 1: Select the range of cells you wish to test.

Step 2: On the Home tab, click Conditional Formatting, Highlight Cells Rules, and Duplicate Values.

Image-9-Conditional-formatting

Step 3: Select a formatting style and click OK.

Step 4: Excel highlights the duplicate names.

Image-10-Duplicate-Value

Since you can see, Excel highlights duplicates, triplicates, etc.

Execute the following steps to highlight triplicates only.

Step 5: First, clear the previous conditional formatting rule.

Image-11-Clear-Confitional-Formatting

Step 6: Select the range of cells you wish to test.
Step 7: Select “Use a formula to determine which cells to format”.

15 Excel Data Cleaning Tricks Guidebook

Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages) 

Step 8: Enter the formula “=COUNTIF(A:A,A5)=3”.
Step 9: Select a formatting style and click OK.
Step 10: Excel highlights the triplicate names.

Image-12-Countif-find-duplicate-value

Now, after completing the process, you can check for duplicate values in another sheet. Although, you can see no duplicate values and all same value got permanently delete.

Conclusion

How to Remove duplicates in Excel

In Excel, there are several ways to filter for unique values—or remove duplicate values:

  1. Select the cells you want to check for duplicates.
  2. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.

This is how we successfully complete on how to remove duplicates in Excel with these simple steps

Pivot Tables – Guidebook for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads. Most Popular Tricks are #3, #7 & #12

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…