How to Delete Duplicates in Excel: 5 Quick Methods

Duplicate data is one of those silent spreadsheet killers. You run a SUM, and the total looks off. You pull a count for your report, and the number seems suspiciously high. Then you scroll through your data and realize — you've got the same rows entered two, five, maybe twenty times. It happens constantly, whether you're merging exports from two systems, copy-pasting data from emails, or working with a dataset that's been touched by a dozen different people.

Knowing how to delete duplicates in Excel — quickly and accurately — is one of the most useful skills you can have. This guide walks through five proven methods, from the one-click Remove Duplicates button to Power Query for large datasets, plus how to handle trickier situations like keeping only the first occurrence or flagging duplicates before you delete anything.

Why Duplicates Are a Problem

Before you delete anything, it's worth understanding why duplicates are so damaging. The most obvious issue is inflated counts — if you're counting customer records and the same customer appears three times, your headcount is wrong from the start. The same goes for totals: a duplicated sales row doubles that sale in every SUM formula that references the range.

Incorrect totals feed into reporting errors that compound fast. A dashboard pulling from a de-duped dataset looks completely different from one pulling from dirty data. Pivot tables are especially vulnerable — they'll happily summarize every duplicate as if it's a unique entry. And if you're using VLOOKUP or XLOOKUP with a dataset that's supposed to have unique keys, duplicates will cause the function to return only the first match and silently ignore the rest, which can lead to data being dropped from your analysis without any warning.

The good news: Excel gives you multiple ways to deal with this, ranging from a one-click button to formula-based approaches that let you inspect before you act.

Method 1: Remove Duplicates Button (Fastest)

The Remove Duplicates button in Excel's Data tab is the fastest, most direct way to clean duplicate rows. It's built in, it's free, and it works on datasets of any size. Here's how to use it step by step.

Step 1: Click anywhere inside your data range. You don't need to select the whole table — Excel will detect the edges automatically.

Step 2: Go to the Data tab on the ribbon, then click Remove Duplicates in the Data Tools group.

Step 3: A dialog box appears listing all your columns. By default, every column is checked. If you want Excel to consider a row a duplicate only when every column matches, leave them all checked. If you only want to remove rows where specific columns match (like email address), uncheck the columns that shouldn't be part of the comparison.

Step 4: Click OK. Excel removes all duplicate rows and shows you a summary: how many duplicates were removed and how many unique values remain.

Important: this method deletes duplicate rows in place — it modifies your original data. Always work on a copy or make sure you've backed up the file before running it. Excel keeps the first occurrence of each duplicate and removes the rest.

This is the right method for 80% of everyday situations. It's fast, it's clear, and it handles multi-column comparison. If you need more control — or want to see what you're deleting before it's gone — use one of the methods below instead.

Method 2: Advanced Filter for Unique Values

Advanced Filter is an underrated tool. Unlike Remove Duplicates, it doesn't delete anything from your original data — instead, it extracts unique rows to a new location. This is useful when you want to keep the original dataset intact and work with a clean copy in a separate range.

Step 1: Select your data range, including headers.

Step 2: Go to Data → Sort & Filter → Advanced.

Step 3: In the Advanced Filter dialog, choose Copy to another location. This tells Excel to write the results somewhere else rather than filtering in place.

Step 4: In the Copy to field, click the cell where you want the unique list to start — usually a cell in an empty column or a new sheet.

Step 5: Check the Unique records only checkbox. Click OK.

Excel pastes only unique rows to the destination range. Your original data is untouched. This method is great for creating a clean reference list without destroying the source, or when you're auditing data and want to compare the original against the de-duped version side by side.

One limitation: Advanced Filter considers the entire row when determining uniqueness. You can't easily limit it to specific columns the way you can with Remove Duplicates. If you need column-level control, Method 3 or Method 5 are better fits.

Method 3: COUNTIF Formula to Flag Duplicates First

