How to Find Duplicates in Excel: A Complete 2025 Guide for All Skill Levels

Microsoft Excel is one of the most powerful tools for managing data — but when duplicates sneak into your spreadsheets, things can get messy fast. Whether you’re handling sales records, customer lists, or student data, knowing how to find duplicates in Excel can save you hours of frustration and ensure your reports stay accurate.

In this complete 2025 guide, you’ll learn multiple ways to find and remove duplicates in Excel, including using conditional formatting, formulas, filters, and advanced Excel features. We’ll also cover pro tips, common mistakes, and real-world examples to help you clean your data like a pro.

Why Finding Duplicates in Excel Matters

Before diving into the “how,” let’s understand the “why.”
Duplicate data can cause major issues such as:

• Inflated totals in sales or inventory reports

• Confusing customer databases

• Errors in mailing lists or contact sheets

• Misinformed business decisions

For example, imagine sending the same promotional email twice to one customer. Not only does it look unprofessional, but it also skews your analytics. That’s why knowing how to find duplicates in Excel is essential for anyone dealing with data — from beginners to analysts.

1. How to Find Duplicates in Excel Using Conditional Formatting

This is the easiest and most visual way to highlight duplicates without deleting them.

Step-by-Step Guide

1. Select your data range
   Highlight the column or area where you want to check for duplicates. For example, select A1:A100 if you’re checking a list of names.

2. Go to the Home tab
   On the Excel ribbon, click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

3. Choose your formatting style
   Excel will prompt you to select a color for duplicate cells (like light red or yellow). Pick one that stands out.

4. Click OK
   Instantly, Excel will highlight all duplicate values in your selected range.

Pro Tip:

If you only want to highlight duplicates across specific columns (like Name + Email), combine those columns into one using a formula such as:
=A2 & " " & B2
Then, apply conditional formatting on the new column.

2. How to Find Duplicates in Excel Using the “Remove Duplicates” Tool

If you’re ready to delete duplicates, Excel’s built-in Remove Duplicates tool is your best friend.

Here’s how:

1. Select your dataset.
   Highlight the area that contains the data you want to clean.

2. Go to Data > Remove Duplicates.

3. Choose columns carefully.
   Excel will show checkboxes for each column. Select one or multiple columns to define what counts as a duplicate.

4. Click OK.

5. Review the result.
   Excel will display a summary showing how many duplicate values were removed and how many unique ones remain.

Example:

If you have a customer list with Name, Email, and Phone columns, and you check only “Email,” Excel will remove rows with the same email, even if names differ.

3. How to Find Duplicates in Excel Using Formulas

If you prefer a formula-based approach that gives more control, Excel has several options.

Option 1: COUNTIF Formula

The COUNTIF function counts how many times a value appears in a range.

Formula Example:
=COUNTIF($A$1:$A$100, A1) > 1

This formula checks if a value in A1 appears more than once in the range A1:A100.

• If TRUE, it’s a duplicate.

• If FALSE, it’s unique.

You can drag this formula down your column to mark duplicates.

Option 2: IF + COUNTIF Combination

If you want Excel to label duplicates:

Formula Example:
=IF(COUNTIF($A$1:$A$100, A1)>1, "Duplicate", "Unique")

This creates an easy-to-read list, showing which entries are repeated.

Option 3: Conditional Formatting with Formula

You can also use formulas inside Conditional Formatting:

1. Select your data range.

2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

3. Enter:
   =COUNTIF($A$1:$A$100, A1) > 1

4. Choose a highlight color.

4. How to Find Duplicates in Excel Across Multiple Columns

Sometimes, duplicates aren’t just in one column but across multiple. For example, the same person might appear twice with slightly different details.

Here’s how to detect them:

Method 1: Combine Columns First

Create a new column and combine the key fields:

Then, use Conditional Formatting or a COUNTIF formula on that new column.

Method 2: Use Remove Duplicates with Multiple Columns

