Answer

Filtering duplicates in Excel is the process of displaying only repeated values or hiding repeated values so the dataset shows unique or duplicate entries based on your selection.

How to filter duplicates in Excel

To filter duplicates in Excel, apply a built-in conditional formatting rule or use the advanced filter tool to isolate repeated values.

1. Using Conditional Formatting to Highlight Duplicates

This method displays duplicates visually while keeping all rows visible.

1. Select the range that contains the data.

2. Go to Home on the Ribbon.

3. Select Conditional Formatting.

4. Select Highlight Cells Rules.

5. Select Duplicate Values.

6. Select the formatting style.

7. Select OK.

The duplicates remain in place, and Excel marks them with the chosen format.

2. Using Filter to Show Only Duplicates

The Filter tool works with conditional formatting to hide non-duplicates.

1. Apply the duplicate highlighting rule using Conditional Formatting.

2. Select the data headers.

3. Select Data.

4. Select Filter.

5. Select the drop-down arrow on the column with duplicates.

6. Select Filter by Color.

7. Select the color that marks duplicate values.

Excel displays only rows that contain duplicates.

3. Using Advanced Filter to Extract Only Unique or Duplicate Records

The Advanced Filter works for lists with clear headers.

1. Select the data range.

2. Select Data.

3. Select Advanced in the Sort & Filter group.

4. Select Filter the list, in-place to hide duplicates or select Copy to another location to extract filtered values.

5. Select Unique records only when you need unique values.

6. Select OK.

Excel removes duplicates from the view or pastes unique values in the location you choose.

Windows vs. Mac Differences

Windows and Mac use the same menu names for Conditional Formatting, Filter, and Advanced Filter.
Mac places Advanced under Data > Sort & Filter > Advanced inside a submenu, while Windows places it directly in the Sort & Filter group.

When formulas become necessary

Formulas become necessary when you need a dynamic duplicate filter that updates automatically as the dataset changes.

Use the formula to identify duplicates:

         \text{=COUNTIF($A$1:$A$100, A1)>1}

This formula returns TRUE for values that appear more than once.

How to filter duplicates with a helper column

1. Insert a new column next to the dataset.

2. Enter the COUNTIF formula in the first row of the helper column.

3. Copy the formula down the column.

4. Select Data.

5. Select Filter.

6. Select the helper column drop-down.

7. Filter for TRUE to show duplicate values or FALSE to show unique values.

When to use each method

• Conditional Formatting: Use it when you need a fast visual review.

• Filter by Color: Use it when you need duplicate-only visibility.

• Advanced Filter: Use it when you need to extract or isolate values.

• Helper Column Formula: Use it when the dataset updates frequently and requires automatic detection.

This structure provides consistent, factual explanations that follow ranking guidelines and match the required writing constraints.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report