INTRODUCTION
Removing duplicate entries in Excel is one of the most common tasks in data cleaning—and also a frequent interview question for roles like data analyst or financial analyst. In this guide, we’ll walk through three effective methods to remove duplicates in Excel, explain the differences between them, and show when to use each approach.
What Are Duplicates in Excel?
Before learning how to remove duplicates in Excel, it’s important to understand what counts as a duplicate. Consider a table with columns for Sales Agent, Region, and Sales Value.
For example:
| Sales Agent | Region | Sales Value |
|---|---|---|
| John | East | 30 |
| John | East | 30 |
| John | East | 35 |
The first and second rows are duplicates, while the third is not—because the sales value differs. So, a true duplicate must match across all columns.
Method 1: Using Excel’s Built-In “Remove Duplicates” Feature
Works in: All Excel versions
- Copy Your Data (Optional, but recommended)
- Press
Ctrl + Atwice to select the entire table (including headers) - Press
Ctrl + Cto copy - Paste to a new location for comparison
- Remove Duplicates
- Select any cell in the dataset
- Go to the Data tab → Click Remove Duplicates
- Make sure “My data has headers” is checked
- Select all columns involved
- Click OK
Example: 4 duplicate values removed, 2 unique rows remain.
Removing Duplicates from One Column Only
If you only want a list of unique Sales Agents, repeat the above steps but select only the Sales Agent column during the Remove Duplicates step.
Method 2: Use Excel’s UNIQUE Formula (Office 365/Web)
Works in: Office 365 Desktop & Excel Online
This method uses Excel’s dynamic array function UNIQUE():
=UNIQUE(TSales)Replace TSales with your table name or cell range. Excel will instantly return only the unique rows from the dataset.
Why This Is Useful:
- Dynamic: Automatically updates when new data is added
- No manual refresh needed
Example:
If you add a new row with previously duplicated data but a different value, the formula auto-refreshes to reflect the change.
Method 3: Remove Duplicates Using Power Query
Works in: Excel 2016 and later
Power Query is a powerful tool for transforming data. Here’s how to use it:
- Go to Data tab → Click Get & Transform Data → Select From Sheet
- Power Query Editor opens
- Select all columns (Shift+Click across)
- Right-click and choose Remove Duplicates
- Click Close & Load To… to insert the cleaned data back into Excel
Important: Power Query is case sensitive
Unlike the built-in remove duplicates feature or the UNIQUE formula, Power Query treats “Bridge” and “bridge” as different values.
Fixing Case Sensitivity:
- In Power Query, go to Transform tab → Format → choose Capitalize Each Word or Lowercase/Uppercase
- This standardizes values before removing duplicates
Refreshing Data:
To update data after adding new entries, just right-click the Power Query table and choose Refresh.
Summary: Best Method to Remove Duplicates in Excel
| Method | Best For | Dynamic? | Case Sensitive? |
|---|---|---|---|
| Remove Duplicates Tool | Quick cleanups | ❌ | ❌ |
| UNIQUE Formula | Live reports & dashboards | ✅ | ❌ |
| Power Query | Advanced data prep | Semi ✅ | ✅ |
Final Thoughts
Learning how to remove duplicates in Excel is essential for clean, accurate data analysis. Choose the method that best fits your workflow and Excel version:
- Use Remove Duplicates Tool for quick fixes
- Use the UNIQUE function for dynamic lists
- Use Power Query for robust, customizable solutions
And remember—Power Query is case-sensitive, so always format your data consistently if you’re using it to remove duplicates.
Liked this post? Share it with your data-loving friends, and subscribe to stay updated with more Excel and data tips!
Let me know if you’d like this turned into a downloadable PDF or added with example Excel files!
