How to Delete Duplicate Entries in Excel (And Keep Your Data Clean)

Duplicate entries in Excel are one of the most common data problems — and one of the easiest to fix once you know where to look. Whether you're managing a contact list, cleaning up a financial report, or merging data from multiple sources, Excel gives you several ways to find and remove duplicates. The right approach depends on what you're working with and how much control you need over the process.

Why Duplicates Happen in Excel

Duplicates creep in for predictable reasons: copy-paste errors, data imports from multiple sources, form submissions that didn't deduplicate automatically, or collaborative spreadsheets where multiple people entered the same record. The tricky part is that "duplicate" doesn't always mean identical — sometimes two rows share a key value (like an email address) but differ in other columns. That distinction matters a lot before you start deleting anything.

Method 1: The Remove Duplicates Tool (Built-In and Fast)

Excel's Remove Duplicates feature is the quickest option for most users.

1. Select any cell inside your data range or table.
2. Go to the Data tab on the ribbon.
3. Click Remove Duplicates in the Data Tools group.
4. A dialog box appears showing all your columns. You can check or uncheck columns to define what counts as a duplicate.
5. Click OK — Excel removes duplicate rows and tells you how many were deleted.

This method is destructive by default, meaning duplicates are deleted immediately. Always work on a copy of your data, or use Ctrl+Z to undo if the result looks wrong.

The column selection step is important. If you check all columns, Excel only removes rows where every single cell matches. If you check just one column — say, Email Address — it removes any row where that field repeats, keeping the first occurrence and deleting the rest.

Method 2: Conditional Formatting to Highlight Duplicates First 🔍

If you want to see duplicates before deleting them, Conditional Formatting lets you flag them visually.

1. Select the column or range you want to check.
2. Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
3. Choose a highlight color and click OK.

Duplicated values will be color-coded instantly. From there, you can manually review and decide which rows to delete. This is especially useful when duplicates aren't clear-cut — for example, when you want to keep the most recent entry rather than the first one.

Method 3: Using COUNTIF to Identify Duplicates with a Formula

For more control, the COUNTIF function lets you flag duplicates with a helper column.

Add a new column next to your data and enter a formula like:

This counts how many times the value in column A has appeared up to the current row. A result of 1 means it's the first occurrence. Any number greater than 1 means it's a duplicate. You can then filter for values greater than 1 and delete those rows.

This approach gives you the most flexibility — you can define exactly what you consider a duplicate, combine multiple columns in the logic, and choose which occurrence to keep.

Method 4: Power Query for Large or Recurring Data Sets

If you're working with large datasets or need to clean duplicates regularly, Power Query (available in Excel 2016 and later, and Microsoft 365) is worth learning.

1. Select your data and go to Data > From Table/Range.
2. In the Power Query editor, select the columns that define a duplicate.
3. Go to Home > Remove Rows > Remove Duplicates.
4. Click Close & Load to push the cleaned data back to Excel.

Power Query keeps your original data intact and creates a separate output. It's also repeatable — once your query is set up, refreshing it with new data applies the same deduplication rules automatically. That makes it the preferred approach for ongoing data cleaning workflows.

Key Variables That Affect Which Method Works Best

What "Duplicate" Actually Means for Your Data 🧹

This is where most data cleaning mistakes happen. Excel treats exact matches as duplicates — so "John Smith" and "john smith" are not the same unless you normalize the case first. Extra spaces, different date formats, or inconsistent capitalization can all fool Excel's tools into missing duplicates that are obvious to a human reader.

Before running any deduplication, it's worth cleaning up formatting inconsistencies using functions like TRIM (removes extra spaces), UPPER or LOWER (standardizes case), and TEXT (normalizes date formats). Running deduplication on messy data can leave genuine duplicates behind or — worse — delete rows that weren't actually the same.

The Spectrum of Use Cases

A freelancer cleaning a 200-row client list can use Remove Duplicates in under a minute and be done. A data analyst importing 50,000 records from multiple CRM exports needs Power Query with a clearly defined key column strategy. A small business owner spot-checking a shared spreadsheet might prefer Conditional Formatting just to see what's going on before touching anything.

The tools scale from simple to powerful — but none of them can automatically know which rows matter in your specific dataset, which occurrence to preserve, or whether a near-match counts as a duplicate in your context. That judgment lives with whoever understands the data.