How to Remove Duplicates in Excel (5 Easy Methods) | 2026

Removing duplicates in Excel means deleting repeated rows or values so that only unique entries remain in your data. Excel gives you five reliable ways to do this: the built-in Remove Duplicates tool, Conditional Formatting, the UNIQUE() formula, Power Query, and COUNTIF-based formulas. Which one you should use depends on whether your data needs a one-time cleanup or a repeatable, refreshable process — and I'll walk you through all five below.

Now let me guess — you opened a spreadsheet today, scrolled down, and thought, "Wait... didn't I already see this row?"

Yeah. I've been there too.

Duplicate data is one of those silent little productivity killers. It sneaks in from imports, copy-paste mistakes, or merged files, and before you know it, your sales report is lying to you. It's the spreadsheet version of a cluttered downloads folder — and just like decluttering your digital files, a little cleanup goes a long way.

So today, I'm going to walk you through exactly how to remove duplicates in Excel — every method, every shortcut, and every mistake you'll want to avoid. No fluff. Just the stuff that actually works.

What Counts as a "Duplicate" in Excel, Anyway?

Before you start deleting things, let's get on the same page.

In Excel, a duplicate value is any entry that appears more than once within a selected range, column, or table. It could be a repeated name, a duplicate invoice number, or an entire row that's identical to another one further up.

Microsoft's own documentation confirms the basic mechanic: you select Data → Remove Duplicates, choose which columns to check, and Excel keeps the first occurrence while deleting the rest.

And here's the thing — not all duplicates are bad. Sometimes repeated values are totally intentional (think recurring monthly expenses). So before you nuke anything, you'll want to actually look at what you're removing, not just blindly click a button.

Quick Comparison: Which Method Should You Use?

Before we dive into steps, here's the at-a-glance version so you can jump straight to the method that fits your situation.

Keep this table handy — I'll go through each row in detail next.

