How to Remove Duplicates in Excel: 5 Methods That Actually Work

Last month, I was working with a client who had a 50,000-row spreadsheet of customer data. The problem? About 8,000 of those rows were duplicates. Sales reports were off by 20%, email campaigns were going to the same people multiple times, and nobody knew which data to trust.

Sound familiar? Duplicate data is like that one friend who shows up twice to every party—annoying, hard to spot, and messes up the headcount every time.

Method 1: The Quick and Dirty Way (Remove Duplicates Button)

Let's start with the easiest method. Excel's built-in Remove Duplicates tool is like a Roomba—it gets the job done without much thinking.

Steps:

1. Select your data range (Ctrl+A is your friend)
2. Click the Data tab on the ribbon
3. Look for "Remove Duplicates" (it's usually on the right side)
4. A dialog box pops up—choose which columns to check
5. Click OK and hold your breath

Excel will tell you how many duplicates it found and removed. If it says 0 but you know there are duplicates, you probably selected the wrong columns. Happens to the best of us.

Method 2: The "I Want to See What I'm Deleting" Approach (Conditional Formatting)

Sometimes you don't want to blindly delete. Maybe you want to review duplicates first. This is where conditional formatting saves the day.

Here's what I do:

• Select your data
• Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
• Boom! Every duplicate lights up like a Christmas tree

I use this method when I'm dealing with sensitive data. Last week, I was cleaning a contact list and found that 50 "different" entries were actually the same person with slightly different spellings (think "John Smith" vs "Jon Smith"). The visual highlight helped me catch these before deleting.

Method 3: The Power User Move (Advanced Filter)

This is my go-to when I need more control. Advanced Filter lets you copy unique values to another location, leaving your original data untouched.

Why I love this method:

• Original data stays safe (no "oops" moments)
• You can see exactly what's being kept
• Works great for large datasets

The trick is to select "Copy to another location" and check "Unique records only". I use this about 80% of the time because it gives me a clean sheet without the anxiety of permanent deletion.

Method 4: The Formula Nerd Approach (COUNTIF)

Okay, this one requires a bit of Excel comfort, but it's incredibly powerful. COUNTIF lets you flag duplicates with a formula.

=COUNTIF(A:A, A2)>1

Put this in a helper column, and it'll show TRUE for duplicates. You can then filter and review them at your leisure.

A consultant taught me this trick back in 2021, and it's been a game-changer for complex datasets where duplicates aren't always obvious—like when order numbers should be unique but customer names might repeat legitimately.

Method 5: The "I Do This Every Day" Power Query Solution

If you're cleaning data regularly, Power Query is worth learning. It's like having a personal assistant that remembers exactly how you like things done.

Quick setup:

1. Select your data and go to Data → From Table/Range
2. In Power Query, right-click the column you want to check
3. Select "Remove Duplicates"
4. Load the results back to Excel

The best part? You can save these steps and reuse them on new data with one click. I have a saved query that cleans my weekly sales reports in about 10 seconds—used to take me 20 minutes.

Common Mistakes I See (And Made Myself)

• Not checking headers: Excel might think "First Name" is a duplicate and delete it. Always check that box!
• Ignoring whitespace: "John" and "John " look the same but Excel sees them differently. Use TRIM() first.
• Deleting without reviewing: I once deleted 200 legitimate entries because I was in a hurry. Don't be like me.