Sometimes you want to see duplicates before you delete them. The COUNTIF function in Excel is perfect for this — it counts how many times a value appears in a range, so you can flag every duplicate row and inspect them before taking action.

Suppose your data is in column A (A2:A1000). Add a helper column next to your data and enter this formula in B2:

=COUNTIF($A$2:$A$1000, A2)

Drag it down for all rows. Any row where the result is greater than 1 is a duplicate — it appears more than once in column A. A result of exactly 1 means that value is unique.

To flag only the duplicates (not the first occurrence), use this instead:

=COUNTIF($A$2:A2, A2)>1

The expanding reference $A$2:A2 grows as you copy the formula down, so it counts how many times the current value has appeared up to this row. The first occurrence returns FALSE, and all subsequent duplicates return TRUE. This way you can filter for TRUE values, review them, and delete just those rows — keeping the first copy of each.

This approach takes a little more setup, but it gives you full visibility before you delete anything. It's ideal for datasets where you can't afford mistakes — financial data, customer lists, anything going into a report where accuracy matters.

Method 4: Conditional Formatting to Highlight Duplicates

Before you delete anything, you might want to see exactly which cells are duplicates — highlighted in color, right in the original table. Conditional formatting in Excel can do this automatically.

Step 1: Select the column (or range) you want to check for duplicates.

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

Step 3: Choose your highlight color and click OK. Excel immediately colors every duplicate value in the selected range.

Now you can scan through your data and see exactly what's duplicated. This is especially useful when you're not sure if your data should have duplicates — sometimes the same value in column A is legitimate because other columns differ. Seeing the highlights helps you make a judgment call before deleting blindly.

To delete after highlighting: use the filter drop-down on that column, or sort by color (right-click any highlighted cell → Sort → Put Selected Cell Color On Top). This brings all duplicates to the top so you can select and delete them in one go.

One thing to note: conditional formatting highlights the value wherever it appears — including the first occurrence. So if you sort by color and delete all highlighted rows, you'll delete every copy, not just the extras. Make sure to keep one row for each duplicate group if that's what you need.

Method 5: Power Query — Best for Large Datasets

If you're working with tens of thousands of rows — or data that comes from multiple sources that you regularly need to merge and clean — Power Query is your best tool. It's built into Excel (available since Excel 2016) and it handles deduplication non-destructively, which means your source data stays untouched.

Step 1: Click anywhere inside your data, then go to Data → From Table/Range. If your data isn't already formatted as a Table, Excel will prompt you to convert it — click OK.

Step 2: The Power Query Editor opens. You'll see your data in a clean grid.

Step 3: To remove all duplicate rows, go to Home → Remove Rows → Remove Duplicates. Power Query removes every row that's an exact duplicate of another.

Step 4: To remove duplicates based on specific columns only, select those columns first (hold Ctrl and click column headers), then right-click and choose Remove Duplicates. Power Query will keep the first row for each unique combination of those columns.

Step 5: Click Close & Load to write the cleaned data back to Excel — either as a new sheet or replacing the existing table.

The real power of Power Query is repeatability. Once you set up the deduplication step, you can refresh the query whenever your source data updates and the same cleaning logic runs automatically. This is huge if you're pulling data from a database export or a CSV that changes weekly. Pair this with a pivot table in Excel and your entire reporting workflow becomes nearly self-maintaining.

How to Remove Duplicates Based on Specific Columns Only

Sometimes a row isn't a true duplicate even if one column matches. Say you have a customer list with columns for Name, Email, and Purchase Date. Two rows might have the same email but different purchase dates — that's not a duplicate, that's the same customer buying twice. If you run Remove Duplicates on all columns, those rows will both survive (which is correct). But if you only want one row per email address regardless of purchase date, you'd uncheck Name and Purchase Date in the Remove Duplicates dialog and leave only Email checked.