Step 1: Back Up Your Data First (Seriously, Don't Skip This)

I'm going to be blunt here — this step gets skipped constantly, and it's the one that causes the most heartbreak.

Excel's built-in duplicate removal tool is permanent. According to Microsoft's support documentation, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values — because Ctrl+Z only saves you if you haven't closed the workbook yet.

So here's what you do:

• Copy your original data to a new worksheet or a separate file

• Label it clearly, like "Backup — Do Not Touch"

• Then start cleaning

Trust me on this one. Five extra seconds now beats an afternoon of regret later.

Step 2: Find Duplicates Before You Delete Them

Honestly? My favorite way to start isn't deleting anything at all — it's seeing the duplicates first.

Use Conditional Formatting to Highlight Duplicates

This is a completely non-destructive method, which means nothing gets deleted. You're just making duplicate values visually obvious so you (a human with actual judgment) can decide what to do with them.

Here's how:

1. Select the range of cells you want to check

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

3. Choose a color that stands out (I like a strong red or yellow fill)

4. Click OK

Now every repeated entry lights up like a warning sign. Zapier's editorial team makes a similar point in their own guide to finding duplicates: if you simply want to find duplicates so you can decide whether to delete them, your best bet is to highlight all duplicate content using conditional formatting. It's a step a lot of beginner tutorials skip entirely.

Step 3: Remove Duplicates Using Excel's Built-In Tool

Okay, now let's get to the main event — the classic Remove Duplicates feature.

This is the fastest way to clean a dataset, and it's baked directly into Excel's ribbon.

Step-by-Step:

1. Click anywhere inside your data range or table

2. Go to the Data tab

3. In the Data Tools group, click Remove Duplicates

4. A dialog box pops up, listing all your columns

5. Check the columns you want Excel to compare (uncheck any you want to ignore — like a "Notes" column that shouldn't factor in)

6. Click OK

Excel will tell you exactly how many duplicate values were removed and how many unique rows remain. Clean, fast, done.

One quirk worth knowing: this tool always keeps the first occurrence of a duplicate and deletes the rest. So if you want to keep a specific row instead, sort your data first so your preferred version lands at the top.

Step 4: Try the UNIQUE() Formula (For Excel 365 Users)

If you're running Excel 365 or Excel 2021+, you've got access to something genuinely elegant: the UNIQUE function.

According to Xelplus, the UNIQUE function was introduced in 2021 along with several other powerful array functions, and its purpose is to return unique values — without touching your original data at all. It spits out a fresh, deduplicated list somewhere else on your sheet.

Here's the basic syntax:

=UNIQUE(range)

So if your data lives in column A, you'd type something like =UNIQUE(A2:A100) into an empty cell, hit enter, and watch Excel "spill" a clean list of unique values right there.

Pro tip: this method is fantastic for dashboards and reports, because your source data stays untouched while your summary stays clean and refreshes automatically as new data comes in.

Step 5: Use Power Query for Bigger, Repeatable Jobs

Now, if you're dealing with large datasets — or data that updates regularly — manually clicking Remove Duplicates every single time gets old fast.

This is where Power Query becomes your best friend.

Why Power Query Wins for Recurring Data Cleanup:

ExcelChamps sums up the appeal well: Power Query lets you preview which rows will be removed before applying changes, keeps the original data intact since transformations apply only to the query output, and can automatically update duplicate removal when the data is refreshed.

How to Do It:

1. Select your data and convert it to a table (Ctrl+T)

2. Go to Data → From Table/Range

3. In the Power Query Editor, right-click the column header

4. Choose Remove Duplicates

5. Click Close & Load

Set it up once, and you'll never have to repeat the manual process again.

Common Mistakes People Make When Removing Duplicates

Let's talk about a few gotchas that trip up even experienced users.

Case Sensitivity

Both the standard Remove Duplicates tool and the UNIQUE formula are case-insensitive. Xelplus notes that Power Query, on the other hand, treats differently-cased text as separate and distinct records. If your data relies on capitalization mattering, keep this in mind.

Blank Rows Get Treated as Duplicates

This one surprises people constantly. Multiple blank cells in a column can get flagged as duplicates of each other, which means an entirely empty row might vanish along with genuinely repeated data — even though nothing was technically repeated in any meaningful sense.

Selecting the Wrong Columns

If you only check one column when your "duplicate" actually depends on two or three columns matching, you'll delete rows that shouldn't have been touched. Always double-check which columns actually define a duplicate in your dataset before clicking OK.

Removing Duplicates Based on Multiple Columns

Sometimes a row only counts as duplicate when several fields match — say, both First Name and Last Name.

To handle this:

1. Select your data

2. Go to Data → Remove Duplicates

3. Check all the columns that need to match simultaneously

4. Click OK

Excel will only remove rows where every checked column is identical, letting you keep, say, two people who share a first name but have different last names.

Frequently Asked Questions

Does Remove Duplicates delete data permanently?

Yes. Once you run Remove Duplicates and save the file, the deleted rows are gone unless you had backed up a copy beforehand. Undo (Ctrl+Z) only works before you close the workbook.

Is Remove Duplicates case-sensitive in Excel?

No. The built-in Remove Duplicates tool and the UNIQUE() formula both treat "Excel" and "excel" as the same value. Power Query is the exception — it treats different capitalization as distinct entries.

Can I remove duplicates without deleting any rows?

Yes. Use Conditional Formatting to highlight duplicates for review, or use the UNIQUE() formula to generate a separate, clean list while leaving your original data untouched.

How do I remove duplicates based on two or more columns?

Select your data, go to Data → Remove Duplicates, and check all the columns that need to match simultaneously. Excel will only remove a row if every checked column is identical to another row.

Why did Excel delete a row that wasn't actually a duplicate?

This usually happens with blank rows. Excel can treat multiple empty cells in the same column as duplicates of each other, so filter out blank rows before running the tool if this is a risk.

What's the fastest method for a one-time cleanup?

The built-in Remove Duplicates tool under the Data tab is the quickest option for a single cleanup pass. For recurring or large datasets, Power Query is generally the better long-term choice.

Does this work the same way in older versions of Excel?

The Remove Duplicates tool and Conditional Formatting work in all modern Excel versions. The UNIQUE() formula requires Excel 365 or Excel 2021 and later.

Wrapping It Up

Removing duplicates in Excel doesn't have to be scary or destructive. You've now got five solid methods in your toolkit — from a quick built-in button to a fully automated Power Query pipeline.

My honest advice? Start with Conditional Formatting to see what you're dealing with, back up your data, and then pick whichever removal method actually fits your workflow.

Clean data leads to accurate decisions. And now, you know exactly how to get there.