How to Remove Duplicates in Excel - [5 Easy Methods]

How to Remove Duplicates in Excel (5 Easy Methods)

Working with duplicate entries in Excel can make your spreadsheets harder to manage and your data less reliable. Whether you are organizing customer lists, employee records, product inventories, or sales reports, removing unnecessary repeated information is an important part of keeping your data clean. Fortunately, Microsoft Excel offers several ways to identify and remove duplicates without manually checking every row. You can use the built-in Remove Duplicates command, the UNIQUE function, Advanced Filter, Power Query, or Conditional Formatting to review repeated entries before deciding what to remove. In this guide, you will learn how to remove duplicates in Excel using five practical methods, understand when each method is appropriate, and choose the right approach while protecting your original information.

What Does Removing Duplicates Mean in Excel?

Removing duplicates in Excel means identifying repeated values or records in a worksheet and eliminating unnecessary occurrences. A duplicate can be a repeated value in a single column or an entire row containing the same information as another record.

For example, consider the following employee list:

Employee Name	Department	Employee ID
Alex	HR	201
Taylor	Sales	202
Alex	HR	201
Alex	Finance	203
Jordan	Marketing	204

In this example, the third record is an exact duplicate of the first record because all three columns contain identical information. However, the fourth record is different because the department and employee ID do not match.

This distinction matters because removing duplicate values from one column is different from removing completely identical rows. Excel allows you to select which columns should be considered when comparing records.

Important: Before permanently removing duplicates, make a backup of your original worksheet. Excel's Remove Duplicates command deletes matching records from the selected range. Although you can immediately undo the operation, keeping a separate copy is a safer practice when working with important information.

How to Remove Duplicates in Excel Using 5 Easy Methods

There is no single method that works best for every spreadsheet. The right approach depends on whether you want to permanently delete repeated records, create a separate list of unique values, clean data regularly, or simply inspect duplicates before making changes.

Let's explore five practical methods and the situations where each one can help.

Method 1: Use the Remove Duplicates Command

The built-in Remove Duplicates command is the quickest way to remove repeated values or duplicate rows in Excel. It is particularly useful when cleaning customer databases, employee records, inventory lists, and other structured datasets.

You can compare one column, multiple selected columns, or all columns to determine which records Excel should consider duplicates.

Step 1: Select Your Excel Data

Open your workbook and navigate to the worksheet containing duplicate entries.

1. Select the complete data range you want to clean.
2. Include all relevant columns so that the corresponding information remains together.
3. Include the column headings if your dataset has headers.

For example, if your customer information is stored in columns A through D, select the entire range rather than selecting only the column containing repeated email addresses.

Step 2: Open the Remove Duplicates Command

After selecting your data:

1. Go to the Data tab in the Excel ribbon.
2. Find the Data Tools group.
3. Click Remove Duplicates.

Excel will open the Remove Duplicates dialog box, displaying the column names available for comparison.

Step 3: Choose the Columns to Compare

This is the most important part of the process because your selection determines which records Excel identifies as duplicates.

• To remove completely identical rows: Select all relevant columns.
• To remove duplicates based on one column: Select only the column containing the identifying values.
• To compare multiple fields: Select the columns that must match for a record to be considered a duplicate.

Also, check the My data has headers option if your first row contains column names. Leave it unchecked if your selected range does not contain headings.

Example: Suppose your worksheet contains Customer Name, Email Address, Phone Number, and Registration Date. If you want to identify repeated customers based on email addresses, select the entire dataset but check only Email Address in the Remove Duplicates dialog box.

Important: Excel uses the selected columns as comparison criteria, but removes the corresponding duplicate rows across the entire selected range. For example, selecting only Email Address does not mean Excel will delete values from that column alone. The other information in each removed row will also be deleted.

Step 4: Remove the Duplicate Records

Once you have selected the appropriate comparison columns:

1. Review your selected columns carefully.
2. Click OK.
3. Excel will identify and remove the additional matching records.
4. A confirmation message will display the number of duplicate values removed and the number of unique values remaining.
5. Click OK to close the message.

Excel retains the first occurrence of a matching record and removes subsequent occurrences according to your selected comparison criteria.

Step 5: Review and Save Your Worksheet

After removing the duplicates, inspect the remaining records to confirm that the correct information has been retained.

Once everything looks correct, press Ctrl + S to save your workbook.

This is the simplest method for permanently removing duplicate records from an Excel worksheet.

Method 2: Use the UNIQUE Function to Keep Your Original Data

If you want to remove duplicates without modifying your original worksheet, Excel's UNIQUE function is an excellent alternative.

Unlike the Remove Duplicates command, this function generates a separate list containing unique values or records. Your original dataset remains unchanged, and the resulting list can update automatically when the source data changes.

