How to Delete Duplicates in Excel: 5 Step-by-Step Methods (2023 Guide)

Okay, let's talk duplicates in Excel. We've all been there. You're staring at a massive list – customer emails, product SKUs, transaction IDs – and suddenly you see double. Or triple. Maybe even more. It's messy, it throws off your totals, and honestly? It's just plain annoying. So, how do you delete duplicates in Excel effectively? That's the million-dollar question, and trust me, there's more than one way to tackle it. I've messed this up before (lost a whole afternoon's work once... don't ask!), so I'll walk you through every method, the good, the bad, and the slightly tricky, just like I'd explain it to a colleague needing help right now.

Before You Zap Those Dupes: The Non-Negotiable First Step

Seriously, don't skip this. I learned the hard way. Deleting duplicates in Excel is permanent. Once you hit that button, the extra copies are gone. Poof. Forever. So:

Back Up Your Data: Select your entire data range. Copy it (Ctrl+C). Open a new worksheet (or even a new workbook). Paste it (Ctrl+V). Name this sheet something obvious like "BACKUP_Original". Now you can breathe easy.
Identify WHAT is Duplicate: Are duplicates based on one column (like an Email address)? Multiple columns together (like First Name + Last Name + Postcode)? Knowing this is crucial for choosing the right method to remove duplicate rows in Excel.
Check for Hidden Rows/Columns: If you filter or hide stuff, it can mess up what gets deleted. Unhide everything (Home tab -> Format -> Hide & Unhide -> Unhide Rows/Unhide Columns) and clear any filters (Data tab -> Clear).

Got merged cells? Oh boy. Excel's Remove Duplicates feature HATES merged cells. It often just errors out. You'll probably need to unmerge them first (select merged cells -> Home tab -> Merge & Center -> Unmerge Cells) and figure out how to handle the now-empty cells. Painful, I know.

Method 1: The Built-in Button (Fast & Easy, But Watch Out!)

This is the go-to for most people asking how do you delete duplicates in Excel. It's quick.

Step-by-Step Guide

Select Your Data: Click anywhere inside your data table. If Excel doesn't automatically select the whole range, drag to select the cells you care about, including headers.
Find the Magic Button: Go to the Data tab on the ribbon. Look in the Data Tools group. See it? The button that says Remove Duplicates. Click that.
Tell Excel What Makes a Duplicate: A dialog box pops up. Here's where you decide. Does your data have headers? Check "My data has headers" (usually yes!). Now, look at the list of columns. By default, ALL columns are selected. That means Excel will only delete a row if every single column has identical data to another row. Is that what you want? Often, it's overkill.

Example: You have sales data. Columns: OrderID, CustomerID, ProductID, Date, Amount. If you leave all columns checked, only rows where absolutely every detail is identical will be considered duplicates (rare in sales). But if you only care about duplicate OrderIDs (meaning the same order was entered twice), you uncheck all columns, then only check "OrderID". Got it?

Hit OK and Breathe: Click OK. Excel does its thing. A message tells you how many duplicates were found and removed, and how many unique values remain. Click OK again.

Pros and Cons of the Remove Duplicates Feature

Pros	Cons
Super fast and easy for simple tasks.	Destructive! Removes data permanently. No undo after you close the file.
Directly built into Excel.	Often selects ALL columns by default, which might not be what you need.
Gives a clear count of removed dupes and unique items.	Struggles badly with merged cells. Usually just errors.
Works well when you clearly define the duplicate criteria.	Offers no preview. You commit without seeing what will be deleted first.
	Doesn't let you choose which duplicate to keep (e.g., keep the latest date). It keeps the first occurrence and deletes the rest.

So yeah, it's handy, but it has limitations. If you need more control, or want to delete duplicate rows in Excel without losing data right away, keep reading.

Method 2: Conditional Formatting - Find Dupes Before You Delete

Want to see the duplicates before you nuke them? This is my preferred safety net. It highlights the offenders visually. Great for spotting patterns or weird data.

