How to keep only duplicates in excel

Answer

To keep only duplicates in Excel, you need to identify the repeated values first and then filter or remove the unique values. There are several methods to do this, depending on your needs.

Method 1: Using Conditional Formatting

1. Select the range of cells you want to check for duplicates.

2. Go to the Home tab.

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

4. In the dialog box, select a format to highlight duplicates (e.g., light red fill with dark red text).

5. Click OK. The duplicate values will now be highlighted.

6. You can filter by color to display only the duplicates:

   1. Go to the Data tab.

   2. Click Filter.

   3. Use the filter dropdown on the column → Filter by Color → choose the highlight color.

This method keeps only the duplicate entries visible.

Method 2: Using the COUNTIF Function

1. Suppose your data is in column A from A2:A100.

2. In a new column B, enter the formula to identify duplicates:

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

3. Drag the formula down to apply it to all rows.

4. Apply a filter on column B:

   1. Go to the Data tab → Filter.

   2. Click the filter dropdown → select Duplicate.

5. Only rows labeled "Duplicate" will be displayed.

6. Copy these rows to another sheet if you want to keep only duplicates.

Method 3: Using Excel’s Remove Duplicates Tool (Workaround)

The Remove Duplicates tool usually deletes duplicate entries and keeps one, but you can use a workaround:

1. Add a helper column to count occurrences:

   =COUNTIF(A:A, A2)

2. Filter this helper column to show only values greater than 1.

3. Copy the filtered data to a new sheet. This sheet will have only duplicates.

Notes and Variations

• On Mac, all steps are the same except menu names slightly differ: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values is identical.

• This process works for both text and numbers.

• If duplicates exist across multiple columns, you can modify the COUNTIF formula to COUNTIFS for multiple criteria.

This approach ensures that only repeated entries remain, while unique values are excluded. It is especially useful for cleaning large datasets or preparing data for analysis.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report