Remove Duplicate Values from One Column

Suppose your customer names are stored in cells A2 through A100, and you want to create a separate list without repeated names.

Click an empty cell where you want the results to appear and enter this formula:

=UNIQUE(A2:A100)

Press Enter. Excel will return a list containing unique values from the selected range.

Remove Duplicate Rows from Multiple Columns

You can also use UNIQUE to identify distinct records containing multiple fields.

For example, suppose your worksheet contains customer information in columns A through C. Enter the following formula:

=UNIQUE(A2:C100)

This formula compares complete rows across the selected three-column range and returns unique combinations. If two rows contain identical values across all three columns, only one combination appears in the resulting list.

Sort the Unique Results Automatically

If you want to arrange the resulting values in ascending order, combine UNIQUE with the SORT function:

=SORT(UNIQUE(A2:A100))

This formula returns unique values and sorts them alphabetically or numerically, depending on the type of information.

Important Things to Remember When Using UNIQUE

• The original source data remains unchanged.
• The results are displayed as a dynamic array and automatically update when the referenced source values change.
• The destination cells must have enough empty space for the results to spill.
• The function is available in Excel for Microsoft 365, Excel 2021, and Excel 2024, including supported web and mobile versions.
• Excel 2019 and Excel 2016 do not support the UNIQUE function.

If your source data is formatted as an Excel Table, you can use a structured reference such as:

=UNIQUE(Table1[Customer Name])

This makes the formula convenient for datasets that grow as new records are added to the table.

For more technical information, visit Microsoft's official UNIQUE function documentation.

Method 3: Use Advanced Filter to Copy Unique Records

Advanced Filter is another useful Excel feature when you want to extract unique records without permanently changing the original data.

This method is especially helpful when working with older Excel versions that do not support the UNIQUE function. You can copy distinct values or rows into another location while preserving your original worksheet.

Step 1: Select Your Dataset

Open your Excel worksheet and select the complete dataset, including its column headings.

Make sure the selected range includes all the columns you want to copy into your new unique list.

Step 2: Open Advanced Filter

Follow these steps:

1. Navigate to the Data tab.
2. Find the Sort & Filter group.
3. Click Advanced.

Excel will open the Advanced Filter dialog box.

Step 3: Configure the Advanced Filter Options

To extract unique records into a separate location:

1. Select Copy to another location.
2. Confirm the List range containing your original data.
3. Enter or select an appropriate destination cell in the Copy to box.
4. Check the Unique records only option.
5. Review the selected settings.

Step 4: Copy the Unique Records

Click OK to complete the operation.

Excel will copy the unique records to your selected destination. Your original dataset will remain unchanged.

This method is particularly useful when you need to prepare a clean customer list, generate a separate product list, or extract unique employee records without changing the source worksheet.

Tip: Advanced Filter is useful for occasional data-cleaning tasks. However, it does not create an automatically updating dynamic list like the UNIQUE function. Run the filter again when you need to extract records from updated source data.

You can find additional instructions in Microsoft's guide to filtering for unique values and removing duplicates.

Method 4: Use Power Query for Large or Repeating Cleanups

Power Query is a powerful Excel feature for importing, transforming, and cleaning structured data. It is especially useful when you regularly receive reports containing repeated records or need to perform the same data-cleaning process multiple times.

Instead of manually removing duplicates every time, you can create a query that includes the required cleaning steps and refresh its results when the source data changes.

Step 1: Convert Your Data into an Excel Table

First, make sure your dataset has properly organized column headings.

1. Select any cell inside your dataset.
2. Press Ctrl + T.
3. Confirm that the selected range is correct.
4. Check My table has headers when appropriate.
5. Click OK.

Converting the range into a table makes it easier to manage structured data and include additional records in a repeatable workflow.

Step 2: Open the Power Query Editor

After creating the table:

1. Select a cell inside the table.
2. Go to the Data tab.
3. Choose From Table/Range.
4. Excel will open the Power Query Editor.

Step 3: Select the Columns to Compare

In the Power Query Editor, identify the columns containing the values you want to use for duplicate detection.

• Select one column to remove records sharing the same value in that field.
• Hold Ctrl while selecting multiple columns to compare a combination of fields.
• Select all relevant columns when you want to identify completely identical rows.

Step 4: Remove Duplicate Records

After selecting the appropriate columns:

1. Navigate to the Home tab.
2. Locate the Reduce Rows group or its equivalent in your Excel version.
3. Choose Remove Rows.
4. Select Remove Duplicates.

Power Query will remove rows containing repeated values according to the selected columns.

Step 5: Load and Refresh the Cleaned Results

After reviewing the transformed data:

