A Complete Guide to Hiding Duplicate Values in Excel

When working in Excel, seeing too much duplicate data can be confusing. This guide explains every way to make duplicate values less visible on the worksheet rather than deleting them.

✅ 1. Make Duplicate Values Less Visible with Conditional Formatting

1. Select the cell range.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Set formatting such as white font and a gray fill to make the values less noticeable.

✅ 2. Filter Out Duplicate Values with Advanced Filter

1. Click Data > Advanced.
2. Select the “Unique records only” option.
3. Output the results to another location so duplicate values are not shown.

✅ 3. Use a Formula to Show Only the First Occurrence

Display only the first occurrence of duplicate values and show the remaining occurrences as blank cells.

=IF(COUNTIF($A$2:A2, A2)=1, A2, "")
  

This formula shows only the cell where a value first appears and leaves subsequent occurrences blank.

✅ 4. Create a Deduplicated List with a PivotTable

1. Create a PivotTable.
2. Add the column containing duplicates to Row Labels.
3. A list of unique values is automatically created with duplicates removed.

✅ 5. Use the UNIQUE Function in Excel 365

This is the simplest and most intuitive method. It automatically displays only unique values.

=UNIQUE(A2:A100)
  

📌 Summary Guide

• For visual hiding, use Conditional Formatting.
• For filtering, use Advanced Filter or a PivotTable.
• For formula-based control, use COUNTIF + IF.
• Excel 365 users should take full advantage of the UNIQUE function.

Simply hiding duplicate values effectively can make your worksheet easier to read and manage. Be sure to apply these methods in your work!