How to hide duplicates in excel

Answer

Hiding duplicates in Excel means filtering the dataset so only unique values remain visible while the duplicated values stay hidden without being deleted. To hide duplicates in Excel, use the built-in filtering tools in the worksheet. The process is slightly different on Windows and Mac.

Windows and Mac Steps

1. Select the entire range that contains the repeated values.

2. Open the Data tab on the Ribbon.

3. Select Advanced in the Sort & Filter group.

4. Choose Filter the list, in-place to hide duplicates in the same location.

5. Check Unique records only.

6. Confirm with OK.

The visible list contains only one instance of each value. The duplicates remain hidden under the applied filter.

Differences between Windows and Mac

• Windows places the Advanced button directly in the Sort & Filter group.

• Mac groups the Advanced Filter option inside a dropdown under Sort & Filter, but the steps and results remain identical.

How to unhide duplicates

To unhide duplicates and restore the full dataset:

1. Go to Data.

2. Select Clear in the Sort & Filter group.

3. The worksheet returns to the complete view.

How to hide duplicates using Conditional Formatting

Conditional formatting does not hide data, but it helps identify which values will be hidden once filtered.

1. Select the range.

2. Go to Home.

3. Choose Conditional Formatting.

4. Pick Highlight Cell Rules.

5. Select Duplicate Values.

6. Apply the formatting.

The highlighted values can then be hidden by applying a regular filter:

1. Add a filter from the Data tab.

2. Filter by the formatting color and hide those rows manually.

How to hide duplicate rows with formulas

Excel formulas do not hide data by themselves. They mark duplicates so you can filter them.

Steps

1. Insert a helper column next to your data.

2. Use the following formula to flag duplicates:

   $=COUNTIF(\$A\$1:A1,A1)$
   

3. Filter the helper column to display only values equal to 1.

4. All values greater than 1 represent duplicates and remain hidden by the filter.

Both Windows and Mac run the formula the same way.

When should you hide duplicates?

Hiding duplicates is effective when:

• A clean display of unique values is required.

• A report needs a summary without altering the original dataset.

• A dataset contains repeated entries that disrupt readability.

These conditions support non-destructive editing because filtering hides duplicates without deleting data.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report