1. Click Close & Load.
2. Excel will load the cleaned results into a worksheet.
3. When you add new records to the source table, use Data > Refresh All to update the query results.

This makes Power Query particularly useful for recurring reports, large customer databases, inventory records, and repeated data-cleaning workflows.

Important Power Query considerations: Power Query compares text values in a case-sensitive manner. For example, ABC and abc may be treated as different values. You can normalize text using an uppercase or lowercase transformation before removing duplicates when case should not matter. Also, Power Query does not guarantee which matching record will be retained. Do not rely on it to preserve the first occurrence when specific record-selection rules are important.

For additional technical details, read Microsoft's Power Query documentation on working with duplicate values.

Method 5: Highlight Duplicates First, Then Decide

Sometimes, immediately deleting repeated entries is not the best decision. Two records may appear identical but contain important differences in other columns. In such situations, it is safer to identify and review duplicates before removing anything.

Excel's Conditional Formatting feature makes this process easier by highlighting repeated values in your selected range. You can inspect the results, identify unnecessary records, and decide which entries should remain.

Step 1: Select the Data You Want to Check

Open your worksheet and select the column or range containing the values you want to inspect.

For example, if you want to identify repeated customer email addresses, select the corresponding email address column.

Step 2: Open Conditional Formatting

Follow these instructions:

1. Go to the Home tab.
2. Click Conditional Formatting.
3. Select Highlight Cells Rules.
4. Choose Duplicate Values.

Step 3: Choose a Highlighting Format

Excel will display a Duplicate Values dialog box.

Choose your preferred formatting option, such as a light red fill with dark red text, and click OK.

Excel will automatically highlight repeated values in the selected range, making them easier to identify.

Step 4: Review the Highlighted Entries

Before removing anything, examine the highlighted records carefully.

Consider the following questions:

• Are the highlighted entries genuinely unnecessary duplicates?
• Do the corresponding records contain different information in other columns?
• Should the first record or the most recent record be retained?
• Are the differences caused by extra spaces or inconsistent formatting?
• Would it be safer to keep all records until they have been verified?

Once you understand the records, you can decide whether to remove them, correct inconsistencies, or keep them as separate entries.

Find Completely Duplicate Rows Using a Formula

The standard Duplicate Values rule identifies repeated values within the selected cells. However, it does not automatically identify rows that are completely identical across multiple columns.

To identify repeated combinations across multiple fields, you can use a COUNTIFS formula in a helper column or a formula-based Conditional Formatting rule.

For example, suppose your data contains Customer Name, Email Address, and Phone Number in columns A, B, and C, with records starting in row 2.

Enter this formula in an empty helper column:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1

Copy the formula down the remaining rows.

Excel will return TRUE when the same combination of customer name, email address, and phone number appears more than once.

This allows you to distinguish completely repeated records from entries that share only one value.

Best practice: Highlighting duplicates does not delete any information. It gives you an opportunity to examine your records before choosing an appropriate cleanup method.

Which Method Is Best for Removing Duplicates in Excel?

Now that you understand all five methods, choosing the right one should be easier. Each approach is useful in a different situation.

Method	Best For	Changes Original Data?
Remove Duplicates	Quickly deleting repeated records	Yes
UNIQUE Function	Generating dynamic unique lists	No
Advanced Filter	Copying unique records elsewhere	No, when copying to another location
Power Query	Large or recurring cleanup workflows	Transforms query results, not the original source directly
Conditional Formatting	Reviewing duplicates before deletion	No

For ordinary spreadsheet cleaning, the Remove Duplicates command is usually the easiest choice. If you want to preserve your original information, the UNIQUE function or Advanced Filter is more appropriate. For recurring data-cleaning tasks, Power Query provides a repeatable workflow.

When you are unsure whether repeated entries should actually be deleted, start with Conditional Formatting and inspect the records before making changes.

Common Problems When Removing Duplicates in Excel

Even when you follow the correct procedure, Excel may sometimes retain entries that appear identical or remove records that you intended to keep. Understanding the possible causes can help you avoid these problems.

Excel Does Not Recognize Some Duplicate Values

One common reason is the presence of extra spaces or hidden characters. For example, Customer and Customer followed by an extra space may look identical but contain different text.

To remove ordinary extra spaces, use the TRIM function in a helper column:

=TRIM(A2)

If your imported data contains nonbreaking spaces, try this formula:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

Copy the cleaned results and paste them as values before removing duplicates. Keep in mind that TRIM and SUBSTITUTE do not eliminate every possible invisible Unicode character.

Excel Removes Records That Are Not Completely Identical

This usually happens when the wrong comparison columns are selected.

For example, two customers may share the same name but have different email addresses and phone numbers. If you compare only the Customer Name column, Excel may treat one record as a duplicate of the other.

