How to Remove Duplicates in Excel: The Complete Step-by-Step Guide (2026)

📌 Key Takeaways:

• The built-in Remove Duplicates tool is the fastest method for most users — takes 10 seconds
• Conditional Formatting helps you FIND duplicates before removing them
• Advanced Formulas give you total control over what gets deleted
• Power Query is the best choice for recurring data cleaning on large datasets
• AI automation (using tools like Claude) can remove duplicates across 100+ files in minutes

Introduction

If you've ever opened an Excel spreadsheet and realized the same customer appears three times, or that your sales report shows the same transaction on multiple rows — you know the pain. Duplicate data is one of the most common and frustrating problems Excel users face.

According to a 2025 study by Gartner, poor data quality costs organizations an average of $12.9 million per year. Duplicate entries are the #1 contributor to this problem. Whether you're an accountant reconciling bank statements, a marketer cleaning a mailing list, or a financial analyst preparing a board report — knowing how to remove duplicates in Excel is an essential skill.

In this complete guide, you'll learn 5 different methods to remove duplicates, from the simple built-in tool that takes 10 seconds to advanced AI-powered automation that cleans thousands of files in minutes. Each method includes step-by-step instructions with real-world examples.

By the end of this guide, you'll never waste time hunting down duplicates again.

---

What Are Duplicates in Excel?

Before we dive into how to remove duplicates, let's clarify what we're actually dealing with.

A duplicate in Excel is any row that contains the same values as another row in a specified set of columns. Excel considers two rows duplicates when ALL the columns you're checking have identical values.

Types of Duplicates

| Type | Description | Example |

|------|-------------|---------|

| Exact Duplicates | All columns are identical | Two rows: "John Smith, NYC, $500" and "John Smith, NYC, $500" |

| Partial Duplicates | Some key columns match but others differ | Two rows: "John Smith, NYC, $500" and "John Smith, NYC, $501" |

| Fuzzy Duplicates | Values are similar but not identical | "John Smith" vs "Jon Smyth" |

| Formula Duplicates | Values look different but formulas evaluate to the same result | Two cells with the same total but different underlying formulas |

💡 Pro Tip: Excel's built-in tool handles exact duplicates perfectly. For partial and fuzzy duplicates, you'll need Methods 3-5 covered later in this guide.

---

Method 1: The Built-in Remove Duplicates Tool (Easiest — 10 Seconds)

This is the fastest way to remove duplicates in Excel. If you need a quick and reliable method, start here.

Step-by-Step Instructions

Step 1: Select any cell within your data range (or highlight the entire range).

Step 2: Go to the Data tab on the Excel ribbon.

