How to Find Duplicates in Excel

Normally when we have dirty data, we tend to get a lot of duplicates. But in Excel it is very easy to spot the duplicates for your data cleanup!

Key Takeaways

• Use Conditional Formatting for Quick Highlighting – Excel’s Conditional Formatting feature can highlight duplicate values instantly for easy identification.

• Remove Duplicates with Built-in Tool – The Remove Duplicates option under the Data tab allows you to delete duplicate entries in a selected range.

• COUNTIF or COUNTIFS Functions Help Identify Duplicates – Using COUNTIF or COUNTIFS formulas can flag duplicate values by checking if a value appears more than once.

• Use Pivot Tables to Summarize Duplicates – A Pivot Table can help group and count duplicate records, making it easier to analyze repeated values.

• Power Query for Advanced Duplicate Handling – Power Query provides flexible options to filter, remove, or keep duplicate records with more control than built-in tools.

Our Data Setup

Here is our sample list of words, you can see it has a lot of duplicates:

STEP 1: Select your list of words / data:

STEP 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

STEP 3: You can select the formatting that you want. For our example, we selected Green Fill with Dark Green Text.

Click OK.

You will now see the magic happen, all of the duplicate values are now highlighted in your Excel worksheet!

Frequently Asked Questions

How do I quickly highlight duplicates in Excel?
Use Conditional Formatting by selecting your data, going to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

Can I remove duplicates without deleting them?
Yes! Use a COUNTIF formula to mark duplicates in a new column instead of deleting them. This way, you can review before making changes.

What is the best formula to find duplicates?
The COUNTIF formula (=COUNTIF(A:A, A2) > 1) helps identify duplicates by counting occurrences of a value in a column.

How can I find duplicates across multiple columns?
Use COUNTIFS to check for duplicates based on multiple criteria, or use Power Query to identify duplicate rows across multiple fields.

Is there a way to filter duplicates instead of deleting them?
Yes, you can use Advanced Filter (Data → Advanced) to display only unique values while keeping duplicates hidden.

Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.