How to Remove Duplicates in Excel

Quick Start: How to Remove Duplicates in Excel Fast

You can use a built-in tool to remove duplicate values in Excel. It takes less than a minute and works for most datasets. Here's how to remove duplicates but keep the first instance in Excel.

Step 1: Select Your Data

Open your Excel file and click any cell inside your data. This becomes the active cell.

Step 2: Go to the Data Tab

At the top of Excel, click the Data tab. In the Data Tools group, click Remove Duplicates.

Step 3: Choose Columns for Comparison

A dialog box will appear.

• Make sure the correct columns are checked.

• If your data has headers, confirm the “My data has headers” box is selected.

Excel will compare the selected values across the chosen columns to find duplicates.

Step 4: Confirm and Remove

Click OK to confirm.

Excel will instantly remove duplicate rows, keeping only the first occurrence of each set of duplicate values.

How to Highlight Duplicates in Excel Using Conditional Formatting

To find duplicate values before deleting, the safest approach is to highlight them with conditional formatting. This lets you review the data first.

Step 1: Select the Range

Select the range of cells you want to check. Make sure all relevant rows or columns are included.

Step 2: Go to the Home Tab

Click the Home tab at the top of Excel. In the Styles group, select Conditional Formatting.

Step 3: Use Highlight Cells Rules

From the dropdown menu, click Highlight Cells Rules > Duplicate Values. A small dialog box will open.

Step 4: Choose the Format

In the box, you can:

• Choose whether to highlight duplicate values or unique values

• Select a color or custom format

Click OK to apply the rule.

Advanced Methods – UNIQUE Function and Power Query

If you work with large datasets or need dynamic results, advanced tools in Excel can help you remove duplicates more efficiently. The UNIQUE function and Power Query are particularly useful when your data changes often.

The UNIQUE Function (Excel 365 and Newer)

The UNIQUE function automatically returns unique values from a selected range. Unlike the Remove Duplicates button, it does not delete anything. It creates a clean list elsewhere.

Step 1: Select a New Cell

Click an empty cell where you want the results to appear.

Step 2: Enter the Formula

Type a formula like: =UNIQUE(A1:C7)

Press Enter. Excel will instantly generate a new list without duplicates. If your original data changes, the results update automatically.

Remove Duplicates Using Power Query

Power Query is designed to handle larger, more complex datasets. It gives you more control over the cleaning process.

Step 1: Load Data into Power Query

Select your data range or table. Go to the Data tab and then click “From Table/Range.” Your data will open inside the Power Query editor. Indicate if your table has headers.

Step 2: Remove Duplicates

Select the relevant column or columns. Right-click and choose Remove Duplicates. Power Query will instantly clean the selected data.

Step 3: Close and Load

Click Close & Load to return the cleaned data to your worksheet.

How to Remove Duplicates in Excel Based on One Column

You may only want to remove duplicate rows based on one specific column, such as email addresses or customer names. Excel makes this easy by letting you choose which column to use for the comparison.

When you remove duplicates using a single column, Excel checks only that column and keeps the first match.

Step 1: Open the Remove Duplicates Tool

Select any cell in your data table. Go to the Data tab in Excel. In the Data Tools group, click Remove Duplicates. A dialog box will appear showing all the columns in your dataset.

Step 2: Choose the Column for Comparison

Excel will display a checklist of columns. Uncheck all columns. Check only the column you want to use to find duplicate values. This tells Excel to compare only that column while leaving the rest unchanged.

Step 4: Confirm the Removal

Click OK to confirm. Excel will analyze the selected column and remove duplicate rows where the same value appears more than once.

Removing Duplicates Based on Multiple Column Values

Use the Data tool to remove duplicates in an Excel table based on multiple columns. Let's assume your table contains employees with the same names but different IDs.

1. Open the Remove Duplicates dialog, and ensure both columns are checked. 
2. Click OK. This prevents Excel from deleting two different people who happen to share the same name.