To prevent this, select the appropriate combination of identifying fields. Use multiple columns whenever a single value cannot reliably distinguish different records.

The UNIQUE Function Returns an Error

If the UNIQUE function produces a #SPILL! error, check whether the destination area contains existing values, merged cells, or other obstacles preventing the results from expanding.

Clear the cells where the formula needs to return its results and try again.

Also, verify that your Excel version supports the UNIQUE function.

Power Query Keeps an Unexpected Duplicate Record

Power Query does not guarantee which matching record will be retained after duplicates are removed. This can cause unexpected results when multiple records share the same identifying value but contain different information in other columns.

When a particular record must be preserved, establish an explicit selection rule for the records before removing duplicates. For example, decide whether the most recent transaction or a record with a preferred status should be retained. Do not rely on the original row order alone.

You Accidentally Removed Important Records

If you have just removed duplicates and notice that some important information is missing, immediately use Ctrl + Z to undo the operation, provided the undo history is still available.

Review your comparison-column settings and repeat the process with the correct selections.

Maintaining a backup of the original worksheet is always a safer practice when working with important business or personal records.

How to Remove Duplicates in Excel – Frequently Asked Questions

Different spreadsheet situations can call for different duplicate-removal techniques. These frequently asked questions cover some practical concerns about removing repeated values, preserving original records, using Excel formulas, and identifying duplicates accurately.

How do I remove duplicates in Excel but keep one occurrence?

Select your complete dataset, go to Data > Remove Duplicates, and choose the columns Excel should compare. Click OK. Excel retains the first occurrence of each matching value or record and removes the subsequent occurrences within the selected range.

How can I remove duplicates in one column without losing other columns?

Select the entire dataset, open Data > Remove Duplicates, and select only the column you want to use for comparison. Excel will identify duplicates based on that column and remove the corresponding rows across the selected range. The other columns in retained rows remain together. Make a backup before proceeding because the entire duplicate row is removed, not just the repeated cell value.

Which Excel function is used to remove duplicate values?

The UNIQUE function returns distinct values or rows from a specified range. For example, =UNIQUE(A2:A100) generates a list of unique values from one column. Unlike the Remove Duplicates command, it does not permanently modify the original dataset.

Can I remove duplicates in Excel without changing the original data?

Yes. Use the UNIQUE function to generate a separate dynamic list of distinct values or rows. Alternatively, open Data > Advanced, select Copy to another location, choose your destination cell, and enable Unique records only. Both approaches let you preserve the original dataset.

How do I highlight duplicate values in Excel before removing them?

Select the cells you want to check, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, and choose a highlighting format. Excel will mark repeated values so you can inspect them before deciding which entries should be removed.

Does Excel automatically keep the first occurrence when removing duplicates?

Yes. Excel's standard Remove Duplicates command keeps the first occurrence of a matching value or record and removes subsequent occurrences according to the selected comparison columns. If you need to preserve a particular record, arrange and review your data before performing the operation.

Why does Excel still show duplicate values after removing duplicates?

Excel may retain entries that look identical when they contain extra spaces, hidden characters, different underlying values, or inconsistent formatting. Your selected comparison columns may also be incorrect. Check the source data, clean ordinary extra spaces using TRIM where appropriate, and review the Remove Duplicates settings before repeating the process.

Which method is best for removing duplicates from large Excel datasets?

Power Query is a useful option for large datasets and recurring cleanup tasks because you can save the transformation steps and refresh the results when the source data changes. For a simple one-time cleanup, the built-in Remove Duplicates command is usually more straightforward.

Still facing a particular duplicate-removal issue in your Excel worksheet? Share the problem, formula, or example in the comments section below so we can better understand your situation. You can also reach out through the Contact Us page for additional assistance with Excel and spreadsheet-related questions.

Final Thoughts on How to Remove Duplicates in Excel

Removing unnecessary duplicate records is an essential part of maintaining clean and reliable Excel spreadsheets. Whether you are managing customer information, employee records, sales reports, or product inventories, choosing the appropriate cleanup method can save time and prevent mistakes.

For quick cleanup, the built-in Remove Duplicates command is usually the easiest solution. When you need to preserve your original information, the UNIQUE function and Advanced Filter offer useful alternatives. For large or recurring data-cleaning tasks, Power Query provides a more repeatable workflow. And when you are not completely sure which records should be deleted, Conditional Formatting allows you to highlight and review duplicates before making a decision.

Most importantly, always check your comparison columns, understand how Excel identifies duplicate records, and keep a backup before permanently deleting information.

With these five methods, you can confidently remove duplicates in Excel, organize your worksheets more efficiently, and maintain more accurate data for your everyday spreadsheet work.