How to only show duplicates in excel

Answer

To only show duplicates in Excel, you can use the Conditional Formatting and Filter features. This method highlights duplicate values and allows you to filter the data to display only the duplicates.

Steps to Show Only Duplicates

1. Select the Data Range
   Click and drag to select the cells you want to check for duplicates. This can be a single column or multiple columns.

2. Apply Conditional Formatting

   1. Go to the Home tab.

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

   3. In the pop-up, select the formatting style to highlight duplicates. Click OK.

3. Filter to Show Only Duplicates

   1. Keep the highlighted cells selected.

   2. Go to the Data tab.

   3. Click Filter. Small dropdown arrows appear on your column headers.

   4. Click the dropdown arrow on the column with duplicates.

   5. Choose Filter by Color and select the color you used in Conditional Formatting.

Only the rows with duplicate values will now be visible. Unique values will be hidden.

Alternative Method Using Formulas

You can use a formula to identify duplicates in a new column and filter them:

1. Suppose your data is in column A, starting from cell A2. In cell B2, enter the formula: 

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

2. Drag the formula down for all rows.

3. Apply a filter to column B and select Duplicate to show only duplicate entries.

This method works across multiple Excel versions and is particularly useful for dynamic data where values may often change.

Notes

• On Mac, Conditional Formatting is located under Home → Format → Conditional Formatting. The rest of the steps remain the same.

• This method highlights all duplicate occurrences, not just the second or later occurrences.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report