Conditional Formatting for Duplicates and Unique Values

Identifying and Highlighting Duplicates

Conditional Formatting in Excel allows you to easily highlight duplicate values in a dataset. This is helpful for identifying errors, duplicate entries, or verifying data integrity.

Why Identify Duplicates?

• Prevents data redundancy and inconsistencies.
• Helps with data validation and cleaning.
• Improves reporting accuracy and efficiency.

How to Highlight Duplicates

1. Select the range of cells you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a formatting style and click OK.

Example

Highlight duplicate product codesSelect the column containing product codes and use Conditional Formatting to mark duplicate entries.

Highlighting Unique Values

Unique values are those that appear only once in a dataset. You can highlight them using Conditional Formatting.

Steps to Highlight Unique Values

1. Select the target range.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Change the dropdown option to Unique and apply a formatting style.

Example

Find unique customer IDsUse this method to locate customer IDs that appear only once in a database.

Highlighting Differences Between Lists

If you have two lists and need to highlight differences, use a formula-based Conditional Formatting rule.

Formula to Highlight Differences

To compare values in column A against column B, use this formula:

=COUNTIF($B$2:$B$100, A2)=0

Example

Compare inventory listsApply this formula to check which items from List A are missing in List B.

Conclusion

Using Conditional Formatting for duplicate and unique values streamlines data validation, ensures accuracy, and enhances visibility in large datasets.