How to extract duplicates in excel

Answer

To extract duplicates in Excel, multiple methods depend on whether you want to highlight, filter, or separate duplicates into a new location.

Method 1: Using Conditional Formatting to Highlight Duplicates

1. Select the range of cells where you want to find duplicates.

2. Go to the Home tab.

3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

4. Choose a formatting style (e.g., light red fill with dark red text).

5. Click OK.

This will highlight all duplicate values in the selected range.

Note: Only the duplicate entries are highlighted. The first occurrence is also highlighted unless you adjust the rules.

Method 2: Using the Remove Duplicates Tool to Extract Unique Values

1. Select the range of data.

2. Go to the Data tab.

3. Click Remove Duplicates.

4. In the dialog box, check the columns to analyze.

5. Click OK.

Excel will show a message with the number of duplicates removed and the number of unique values remaining.

Note: This method removes duplicates permanently from the original data, so copy the original data to a new sheet if you want to preserve it.

Method 3: Using the COUNTIF Function to Identify Duplicates

1. Suppose your data is in column A starting from A2.

2. In cell B2, enter the formula: 

   =IF(COUNTIF(A:A, A2)>1, "Duplicate", "Unique")

3. Drag the formula down for all rows.

This formula labels each value as Duplicate or Unique.

Method 4: Extracting Duplicates to a New Sheet Using Advanced Filter

1. Select the range of data.

2. Go to the Data tab.

3. Click Advanced in the Sort & Filter group.

4. Choose Copy to another location.

5. Set List range to your data range.

6. Check Unique records only.

7. Choose a destination cell for the extracted list.

8. Click OK.

This creates a new list containing only unique values. To get duplicates, combine with the COUNTIF method and filter only the "Duplicate" rows.

Method 5: Using Excel’s Power Query to Extract Duplicates

1. Select the data range.

2. Go to the Data tab and click From Table/Range.

3. In the Power Query editor, go to Home → Remove Rows → Remove Duplicates for unique values.

4. To see duplicates instead, group data by the column, add a count column, and filter rows where count > 1.

5. Click Close & Load to bring results back to Excel.

Power Query allows handling large datasets efficiently and is useful for recurring duplicate detection.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report