Highlighting Duplicates Step-by-Step

Select Your Target Range: Click and drag to select the specific cells you want to check for dupes (e.g., just the Email column).
Open Conditional Formatting: Go to the Home tab. Find the Styles group. Click Conditional Formatting.
Navigate to Duplicate Values: Hover over Highlight Cells Rules. Then choose Duplicate Values... from the flyout menu.
Pick Your Highlight Style: A small dialog appears. It should say "Duplicate" in the first dropdown. Then, choose how you want them highlighted from the second dropdown (Light Red Fill with Dark Red Text is common, but pick any color that stands out). Click OK.

Bam! Every duplicate value in your selected column is now brightly colored. Instant visual cue. Now you can scan, decide if those *should* be dupes (sometimes legit dupes exist!), or start the deletion process more confidently knowing what will go.

Want to find unique values instead? In step 4, choose "Unique" from the first dropdown.

Okay, I See Them! How Do I Actually Delete Them Now?

Conditional Formatting just highlights; it doesn't delete. Now you have choices:

Manual Delete (Small Lists): Sort your data by the highlighted column (click the column header, then Data Tab -> Sort A to Z). All the highlighted dupes will group together. You can then visually select entire rows and delete them manually (right-click -> Delete -> Entire Row). Tedious for large lists.
Filter by Color: Click the filter dropdown arrow in your highlighted column's header. Hover over Filter by Color. Choose the fill color you used for duplicates. Now only the duplicate rows are visible. Select all these visible rows (click the first row number, scroll down, hold Shift, click the last row number). Right-click -> Delete Row. Remember to clear the filter afterward (Data tab -> Clear).
Use the Remove Duplicates Button: Since you've visually confirmed what will be removed, you can now use Method 1 (Data Tab -> Remove Duplicates) with more confidence, selecting only the relevant column(s).

Bonus Tip: Need dupes based on multiple columns? Select all relevant columns first (hold Ctrl to select non-adjacent ones), then apply Conditional Formatting using the "Use a formula to determine which cells to format" rule. The formula gets complex (like `=COUNTIFS($A:$A, $A1, $B:$B, $B1) > 1` assuming data starts row 1, checking cols A & B). Requires intermediate skills but powerful.

Method 3: Advanced Filtering - More Control Over Unique Records

This method is fantastic if you want to delete duplicates in Excel by extracting the unique records to a new location first. Zero risk to your original data. Perfect for creating clean lists.

Using Advanced Filter to Extract Unique Records

Select Your Data: Click anywhere inside your data range.
Open Advanced Filter: Go to the Data tab. Find the Sort & Filter group. Click Advanced.
Configure the Dialog:
- Action: Choose Copy to another location.
- List range: Should automatically show your selected data range. Verify it.
- Copy to: Click in this box, then click on the cell in your worksheet where you want the unique list to start (e.g., the top-left cell of a blank area).
- Check the box: Unique records only. This is the key!
Execute: Click OK.

Excel instantly copies only the unique rows from your original data to the new location you specified. Your original data remains completely untouched. Now you have a pristine, duplicate-free list. You can rename this new list, work with it, and if you're happy, you can delete or archive the original messy data.

This is honestly one of the safest ways to remove duplicate rows in Excel, especially for critical data. No permanent deletion happens until you decide to get rid of the old sheet.

Method 4: Power Query (Get & Transform) - The Heavyweight Champion

For massive datasets, complex criteria, or needing to automate the deduplication process repeatedly, Power Query (called Get & Transform in some Excel versions) is unmatched. It feels a bit intimidating at first, but it's incredibly powerful and non-destructive. You load data, transform it (remove dupes), and only load the clean result back. The original source stays pristine.

Here's how to delete duplicates in Excel using Power Query:

