How to Remove Duplicates in Excel? (5 Easy Methods)

Sharing is caring!

Removing duplicates in Excel is one of the most important steps in data cleaning. Whether you’re working with a simple list or a large dataset, duplicates can lead to inaccurate analysis, reporting errors, and confusion. Thankfully, Excel offers several easy and effective ways to find and delete duplicate values or rows.

In this guide, we will show you how to use built-in features, conditional formatting, formulas, and Power Query to identify and remove duplicates. Each method has its advantages depending on your goal and the size of your data.

Backup Your Data First

Before you begin any duplicate removal process, it’s highly recommended to make a backup copy of your original data. This is because most removal actions are permanent, and once duplicates are deleted, you may not be able to recover them.

You can back up your data by:

• Copying the entire sheet to a new worksheet
• Saving the workbook with a different name

Method 1: Remove Duplicates Using Excel’s Built-In Feature

This is the most commonly used method and is ideal for quick cleanup.

1. Select Your Data

Highlight the range of cells or select any cell inside a data table where you want to remove duplicates.

2. Open the Remove Duplicates Tool

• Go to the Data tab on the Ribbon.
• Click on Remove Duplicates in the Data Tools group.

3. Choose Columns for Duplicate Checking

• A dialog box will open.
• Select the columns you want to check for duplicate values.
• To check all columns, click Select All.
• To check specific ones, uncheck all and choose only the ones needed.
• If your data has headers, make sure the box My data has headers is checked.

4. Remove Duplicates

• Click OK.
• Excel will process the data and show a summary: how many duplicate values were found and removed, and how many unique values remain.

Note: If you choose multiple columns, Excel will remove a row only if all selected columns have duplicate values. If you select just one column, the duplicate is determined by that column alone, but the entire row will be deleted.

Method 2: Highlight Duplicates with Conditional Formatting

This method helps you visualize duplicates before removing them. It’s best when you want to review duplicates manually.

Steps to Highlight Duplicates

1. Select the data range you want to check.
2. Go to the Home tab.
3. Click Conditional Formatting > Highlight Cells Rules > Duplicate Values.
4. Choose a formatting style (e.g., light red fill).
5. Click OK.

Now, any duplicate values will be visually marked, and you can delete them manually if needed.

Method 3: Use Formulas to Remove Duplicates

If you want more control or need to work with complex rules, using Excel formulas is a flexible approach. This method is also dynamic, which means it updates as your data changes.

1. Combine Columns (If Needed)

If you need to check for duplicates across multiple columns, combine them first.

• In a new column, use:
  =A2&B2&C2
  Or, using the CONCATENATE function:
  =CONCATENATE(A2,B2,C2)

2. Use COUNTIF to Find Duplicates

In the next column, enter:
=COUNTIF(D$2:D2, D2)

This formula checks how many times the combined value has appeared so far. The first occurrence will show 1, and duplicates will show 2, 3, and so on.

3. Filter Unique Rows

• Apply a filter to the column with the COUNTIF formula.
• Filter to show only rows where the count is 1.
• Copy or move these unique values to a new sheet.

Example Table

Only the rows with Count = 1 are unique.

Method 4: Remove Duplicates with Power Query

Power Query is a powerful tool for data transformation. It’s useful when dealing with large datasets or when you need to automate the cleanup process.

1. Load Data to Power Query

• Select your data range.
• Go to Data > Get & Transform Data > From Table/Range.
• If prompted, confirm if your range has headers.

2. Remove Duplicates in Power Query

• In Power Query Editor, select the columns to check.
• Right-click the column headers and choose Remove Duplicates.

3. Load Cleaned Data Back to Excel

• Click Close & Load on the Home tab.
• The cleaned data will appear in a new worksheet or table.

Power Query also allows you to refresh the data automatically, which is ideal for ongoing duplicate checks.

Method 5: Use Keyboard Shortcut to Remove Duplicates

If you want to speed up the process, use this quick shortcut:

• Press Alt + A + M

This will instantly open the Remove Duplicates dialog box, saving you time on navigation.

Comparison of Methods to Remove Duplicates in Excel

Here’s a quick look at each method to help you choose the right one.

Extra Tips for Managing Duplicates

Here are some best practices to keep your Excel data clean and accurate:

• Back up your file before running any removal tool.
• Use Conditional Formatting first if you want to spot check duplicates.
• Use formulas when your data changes often or when you need custom rules.
• For automated workflows, stick with Power Query.
• If your data comes from an external source (like CSV or database), Power Query can be a better choice to keep it clean and updated.

When to Use Which Method

Summary

Removing duplicates in Excel is essential to keep your data accurate and organized. Whether you’re handling customer lists, product catalogs, survey responses, or sales records, cleaning up duplicates improves your reports and reduces confusion.

Excel makes it easy with tools like:

• The Remove Duplicates button
• Conditional Formatting for visual checks
• Formulas for dynamic filtering
• Power Query for automation

By learning these methods and choosing the right one for your task, you can ensure your spreadsheets stay clean, professional, and ready for analysis.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.