How to Remove Duplicates in Excel but Keep One

A common question is "how to remove duplicates in excel but keep one." Excel's built-in Remove Duplicates tool does this automatically. It identifies the first instance (the top-most row) as the "original" and removes all subsequent matching rows. To control which one is kept, sort your data first so your preferred record is at the top.

How to Remove Duplicates in Excel 2016 and Older

If you are wondering "how do you remove duplicates in excel 2016," the process is nearly identical to newer versions. While you won't have access to the UNIQUE function, the Remove Duplicates button on the Data tab and Advanced Filters are fully functional. To use Advanced Filter: Go to Data > Advanced > Select "Unique records only" and "Copy to another location."

Comparison: Which Method Should You Use?

Troubleshooting Duplicates: Common Errors & Fixes

Have you followed every step correctly, but the results don't look right? You might still see duplicate rows, or Excel might report that "no duplicate values found" when you can clearly see them on your screen. This usually happens because of hidden data issues that interfere with Excel's comparison logic.

1. Hidden Spaces and "Ghost" Characters

The most common error is trailing or leading spaces. To Excel, a cell containing "Data " (with a space) is not a true match for a cell containing "Data".

• The fix: Use the TRIM function in a new column to clean your list. For example, =TRIM(A2) will remove those invisible spaces.

2. Numbers Stored as Text

If one number is formatted as a "number" and another as "text," Excel will not treat them as duplicates. This often happens when you open data exported from another system (such as a CRM or accounting software).

• The fix: Select your columns, go to the Data tab, and use "Text to Columns" to format the entire range consistently as numbers.

Read more: How to convert text to number Excel.

3. Case Sensitivity in Formulas

While the standard tool in the Data tools group is not case-sensitive (it treats "Apple" and "apple" as the same), certain advanced formula methods or a specific Power Query setting might treat them as unique values.

• The fix: If you need to ensure case-insensitive results in a formula, use the UPPER or LOWER function to standardize your text before performing a comparison.

4. Dealing with Merged Cells and Filtered Rows

Excel cannot remove duplicates if the selected range contains merged cells or if the table has active filters or subtotals.

• The fix: Unmerge all cells in your range and ensure you have cleared all filters from the Data tab before starting the process.

Final Thoughts

Managing duplicates in Excel is simple once you know the right method. You can use built-in tools under the Data tab to quickly remove duplicates, apply conditional formatting to highlight duplicate values first, or rely on the UNIQUE formula and Power Query for more advanced control.

The key is to determine what counts as a duplicate in your data before you remove anything. Always select the correct range, confirm your settings, and keep a backup copy.

FAQs

1. How do I remove duplicates in Excel quickly?

Select your data range, go to the Data tab, click Remove Duplicates, choose the columns for comparison, and confirm. Excel will remove duplicate rows instantly.

2. How can I highlight duplicate values without deleting them?

Use Conditional Formatting from the Home tab, choose Highlight Cells Rules, then select Duplicate Values. Excel will highlight duplicates in the selected range.

3. How to remove duplicates in Excel but keep one?

Excel's "Remove Duplicates" tool always keeps the first instance and deletes all subsequent copies. To keep a specific one, sort your data so the desired record is at the top of the list before running the tool.

4. How do I remove duplicates in an Excel column?

Select only that specific column, then go to Data > Remove Duplicates. If asked if you want to "Expand the selection," choose "Continue with current selection" to only affect that single column.

5. How do I find duplicates using a formula?

Use a formula like =COUNTIF(A:A,A2)>1 to find duplicates in a column. It returns TRUE for duplicate values.

6. Can I undo removed duplicates?

You can undo immediately using Ctrl + Z. If the file is saved and closed, you’ll need a backup copy to restore the data.

Related Articles

How to Create Interactive Dashboards in Excel

How to Search Within Excel

How to Swap Columns in Excel