How to Find and Remove Duplicates in Excel – wintips.org

If you are looking for easy ways to find and delete duplicate records in Excel, then you are at the right place because in this guide, we will show you easy ways to do it in detail.

Microsoft Excel is widely used, but it can be confusing when data is duplicated, which reduces the practicality of spreadsheets. Incorrectly creating duplicate records in Excel can lead to incorrect reports and skewed measurements, making the results unreliable.

Fortunately, finding and removing duplicates in Excel is very simple. Below you will find several ways to accomplish this task, so let’s get started.

How to find and delete duplicate entries in Excel.

Method 1. How to find and highlight duplicate records in Excel.

An easy way to find and remove duplicate values ​​in Excel is to manually highlight and remove them using the conditional formatting feature.

1. Select the cell(s) or column(s) you want to remove duplicates from.

2. Now inside Home pageclick on it Conditional formatting and select Rules for distinguishing cells –> Duplicate values.

image

3. A dialog box should now appear on your screen. If you want a different color for the duplicate values ​​(in the box next to “with values”), select it and then click . OK.

image

4. Once duplicate entries are detected, you can easily remove them manually.

Method 2. How to delete duplicate entries in Excel with the “Delete Duplicates” command.

The easiest way to remove duplicate values ​​in an Excel file is through the “Remove Duplicates” command within the program.

1. Select the cell(s) or column(s) you want to remove duplicates in the Excel file.

2. go to Data tab and then select Remove duplicates.

image

3a. It should be opened Remove duplicates mirror Here you can choose which columns to remove duplicate values ​​from and whether or not your data has headers.*

* Note: If your data contains headers, check the appropriate box.

3b. Hit when done OK.

image

3c. You will then be notified of how many duplicate entries have been found and removed and how many unique entries are left.

image

Method 3. How to Hide Duplicate Values ​​in Excel Using Advanced Filters

1. Select the cell(s) or column(s) you want to remove duplicates from.

2. Dan Data tab, press Complicated to open the filters window.

image

3. In the advanced filter window, select and click OK:

  • Filter the list in place.
  • Unique entries only.

image

4. When the list is filtered in place, the duplicate rows are hidden, but when the data is moved to another location, the duplicate rows are deleted.

Method 4. How to Remove Excel Duplicates Using Power Query Tool

1. Start Excel and select the cells/range of cells you want to check for duplicates.

2. Now go to Data tab and click on it From table/range. Hit OK In the Create Table dialog box.

image

3. In the Power Query Editor, right-click and select the column you want to remove duplicates from Remove duplicates.

image

4. press Close and load from the menu to create a new sheet with unique entries in the current workbook.

image

Method 5. How to remove duplicate entries in Excel using a formula.

To illustrate this approach, we’ll use a simple example with columns called “Sport,” “Athlete Name,” and “Medal Won,” which we’ll combine into a new column called “Merged.” So let’s begin.

1. Select the first empty cell in the “Combined” column (for example, “D2” in this example) and enter the formula for the combination of the first 3 columns in the formula bar. (eg “=A2&B2&C2” in this example). Click when done Enter.

2. Then copy the formula to all rows. It should change automatically depending on the number of rows.

image

3. Now create another column and name it.Calculation‘. This column helps to identify duplicate values.

4. Use the COUNTIF function in the first empty cell (for example, “E2”) of the “Pick” column and enter the following formula:

=COUNTIF($D$2:D2,D2)

5. Copy the above formula to all rows.

6. Now, if the value of Count is “1”, then it has appeared only once and is unique. If the value is “2” or more, it is considered a duplicate value.

image

6. Select Now Calculation column and go to Data tab.

7. Choose Filter and click the arrow icon against your column.

8. Hold the box only against 1 is set. Select the rest and click OK. This should remove duplicate values.

image

That’s it! Which method worked for you?
Let me know if this guide helped you by leaving a comment about your experience. Please like and share this guide to help others.

If you found this article useful, please support us by donating. Even $1 can make a big difference for us We continue to help others while keeping this site free:

Leave a Comment