How to Remove Duplicates in Excel: A Step-by-Step Guide

When you're working with large data sets in Excel, it's not uncommon to run into duplicate values that can skew your results or inflate your reports. Duplicate data can lead to miscalculations, redundancy, and inefficiency, especially when you're preparing reports, running analyses, or generating dashboards. 

Thankfully, Microsoft Excel includes a built-in feature that makes removing duplicate entries quick and painless. Whether you're cleaning up a contact list, analyzing sales records, or managing call logs, learning how to remove duplicates in Excel is an essential skill for keeping your data clean and accurate. 

Understanding how to identify, remove, and even extract duplicates can save hours of manual work and improve data reliability. Excel provides a simple yet powerful tool for de-duplicating entries based on one or multiple columns. 

Once mastered, this feature can be used in everyday scenarios ranging from removing duplicate email addresses in a list to isolating unique combinations of customer and order information. 

In this comprehensive guide, we’ll walk through everything you need to know about using the Remove Duplicates feature in Excel. You'll learn about where to find this tool, how to use it step by step, common use cases, best practices, and alternative ways to handle duplicates using formulas or Power Query. With practical examples and clear instructions, you’ll gain the confidence to handle duplicates in any Excel workbook you encounter. 

What Is the Remove Duplicates Feature in Excel?

The Remove Duplicates feature in Excel allows you to automatically scan a range of cells or a full table and eliminate rows that contain repeating values in one or more selected columns. It’s a built-in utility designed to streamline data cleaning by quickly identifying and removing redundant entries based on your chosen criteria. 

This tool is highly efficient for eliminating repeated records in tasks such as: 

• Cleaning mailing lists to avoid sending duplicate emails 
• Preparing sales data for accurate analysis 
• Generating summary reports where each row should represent a unique item 
• De-duplicating survey responses, transaction logs, or product catalogs 

It works seamlessly on both formatted Excel tables and standard ranges of data, making it versatile and accessible regardless of how your spreadsheet is set up. It helps reduce the risk of reporting errors and improves overall data hygiene. 

Where to Find the Remove Duplicates Option

You don’t need to convert your data into a formal Excel table to use the Remove Duplicates feature. However, understanding where to find it helps streamline your workflow. Excel provides two primary access points to this feature, depending on how your data is formatted. 

Option 1: From the Data Tab 

1. Click anywhere inside your dataset. 
2. Go to the Data tab on the Excel ribbon. 
3. Click Remove Duplicates in the Data Tools group. 

This method works on any selected range, not just tables, and is ideal for quick, ad hoc data cleanups. 

Option 2: From the Table Design Tab (If Using a Table) 

1. Click inside your Excel table. 
2. Go to the Table Design tab. 
3. Click Remove Duplicates in the Tools group. 

This method is great when you’re working with structured data tables and want to ensure the formatting and table integrity are preserved during the cleanup process. 

Want more lessons like this one? Check out our Excel Intermediate training course to access this and other essential Excel skills.  

How to Use Remove Duplicates in Excel (Step-by-Step)

Let’s go through a practical example. Suppose you have a dataset with multiple columns like Date, Agent, and Line, and you suspect that some entries are duplicates. Here's how to use the Remove Duplicates feature effectively: 

Step 1: Select the Data Range

Click anywhere within the data set or table where you want to check for duplicates. You can also highlight a specific range if you only want to apply the feature to a subset of your data. Make sure your data has column headers to simplify selection in the next step. 

Step 2: Launch Remove Duplicates

From either the Data or Table Design tab, click Remove Duplicates. Excel will open a dialog box with checkboxes for each column in your dataset. 

Step 3: Choose Columns for Comparison

A popup will appear showing all column headers in your data. Excel will ask you which columns to compare when identifying duplicate rows. 

• Check all columns if you want to eliminate rows that are exact matches across every field. 
• Select specific columns if you want to remove rows based on a subset of the data. For example, you might only compare duplicates based on "Date" and "Agent" fields. 

You can also sort your data first to ensure you keep the most relevant or most recent record when duplicates are removed. This is particularly helpful in datasets with time-based information. 

Step 4: Confirm and Review Results

Click OK after selecting your columns. Excel will remove any duplicate rows based on your criteria and give you a summary of how many duplicates were removed and how many unique values remain. It's a good idea to review this summary to ensure the action matched your expectations. 

If something seems off, press Ctrl + Z immediately to undo the action. Excel retains your undo history until you close the workbook, so you can always backtrack if needed. 

Real-World Example: Removing Duplicate Agent Entries

Let’s say you have a call log table where agents can appear multiple times a day. However, you only want one entry per agent per date to create a unique activity report. Here’s how to handle that: 

1. Click inside your table. 
2. Go to the Table Design tab. 
3. Click Remove Duplicates. 
4. In the popup, uncheck all columns. 
5. Check only Date and Agent. 
6. Click OK. 

Excel will now find rows where the combination of Date and Agent appears more than once and remove the duplicates. This is especially useful for simplifying your data before feeding it into pivot tables or reports that rely on distinct records. 

Note: Excel won’t highlight or preview the duplicates it removes, so it's essential to review your data beforehand. For added safety, you can first duplicate the sheet or use Conditional Formatting to highlight duplicates before deletion. And of course, Ctrl + Z is your best friend if anything unexpected happens. 

Ready to go beyond basic tasks and dive into advanced Excel? capabilities. Our advanced Excel course helps experienced users take full control of Excel by mastering complex formulas, automating processes, and analyzing large datasets with confidence. 

Tips for Managing Duplicates Effectively

Here are a few best practices and additional tips to make the most of the Remove Duplicates tool: 

• Back up your data before making changes. Always work on a copy of your original data or duplicate the sheet. 
• Use Conditional Formatting to visually inspect duplicates before removing them. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. 
• Filter your data to isolate suspected duplicates manually, especially when dealing with complex datasets. 
• Understand your data logic—in some cases, duplicates may be intentional, like multiple product purchases by the same customer. 

Alternatives to the Remove Duplicates Tool

If you need more flexibility or want to identify duplicates without removing them immediately, consider using these advanced Excel features: 

1. Formulas

Use Excel formulas like =COUNTIF() or =UNIQUE() to detect or extract duplicate values.

Example: 

=UNIQUE(A2:A100) 

This formula returns a list of unique values from column A. 

2. Power Query

Power Query offers a more advanced and customizable way to clean your data. With Power Query, you can remove duplicates, transform data types, merge datasets, and automate repetitive tasks—all without modifying your original data. 

3. Advanced Filters

Use the Advanced Filter feature in the Data tab to extract unique records to another location in the workbook. This is useful for creating a deduplicated list without altering your source data. 

Take Your Excel Skills to the Next Level

Learning how to remove duplicates is just the beginning. Whether you're a beginner or looking to sharpen your skills for professional growth, Intellezy offers a full suite of Excel training courses designed to help you work smarter and more efficiently. 

From data cleaning and formula building to pivot tables, charts, and advanced analytics, our expert-led video lessons make it easy to master Excel at your own pace. 

• Courses for all skill levels 
• Learn at your own pace, anytime 
• Taught by certified Excel experts 
• Available on-demand with closed captions and assessments 

Ready to become an Excel pro?

Start your free trial today and explore our full library of professional training courses, featuring dozens of online Excel courses and hundreds of other software & business titles. Whether you're managing spreadsheets for personal productivity or driving business decisions, our Excel training will help you unlock the full potential of your data.