How to remove non duplicates in excel

Answer

To remove non-duplicates in Excel, you are essentially keeping only the values that appear more than once and removing all unique values. Follow these steps:

Using Conditional Formatting and Filtering

1. Select the range of data you want to process. For example, select column A if your data is in A2:A100.

2. Go to the Home tab and click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

3. In the Duplicate Values dialog box:

   • Choose Duplicate in the first drop-down.

   • Select a formatting style (e.g., light red fill).

4. Click OK. All duplicates will now be highlighted.

5. Apply a filter:

   • Go to the Data tab → Filter.

   • Use the filter dropdown in your column and select only the highlighted duplicates.

6. Copy the filtered data to a new location or delete the rows that are not highlighted.

Using a Formula to Identify Non-Duplicates

1. In a blank column next to your data, enter this formula (assuming data is in A2:A100):

   =COUNTIF($A$2:$A$100, A2)>1

2. This formula returns TRUE for duplicates and FALSE for unique values.

3. Apply a filter on the column with the formula.

4. Filter to show only TRUE values.

5. Copy or delete the rows with FALSE (non-duplicates).

Using the “Remove Duplicates” Tool (Indirect Method)

Excel’s Remove Duplicates tool does not directly remove non-duplicates. Use this approach:

1. Add a helper column with the formula: 

   =COUNTIF(A:A, A2)

2. This counts the number of times each value appears.

3. Filter the helper column for values greater than 1.

4. Keep the filtered rows and delete the rest (non-duplicates).

Notes

• On Windows and Mac, all these steps are identical in Excel 2016 or later.

• Conditional Formatting is dynamic, so changes in data automatically update highlights.

• Using formulas gives more control, especially for large datasets.

• Removing non-duplicates is useful for cleaning data for analysis or reporting where repeated entries are meaningful.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report