Load Your Data into Power Query: Select any cell in your data table. Go to the Data tab. In the Get & Transform Data group, click From Table/Range. Ensure "My table has headers" is checked. Click OK. This opens the Power Query Editor window.
Choose Columns for Duplicate Check: In the Power Query Editor, you'll see your data. Select the column(s) you want to base duplicate removal on. Hold Ctrl to select multiple columns. If you want to deduplicate entire rows, select ALL columns.
Remove Duplicates: With the column(s) selected, go to the Home tab within the Power Query Editor ribbon. In the Reduce Rows group, click Remove Duplicates. The duplicates based on your selected columns vanish instantly from the preview.
Apply and Load: Click Close & Load (or just "Close & Load To..." if you want options) on the Home tab. Power Query creates a new worksheet with your clean, duplicate-free data. The original data remains unchanged.

Why Power Query Rocks for Deduping

Non-Destructive: Your source data is safe. Always.
Handles Complexity: Easily dedupe based on multiple columns, partial matches (with transforms), case sensitivity (needs an extra step), or even fuzzy matching (finding "close" dupes like typos).
Choose Which Duplicate to Keep: Sort your data within Power Query (e.g., sort by "Date" descending) before removing duplicates. Power Query keeps the *first* row in the current order for each unique set. So sorting lets you keep the latest, earliest, largest, etc.
Automation: If your source data updates (e.g., a CSV you refresh), just right-click the result table and hit "Refresh". Power Query reruns all your steps (including deduping) automatically.
Handles Millions of Rows: Much better performance than standard Excel functions for huge data.

Learning curve? A bit. Worth it for serious data cleaning? Absolutely. If you find yourself constantly asking how do you delete duplicates in Excel for large or recurring tasks, invest some time in Power Query.

Method 5: Formula Power - Identify and Isolate Duplicates

Sometimes you just need to flag duplicates for review, not delete them immediately. Formulas are perfect for this. They add a helper column saying "Dupe" or "Unique". You can then filter, sort, or make decisions based on that flag. Let's look at the most common formulas for spotting duplicates.

Common Duplicate Identification Formulas

Formula	What It Does	Example (Assume data starts row 2, checking column A)	Result Meaning
`=COUNTIF($A$2:$A$100, A2) > 1`	Checks if the value in current row (A2) appears more than once in the range A2:A100.	Enter in cell B2, drag down.	TRUE = Duplicate (appears more than once). FALSE = Unique (appears once only). Note: The first occurrence of a value also gets TRUE if it repeats later!
`=COUNTIF($A$2:A2, A2) > 1`	Checks if the value in current row (A2) has already appeared above it in the list (within A2 to itself).	Enter in cell B2, drag down.	TRUE = Duplicate (not the first occurrence). FALSE = Either unique OR the first occurrence of a duplicate. This flags extra copies, not the first one.
`=IF(COUNTIF($A$2:A2, A2) > 1, "Duplicate", "Unique")`	Same logic as above, but outputs clear text labels.	Enter in cell B2, drag down.	"Duplicate" = Not the first occurrence. "Unique" = First occurrence or truly unique.

Handling Duplicates Based on Multiple Columns

Want to flag duplicates only if, say, First Name and Last Name and Email match? Use `COUNTIFS`:

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2, $C$2:$C$100, C2) > 1

(Adjust column letters/ranges to match your data). This counts rows where Col A = A2 AND Col B = B2 AND Col C = C2. >1 means duplicate.

What to Do with the Flagged Data

Once you have your helper column (say Column B) filled with TRUE/FALSE or "Duplicate"/"Unique":

Filter: Filter Column B for TRUE or "Duplicate". Now only duplicate rows are visible. You can review them, delete them manually (select visible rows -> right-click -> Delete Row), or apply another action.
Sort: Sort by Column B to group all duplicates together for easier review.
Conditional Formatting: Based on your formula result (e.g., format cells where formula is TRUE). Works alongside the helper column.

Formulas give you maximum flexibility and visibility before you decide how to delete duplicate rows in Excel. They are calculation-heavy for huge datasets though.

Troubleshooting Common Duplicate Deletion Headaches

Things rarely go perfectly. Here are some snags I've hit:

"Duplicates" That Aren't Really Dupes: Extra spaces are the classic culprit! "John Doe" vs "John Doe " (with trailing space) look the same but Excel sees them as different. Solution: Use the `TRIM()` function on text columns before deduping (`=TRIM(A2)` in a helper column, copy/paste values over original). Also watch for case sensitivity ("APPLE" vs "apple") - Excel's standard tools ignore case. If case matters, use Power Query or helper formulas (`=EXACT()`).
Partial Matches: Need to dedupe based on part of a cell? Like just the domain from an email? You'll need to extract that part first (using formulas like `=RIGHT(A2, LEN(A2)-FIND("@",A2))` for domain, or Text to Columns) before deduping.
Merged Cells Nightmare: As mentioned earlier, Excel's Remove Duplicates and many formulas choke on merged cells. You usually need to unmerge first. Try "Center Across Selection" (Format Cells -> Alignment -> Horizontal dropdown) for visual centering without merging, if that's why you merged.
Data Loss Panic (No Backup!): If you deleted dupes and saved without a backup... recovery is tough. Try Ctrl+Z repeatedly immediately after. If you closed and reopened? Check File -> Info -> Manage Workbook -> see if an unsaved version or auto-recover file exists. Otherwise... learn for next time. Backups are essential!
Formula Dependency Breakage: If your deleted rows were referenced by formulas elsewhere, those formulas might now show `#REF!` errors. You'll need to adjust the formulas or rethink the deletion. Check dependent formulas before deleting large chunks.

Pro Tip - Fuzzy Matching: Need to find "close" duplicates? Like "New York" vs "New-York" vs "new york". Power Query has a "Fuzzy Matching" option when merging queries that can handle this (based on similarity thresholds). It's advanced but incredibly useful for messy real-world data.

Decision Time: Which Method Should YOU Use to Delete Duplicates in Excel?

Alright, information overload! Here's a cheat sheet to pick the best way to delete duplicates in Excel for YOUR situation:

Your Situation / Need	Best Method(s)	Why
Quick removal, simple criteria, small dataset, okay with permanent deletion	Remove Duplicates Button (Method 1)	Fastest, built-in, direct.
See the duplicates first before deleting anything	Conditional Formatting (Method 2) + Filter/Manual Delete OR Remove Duplicates	Visual safety net, control over deletion method.
Create a clean list without altering the original data	Advanced Filter (Method 3)	Safe, easy extraction of uniques.
Large datasets, complex criteria (multi-column, partial match), case sensitivity, need to automate, choose which dupe to keep	Power Query (Method 4)	Most powerful, non-destructive, handles complexity, automatable.
Flag duplicates for review/analysis without immediate deletion, maximum flexibility	Formula Flags (Method 5) + Filtering	Full visibility, control, works with other analysis.
Merged cells are involved	Unmerge first, then ANY method. Or use Power Query carefully (it can sometimes handle them on load).	Most tools fail on merged cells directly.

Honestly? For anything beyond a tiny list I touch once, I lean heavily towards Conditional Formatting for safety checks or Power Query for serious cleaning. The built-in button is fast but feels like playing Russian roulette with my data sometimes.

Your Excel Duplicate Deletion Questions Answered (FAQ)

Q: Does Excel remove duplicates keep the first or last instance?

A: Excel's built-in "Remove Duplicates" feature (Method 1) always keeps the first occurrence of the duplicate set it finds in your data range and deletes all subsequent duplicates. It gives you no choice. If you need to keep the last instance (e.g., the most recent transaction), you must sort your data first (e.g., sort your "Date" column descending so the newest date is at the top). Then run Remove Duplicates. Now the "first" occurrence is the newest one. Power Query (Method 4) also keeps the first occurrence in its current order, so you can sort there too before deduping.

Q: How do I remove duplicates in Excel but keep blank rows?