When using Data > Remove Duplicates, select all relevant columns (like Name, Email, and Phone). Excel will treat rows with identical values in all chosen columns as duplicates.

5. How to Find and Highlight Unique Values

Sometimes, you might want the opposite — to find entries that appear only once.

Use this formula:

If the result is TRUE, the value is unique. Combine it with Conditional Formatting to highlight unique cells visually.

6. How to Find Duplicates in Excel Using Advanced Filter

Excel’s Advanced Filter lets you extract duplicate or unique values into a new list.

Steps:

1. Select your data range.

2. Go to Data > Advanced under the Sort & Filter section.

3. In the dialog box:

   • Choose Copy to another location.

   • Check Unique records only.

   • Specify the output range.

4. Click OK.

Now you’ll have a clean list of unique entries — duplicates removed.

7. How to Find Duplicates in Excel Using Pivot Tables

Pivot Tables aren’t just for reports — they can also help you spot duplicates quickly.

How:

1. Select your dataset.

2. Go to Insert > PivotTable.

3. In the PivotTable Fields pane, drag the field (like “Email”) to both Rows and Values areas.

4. The Values area will automatically count occurrences.

If any value count is greater than 1, it’s a duplicate.

8. Tips to Manage Duplicates Efficiently

• Always back up your data before removing duplicates.

• Trim spaces using =TRIM(A1) to fix hidden duplicates caused by extra spaces.

• Check for case sensitivity — Excel treats “John” and “john” as the same by default.

• Use Data Validation to prevent duplicates in the first place.

  • Go to Data > Data Validation > Custom

  • Enter: =COUNTIF(A:A, A1)=1

This ensures that future entries in that column must be unique.

9. How to Find Duplicates in Excel on Mac and Mobile

If you’re using Excel on Mac, the process is almost identical. The only minor difference is where certain menu options are located.

• For Conditional Formatting, go to Home > Styles > Conditional Formatting.

• For Remove Duplicates, find it under the Data tab.

On mobile (Excel app):

• Tap the range.

• Use the Sort & Filter feature.

• While you can’t use every advanced tool, you can still filter and manually identify duplicates.

10. Common Mistakes When Finding Duplicates

Even experienced Excel users make these mistakes:

• Forgetting to select the entire dataset before removing duplicates.

• Using formulas with relative references instead of absolute ones (A1 instead of $A$1).

• Ignoring trailing spaces that make identical entries appear unique.

• Removing duplicates without confirming the right columns are selected.

Avoid these, and your data cleaning process will be flawless.

Conclusion

Learning how to find duplicates in Excel is one of the most valuable data management skills you can develop. Whether you prefer visual methods like Conditional Formatting, automated tools like Remove Duplicates, or custom formulas for flexibility, Excel offers plenty of ways to keep your data clean, consistent, and accurate.

With practice, you’ll not only save time but also prevent reporting errors and improve overall data integrity — which means smarter decisions and smoother workflows.

FAQs About Finding Duplicates in Excel

1. How can I find duplicates in Excel without deleting them?

Use Conditional Formatting or the COUNTIF formula to highlight duplicates instead of removing them. This way, you can review them first.

2. What’s the fastest way to remove duplicates in Excel?

The Remove Duplicates tool under the Data tab is the quickest. It automatically scans and deletes duplicates based on your selected columns.

3. How do I find duplicates across multiple columns?

Select multiple columns in the Remove Duplicates dialog, or combine them using a formula like =A2 & B2 before applying duplicate checks.

4. Why are some duplicates not detected?

Hidden spaces, case differences, or formatting issues often cause this. Use the TRIM and LOWER functions to clean data before checking.

5. Can I find duplicates in Excel using a Pivot Table?

Yes! Create a Pivot Table with your key column in both Rows and Values. The count will show which items appear more than once.

✅ Final Tip:
Mastering how to find duplicates in Excel will not only make your data cleaner but also boost your confidence in handling large datasets efficiently — a must-have skill in today’s data-driven world.