This column-level control is one of the most useful and underused features of the Remove Duplicates dialog. You can check any combination of columns to define what "duplicate" means for your specific dataset. The same logic applies in Power Query — select the columns you care about, then right-click Remove Duplicates to dedupe by just those columns.

For the COUNTIF approach, just build your formula against the column(s) that define uniqueness rather than every column in the sheet. If uniqueness is defined by email, =COUNTIF($B$2:$B$1000, B2) flags any row where the email appears more than once — regardless of what's in the other columns.

Using Excel keyboard shortcuts can speed up the whole workflow: Ctrl+Shift+L toggles filters on and off, and Alt+D+F+F opens the Advanced Filter dialog without touching the mouse.

First Occurrence vs Last Occurrence — Which One Do You Keep?

By default, Excel's Remove Duplicates always keeps the first occurrence of a duplicate and deletes the rest. Most of the time that's what you want. But occasionally — say, you're working with timestamped records and you need the most recent version of each entry — you need to keep the last occurrence instead.

Excel doesn't have a direct "keep last" toggle, but you can work around it. Sort your data in reverse order (newest first, or highest row number first) before running Remove Duplicates. Since Excel always keeps the first row of each duplicate group, sorting in reverse means what was previously the last occurrence is now at the top — so that's what gets kept. After deduplication, re-sort in your preferred order.

With COUNTIF, you can get more precise. Use this formula to mark only the last occurrence of each duplicate as unique:

=COUNTIF(A2:$A$1000, A2)=1

This counts from the current row to the end. The last occurrence of a value has a count of 1 (it only appears once in that remaining range), while earlier occurrences have counts greater than 1. Filter for TRUE to isolate the last occurrence of each value.

For large datasets, Power Query handles this more elegantly — you can sort the query by a date or ID column descending, then remove duplicates to keep the most recent record for each key automatically.

Common Mistakes When Deleting Duplicates

The most frequent mistake is running Remove Duplicates without checking which columns are selected. If you leave all columns checked when your data has a timestamp or row ID column, almost nothing will be flagged as a duplicate — because no two rows match on every column. Always verify which columns should define uniqueness before you hit OK.

The second big mistake is not backing up. Remove Duplicates works in place. Once you save the file after running it, your deleted rows are gone. Excel's Undo (Ctrl+Z) works immediately after the operation, but not after you've closed and reopened the file. Make a copy first, every time.

A third mistake: assuming all highlighted cells should be deleted. Conditional formatting marks both the original and the duplicate. If you delete every highlighted row, you delete all copies — which may leave you with gaps in your data. Use the first-vs-last approach above to decide which copy survives.

Finally, watch out for near-duplicates — rows that look the same but aren't because of extra spaces, different capitalization, or leading zeros. "John Smith" and "john smith" are not the same to Excel. Use TRIM() to remove extra spaces and UPPER() or LOWER() to normalize case before running deduplication. Otherwise you'll think you've cleaned your data when the near-duplicates are still lurking.

Also worth cleaning: after deduplication you often find stray blank rows. Learning how to remove empty rows in Excel is a natural next step in the same data cleanup workflow.

Putting It All Together

Most of the time, the Remove Duplicates button is all you need. It's fast, it's built in, and it gives you column-level control right in the dialog. For anything more nuanced — reviewing before deleting, keeping the last occurrence, working with large datasets, or setting up a repeatable cleaning workflow — one of the other four methods will serve you better.

A practical approach: start with conditional formatting to highlight and inspect duplicates visually. Once you're confident in what's there, use COUNTIF to flag the exact rows you want to delete, then filter and delete those rows. Or if your dataset is clean enough that you trust a one-pass deletion, go straight to Remove Duplicates with the right columns checked.

If your work involves regular data imports and you're spending time on this same cleanup every week, Power Query is worth the investment. Set it up once, and every refresh does the deduplication automatically — no manual steps, no risk of forgetting to clean before you analyze.