A: Blank cells are treated like any other value. If you have multiple completely blank rows, they will be considered duplicates of each other. The Remove Duplicates tool will delete all but one blank row. If you have blank cells *within* rows that aren't entirely blank, those rows are evaluated based on the columns you select. If you specifically want to keep all blank rows regardless, it's tricky. One way: Add a helper column with a formula like `=IF(COUNTA(A2:Z2)=0, "Keep", "")` (adjust range to cover your row). This marks truly blank rows with "Keep". Then, when using Remove Duplicates or Advanced Filter, include this helper column but don't select it for deduping criteria. The "Keep" tag will prevent truly blank rows from being considered duplicates of each other. Power Query gives you more nuanced control over handling blanks during deduplication.

Q: Can I remove duplicates based on only one column but keep data in other columns?

A: Absolutely. This is very common! When using the Remove Duplicates dialog (Method 1), simply uncheck all columns except the one you care about (e.g., just "Email Address"). Excel will delete entire rows where the email address is a duplicate, keeping only the first row with that email. All other column data in the kept row remains intact. Similarly, in Conditional Formatting, Advanced Filter, Power Query, or Formulas, you select only the specific column(s) you want to define the duplicate criterion. The tool then acts on the entire row associated with duplicates in that column.

Q: Why isn't Excel seeing my duplicates? They look the same!

A: This is almost always due to invisible characters:

Spaces: Leading, trailing, or extra spaces between words. Use `=TRIM(A2)` in a helper column, copy, then Paste Values over the original data.
Non-breaking spaces: Often copied from web pages (ASCII 160). `TRIM` won't remove these! Use `=SUBSTITUTE(A2, CHAR(160), " ")` first, then `TRIM`.
Case Sensitivity: Excel's standard tools ignore case ("APPLE" = "apple"). If case matters, you need Power Query (with case-sensitive option) or formulas using `=EXACT(A2, B2)`.
Formulas vs. Values: A cell with the formula `="Cat"` looks identical to a cell with just the value "Cat", but they are technically different. Copy the column and Paste Values to convert formulas to static values before deduping.
Numerical Precision: Very long decimals might *look* rounded the same but differ slightly internally. Use the `ROUND()` function if appropriate.

Check cell contents carefully using the formula bar! Conditional Formatting using the "Duplicate Values" rule can sometimes highlight differences invisible to the naked eye.

Q: Is there a shortcut key to remove duplicates?

A: There isn't a single dedicated shortcut key like F7 for spelling. However, once you've selected your data, you can use the Alt key sequences: Press Alt, A, M consecutively (not held down). This activates the Data tab (Alt+A) and then clicks the Remove Duplicates button (M is its accelerator key). So Alt+A, M is the closest thing to a shortcut.

Q: How can I remove duplicates in Excel Online or Google Sheets?

A: The core concepts are similar:

Excel Online: The Remove Duplicates button is on the Data tab, working much like the desktop version. Conditional Formatting and Filtering are also available.
Google Sheets:
- Menu: Data -> Data Cleanup -> Remove duplicates. Very similar dialog to Excel.
- Conditional Formatting: Format -> Conditional formatting -> Format rules: "Custom formula is" -> Use `=countif(A:A, A1)>1` (adjust range).
- Unique Function: `=UNIQUE(A:C)` (to extract unique rows from Cols A-C to a new location). Similar to Excel's Advanced Filter.

Wrapping It Up: Clean Data, Sane Mind

Figuring out how do you delete duplicates in Excel isn't just about clicking a button. It's about understanding your data, knowing the risks (always backup!), and picking the right tool for the job – whether it's the quick button, the safety of Conditional Formatting, the extraction power of Advanced Filter, the muscle of Power Query, or the precision of formulas. Each method solves the core problem of deleting duplicates in Excel but in ways suited to different needs. Start simple, but don't be afraid to dive into Power Query if you handle data regularly. Getting rid of those pesky doubles makes your data more accurate, your calculations trustworthy, and honestly, just makes your spreadsheet look a whole lot better. Less clutter, less stress. Now go clean up that data!

September 26, 2025