Step 3: Click Remove Duplicates (it's in the "Data Tools" group).

Step 4: A dialog box appears. Choose which columns to check for duplicates:

• Check ALL columns to find fully identical rows
• Select specific columns to find duplicates based on key fields only (e.g., only check the "Email" column)

Step 5: Click OK.

Step 6: Excel shows a message: "X duplicate values found and removed; Y unique values remain."

⚠️ Important: This method permanently deletes the duplicate rows. Always save a backup copy first!

Pro Tips for the Remove Duplicates Tool

Use Case 1: Remove exact duplicates (all columns matched)

Check every column. Excel only deletes rows where every single cell is identical.

Use Case 2: Remove duplicates based on one column

Uncheck all columns except the one you want to check. For example, to remove duplicate email addresses while keeping the first occurrence, check only the "Email" column.

Use Case 3: Remove duplicates based on multiple columns

Check multiple columns. Excel removes rows where the combination of those columns is identical. For example, check "First Name" + "Last Name" to remove the same person appearing twice even if other data differs.

| Scenario | Columns to Check | Result |

|----------|-----------------|--------|

| Same customer, different order dates | Customer ID | Keeps first order, removes rest |

| Same invoice, accidentally entered twice | Invoice # | Removes the duplicate invoice |

| Same product, different suppliers | Product Code | Keeps first supplier entry |

---

Method 2: Find Duplicates Using Conditional Formatting (Best for Review)

Before deleting duplicates, you often want to see them first. Conditional Formatting is perfect for this.

How to Highlight Duplicates in Excel

Step 1: Select the range you want to check.

Step 2: Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

Step 3: Choose a formatting style (the default is "Light Red Fill with Dark Red Text").

Step 4: Click OK.

Every duplicate value in your selected range will now be highlighted. This lets you:

• Visually inspect duplicates before removing them
• Decide which occurrences to keep or delete
• Catch false positives (values that look like duplicates but aren't)

💡 Pro Tip: Combine Conditional Formatting with a CountIf formula to see exactly how many times each value appears:

`excel

=COUNTIF(A:A, A2)

`

Copy this formula down, and any cell showing a value greater than 1 is a duplicate. This gives you a permanent, auditable record of your duplicates.

---

Method 3: Remove Duplicates with Excel Formulas (Most Control)

For situations where you need fine-grained control over which duplicates to remove and how, formulas are your best friend. This method is especially useful when you need to keep a record of your cleaning process.

Formula 1: Identify Duplicates with COUNTIF

`excel

=IF(COUNTIF($A$2:$A$100, A2) > 1, "Duplicate", "Unique")

`

Place this formula in a helper column. It marks each row as "Duplicate" or "Unique".

Formula 2: Number Each Occurrence with COUNTIFS

`excel

=COUNTIFS($A$2:A2, A2, $B$2:B2, B2)

`

This formula numbers each occurrence. The first time a combination appears, it returns 1. The second time, 2, and so on. Filter for values > 1 to see all duplicates.

Formula 3: Return the First Occurrence Only (UNIQUE — Excel 365/2026)

If you have Excel 365 or Excel 2026:

`excel

=UNIQUE(A2:B100)

`

This returns only unique rows from your range. It doesn't modify your original data — it creates a new, clean list elsewhere.

⚠️ Important: Excel 2021 and earlier users should use the Remove Duplicates tool or Power Query instead of the UNIQUE formula.

Real-World Example: Cleaning a Customer List

Let's say you have 10,000 customer records and you need to:

Find all duplicate email addresses

Keep the record with the most recent purchase date

Delete the older entries

Step 1: Sort your data by Purchase Date (newest first).

Step 2: Add a helper column with:

`excel

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

`

(This marks the first occurrence — the most recent due to sorting — as "Keep" and everything else as "Delete".)

Step 3: Filter for "Delete" rows and remove them.

---

Method 4: Remove Duplicates with Power Query (Best for Large Datasets)

Power Query is Excel's built-in data transformation tool. It's the best method for large datasets (100,000+ rows) and for recurring data cleaning tasks where you need to repeat the same process every week or month.

How to Remove Duplicates in Power Query

Step 1: Select your data range.

Step 2: Go to Data → From Table/Range (your data will be converted to a table if it isn't already).

Step 3: Power Query Editor opens. Select the column(s) you want to check for duplicates.

Step 4: Go to Home → Remove Rows → Remove Duplicates.

Step 5: Click Close & Load to save the cleaned data back to Excel.

Why Power Query is the Best Choice for Recurring Tasks

When you save a Power Query, it retains every step. Next time you get a new data file:

Click Data → Queries & Connections

Right-click your query → Refresh

Power Query re-applies all cleaning steps — including duplicate removal — automatically

| Feature | Remove Duplicates Tool | Power Query |

|---------|----------------------|-------------|

| Speed on 10K rows | < 1 second | < 1 second |

| Speed on 1M rows | Slow (may freeze) | < 5 seconds |

| Reusable | ❌ No | ✅ Yes |

| Undo possible | ✅ Yes (before save) | ✅ Yes (before close) |

| Handles complex logic | ❌ No | ✅ Yes |

---

Method 5: Remove Duplicates with AI Automation (Best for 100+ Files)

This is where modern Excel professionals gain a massive advantage. If you need to remove duplicates across multiple files, sheets, or on a recurring schedule — AI automation is your answer.

Why AI Beats Manual Methods for Recurring Tasks

Excel power users are now using AI tools like Claude AI to generate VBA macros and Power Query scripts in seconds — without writing a single line of code.

Example AI Prompt for Removing Duplicates:

> "Write a VBA macro that: 1) Opens every CSV file in a folder called 'Monthly Reports' 2) Removes duplicate rows based on column A (Invoice ID) 3) Keeps the row with the most recent date in column D 4) Saves the cleaned file with '_clean' appended. Add error handling."

AI tools generate production-ready code in under 30 seconds. This used to take an experienced VBA developer several hours.

How to Use AI for Duplicate Removal

Step 1: Copy your data structure into an AI tool like Claude.

Step 2: Describe exactly what you want: "Remove duplicates from this table. Check columns A and B. Keep the row with the latest date in column C."

Step 3: The AI either:

• Writes a VBA macro you can run in Excel (one-time cleanup)
• Builds a Power Query script you can refresh weekly
• Creates an Excel formula with instructions

Step 4: Paste and run. That's it.

Comparison: Manual vs AI-Assisted Duplicate Removal

| Scenario | Manual Time | AI-Assisted Time | Savings |

|----------|-------------|-----------------|---------|

| Remove duplicates in 1 file (10K rows) | 2 minutes | 30 seconds | 75% |

| Clean 50 weekly sales files | 3 hours | 10 minutes | 94% |

| Build reusable cleaning process | 2 hours | 5 minutes | 96% |

| Find and fix fuzzy duplicates | 30 minutes | 2 minutes | 93% |

💡 Pro Tip: The [Mastering Claude AI for Excel](/mastering-claude-ai-for-excel) ebook includes 200+ ready-to-use prompts for data cleaning, duplicate removal, and automation. It costs less than a coffee ($7.99) and saves you hours every single week.

---

Method 6: Automate with VBA (Best for Repetitive One-Click Cleanup)

If you need to remove duplicates on a regular schedule — every Monday morning, for instance — a VBA macro turns the process into a single button click.

Complete VBA Macro to Remove Duplicates

`vba

Sub RemoveDuplicatesWithOptions()

Dim rng As Range

Dim ws As Worksheet

' Set your worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

' Define your data range (adjust as needed)

Set rng = ws.Range("A1").CurrentRegion

' Turn off screen updating for speed

Application.ScreenUpdating = False

' Remove duplicates based on column A and B

rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

' Display result

MsgBox "Duplicates removed successfully!", vbInformation

' Turn screen updating back on

Application.ScreenUpdating = True

End Sub

`

How to Use This Macro

Step 1: Press Alt + F11 to open the VBA editor.

Step 2: Go to Insert → Module.

Step 3: Paste the code above.

Step 4: Press F5 to run, or assign the macro to a button for one-click access.

Step 5: Save your workbook as a Macro-Enabled Workbook (.xlsm).

---

Common Mistakes When Removing Duplicates

Avoid these pitfalls that trip up even experienced Excel users:

Mistake 1: Not Making a Backup First

The Remove Duplicates tool deletes rows permanently. If you remove the wrong data, it's gone (unless you undo immediately).

✅ Fix: Always save a copy of your original data before cleaning. Use Ctrl+C → Ctrl+V to duplicate the sheet.

Mistake 2: Removing Duplicates on the Wrong Columns

Checking too many columns = missing real duplicates. Checking too few = deleting data that shouldn't be deleted.

✅ Fix: Be deliberate about which columns define a "duplicate" for your specific use case.

Mistake 3: Forgetting About Leading/Trailing Spaces

"John@email.com" and "John@email.com " look the same to your eyes but are DIFFERENT to Excel.

✅ Fix: Use the TRIM function first: =TRIM(A2) then copy-paste as values before removing duplicates.

Mistake 4: Assuming Remove Duplicates Handles Fuzzy Matching

"John Smith" and "Jon Smith" are NOT duplicates to Excel's Remove Duplicates tool.

✅ Fix: Use Power Query with fuzzy matching or AI automation for near-duplicate detection.

Mistake 5: Not Checking for False Positives

Sometimes "duplicates" in your data are legitimate (e.g., same customer making multiple purchases).

✅ Fix: Always review duplicates before deleting. Use Conditional Formatting (Method 2) to inspect first.

---

Best Practices for Managing Duplicates Long-Term

1. Prevent Duplicates at Data Entry

The best way to handle duplicates is to prevent them in the first place:

`excel

=COUNTIF(A:A, A2) = 1

`

Use Data Validation (Data tab → Data Validation → Custom) with this formula. It prevents users from entering duplicate values in real-time.

2. Create a Reusable Cleaning Process

For monthly or weekly cleaning:

Save your Power Query steps as described in Method 4

Or record your VBA macro from Method 6

Add a button to your Quick Access Toolbar for one-click cleanup

3. Use Tables, Not Ranges

Convert your data to an Excel Table (Ctrl+T) before removing duplicates. Tables:

• Automatically expand when new data is added
• Keep your formulas consistent
• Work better with Power Query

4. Keep a Change Log

If you're cleaning data for a team, track what was removed:

`excel

=IF(COUNTIF($A$2:$A$100, A2) > 1, TEXTJOIN(", ", TRUE, "Duplicate found", "Removed"), "Kept")

`

---

Comparison Table: Which Method Should You Use?

| Your Situation | Best Method | Time Required | Skill Level |

|----------------|-------------|---------------|-------------|

| Quick one-time cleanup | Method 1: Remove Duplicates tool | 10 seconds | Beginner |

| Need to inspect duplicates first | Method 2: Conditional Formatting | 1 minute | Beginner |

| Complex rules for what to keep | Method 3: Formulas | 5 minutes | Intermediate |

| Large dataset (100K+ rows) | Method 4: Power Query | 2 minutes | Intermediate |

| Recurring weekly/monthly task | Method 4: Power Query | 2 minutes setup | Intermediate |

| Cleaning 50+ files | Method 5: AI Automation | 5 minutes | All levels |

| Need a one-click button | Method 6: VBA Macro | 10 minutes setup | Intermediate |

---

Frequently Asked Questions (FAQ)

Q1: How do I remove duplicates in Excel but keep the first occurrence?

By default, Excel's Remove Duplicates tool keeps the first occurrence and deletes all subsequent duplicates. No special settings needed. If you want to keep a DIFFERENT occurrence (e.g., the most recent one), use Method 3 (Formulas) and sort your data first.

Q2: How do I remove duplicates based on one column in Excel?

Go to Data → Remove Duplicates, then uncheck all columns except the one you want to check. Excel will only look for duplicates in that single column.

Q3: What is the shortcut key for Remove Duplicates in Excel?

There's no single shortcut key, but you can use: Alt → A → M (press sequentially, not simultaneously). This navigates to Data → Remove Duplicates.

Q4: How do I find duplicates in Excel without deleting them?

Use Conditional Formatting (Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values). This highlights duplicates in color without deleting anything.

Q5: Can Excel remove duplicates across multiple sheets?

Not directly. Excel's Remove Duplicates tool works on one sheet at a time. For cross-sheet duplicate removal, use Power Query (Method 4) — you can combine multiple sheets into a single query and remove duplicates there.

Q6: How do I remove duplicate rows in Excel but keep the total?

If you have duplicate rows that you want to consolidate (e.g., combining quantities), use:

SUBTOTAL or SUMIF to aggregate the values

Then use Remove Duplicates on the unique identifiers

Or use a Pivot Table — it automatically consolidates duplicates.

Q7: Does the UNIQUE function remove duplicates?

Yes! The UNIQUE function (available in Excel 365 and 2026) returns a list of unique values from a range. It's a formula-based approach that doesn't modify your original data.

Q8: How do I use AI to remove duplicates in Excel?

Use an AI tool like Claude AI to generate a VBA macro or Power Query script. Simply describe your data and what you want cleaned. The AI produces the code in seconds. The [Mastering Claude AI for Excel](/mastering-claude-ai-for-excel) ebook includes 25+ data cleaning prompts ready to use.

---

Conclusion

Knowing how to remove duplicates in Excel is one of the most time-saving skills you can learn. Whether you use the 10-second built-in tool, write advanced formulas, set up Power Query for recurring tasks, or leverage AI automation for bulk operations — the right method depends on your specific needs.

Here's your quick decision guide:

• 10 seconds needed? → Method 1: Remove Duplicates tool
• Need to inspect first? → Method 2: Conditional Formatting
• Complex business rules? → Method 3: Advanced Formulas
• Large or recurring data? → Method 4: Power Query
• Hundreds of files? → Method 5: AI Automation (Claude AI)
• One-click recurring cleanup? → Method 6: VBA Macro

Ready to take your Excel skills to the next level? The [Mastering Claude AI for Excel](/mastering-claude-ai-for-excel) ebook gives you 200+ ready-to-use prompts, 25 professional templates, and the complete system to automate Excel tasks like duplicate removal, data cleaning, and reporting. Over 2,400 professionals have already transformed their workflow. Get instant access for only $7.99 — less than a coffee, with a 30-day money-back guarantee.

[⚡ Get Instant Access — $7.99 →](/mastering-claude-ai-for-excel)