How to Find Duplicates in Excel: Identify, Highlight, Count, and Filter

Working with data in Excel is very common, whether you are a student, an accountant, an analyst, or any business professional. But one issue that almost everyone faces is duplicate data. Duplicate values can create confusion, give wrong calculations, incorrect reports, and sometimes even lead to serious business mistakes. That’s why knowing how to find and manage duplicates in Excel is a very important skill.

In this blog, we will learn how to identify, highlight, count, and filter duplicates in Excel using simple and practical methods. These techniques are perfect for beginners and also very helpful for advanced Excel users.

What Are Duplicates in Excel?

Duplicates are the values that appear more than one time in a dataset.
They can be:

• Duplicate names

• Duplicate email IDs

• Duplicate phone numbers

• Duplicate invoice numbers

• Duplicate product codes

For example:

Here, both Name and Email are duplicated.

Why Is It Important to Find Duplicates?

Duplicates can:

• Give wrong totals

• Create confusion in reports

• Causes wrong decision-making

• Increase data size without reason

• Affect overall data accuracy

That’s why, before doing any analysis, it is always a good habit to check and clean duplicate data first.

1. How to Identify Duplicates Using Conditional Formatting

This is the fastest way to visually find duplicates.

Steps:

• Select the column where you want to check duplicates

• Go to Home? Conditional Formatting? Highlight Cells Rules? Duplicate Values

• Choose any color format

• Click OK

Excel will automatically highlight all duplicate values.

This method is best when:

• You want a quick visual result

• You are checking only one column

2. How to Find Duplicates Using the COUNTIF Formula

COUNTIF is one of the most powerful and simple ways to identify duplicates.

Formula:

If the result is:

• 1 ? Unique value

• More than 1 ? Duplicate value

Example:

This method is useful when:

• You want logical confirmation

• You need to create reports

• You want to filter duplicates later

3. How to Highlight Duplicates Using a Formula

You can highlight duplicates using Conditional Formatting with a formula, also.

Steps:

• Select your data

• Go to Conditional Formatting? New Rule

• Choose Use a formula to determine which cells to format

• Enter:

• Set format and apply

This is powerful when:

• You work with multiple columns

• You need more control

4. How to Count Duplicates in Excel

To count only duplicate values:

This shows:

• 0 ? Unique

• 1 or more? Number of duplicates

To count total duplicate entries (Excel 365 / 2021):

5. How to Filter Duplicates in Excel

To show only duplicates:

Add helper column:

Then:

• Apply Filter

• Filter values greater than 1

Now, only duplicate records will be visible.

This is useful when:

• You want to review duplicates

• You want to delete or correct them

6. How to Remove Duplicates in Excel

Steps:

• Select your dataset

• Go to Data? Remove Duplicates

• Select columns

• Click OK

Excel keeps the first record and removes others.

Always keep a backup before removing duplicates, because once they’re gone, it’s hard to get them back.

7. How to Find Duplicates in Multiple Columns

Sometimes duplicates depend on a combination like:

• Name + Email

• Invoice No + Date

Use:

If result > 1, it means a duplicate.

8. Find Case-Sensitive Duplicates

Excel normally ignores case (A and a are the same).
For case-sensitive:

If result > 1, value is a duplicate.

9. Using Pivot Table to Find Duplicates

Steps:

• Insert Pivot Table

• Add your column to Rows

• Add the same column to Values (Count)

• Filter Count > 1

Pivot Tables are handy for large datasets.

10. Real-Life Example

If you have:

• Customer list

• Phone numbers

• Email IDs

Finding duplicates helps:

• Avoid calling the same customer twice

• Avoid sending double emails

• Maintain clean CRM data

Best Practice for Advanced Excel Users

For students of advancedexcel.net, handling duplicates is a core skill because:

• It improves data accuracy

• It builds strong data cleaning skills

• It prepares you for Data Analyst roles

• It is used in almost every real project

Conclusion

Finding duplicates in Excel is not difficult once you know the right methods. Whether you want to identify, highlight, count, or filter duplicates, Excel gives you many powerful tools. From Conditional Formatting to COUNTIF, from Filters to Pivot Tables, every method has its own purpose.

If you want clean data, correct reports, and professional Excel skills, learning duplicate management is something you should never skip.