How to Remove Duplicates in Excel (3 Easy Ways)

Quickly Find and Delete Duplicates in Excel Worksheets

by Avantix Learning Team | Updated September 15, 2023

Applies to: Microsoft^® Excel^® 2013, 2016, 2019, 2021 and 365 (Windows)

You can remove duplicates in Excel in several ways. When you use the Remove Duplicates tool, Excel will keep the first instance and the remaining duplicates in the data set will be deleted. It's common to remove duplicate rows in a list or data set so that the data can be sorted, filtered and summarized. You'll need to decide what you consider to be a duplicate (based on one or more fields or columns).

Recommended article: How to Highlight Errors, Blanks and Duplicates in Microsoft Excel

Do you want to learn more about Excel? Check out our virtual classroom or in-person Excel courses >

In this article, we'll review 3 easy ways to remove or delete duplicates in Excel:

1. Use Remove Duplicates on the Data tab in the Ribbon
2. Use Remove Duplicates on the Table Design or Table Tools Design tab in the Ribbon
3. Create a formula to remove duplicates if there are extra spaces in the data

The process is to identify the duplicates and then delete the duplicate rows.

For all of the techniques below, the list or data set should have:

• Unique header names in the header row
• No blank rows
• No blank columns
• No merged cells

If you have used the Subtotal feature to add subtotals, you should remove them. If you are planning on using structured reference formulas in Excel tables, it's easier if the field names do not include spaces.

Note: Screenshots in this article are from Excel 365 but are similar in previous versions of Excel.

In the sample data set below, the data includes unique headers and duplicate records but no blank rows or columns:

Before removing duplicates, you may want to save a copy of the worksheet or workbook so you can keep the original data.

1. Remove duplicates using Remove Duplicates on the Data tab in the Ribbon

To remove or delete duplicates from a data set using Remove Duplicates on the Data tab in the Ribbon:

1. Select a cell in the data set or list containing the duplicates you want to remove. If the data set has blank rows or columns within it, you'll need to select the data first (click in the first cell and Shift-click in the last cell).
2. Click the Data tab in the Ribbon.
3. Select Remove Duplicates in the Data Tools group. A dialog box appears.
4. Assuming your data set or list has headers, ensure My data has headers is checked.
5. In the columns area, select or check the field(s) containing the duplicates you want to remove. You can select one or more fields (columns) or All. A dialog box appears indicating how many records will be removed.
6. Click OK.

Excel will remove duplicates, keep the first record of the duplicate records and provide a summary of the number of rows that have been removed.

To use a keyboard shortcut to access the Remove Duplicates command on the Data tab on the Ribbon, press Alt > A > M (press Alt, then A, then M).

Remove Duplicates appears in the Data Tools group on the Data tab in the Ribbon:

In the following example, the Remove Duplicates dialog box appears with 3 fields from the data set:

2. Remove duplicates in an Excel table

If your data is formatted as an Excel table (typically by pressing Ctrl + T), you can remove the duplicates using the Table Design or Table Tools Design tab in the Ribbon.

To remove duplicates in an Excel table:

1. Click in the table that contains the duplicates you want to remove.
2. Click the Table Design or Table Tools Design tab in the Ribbon.
3. Select Remove Duplicates in the Tools group. A dialog box appears.
4. Assuming your table has headers, ensure My data has headers is checked.
5. In the columns area, select or check the field(s) containing the duplicates you want to remove. You can select one or more fields (columns) or All. A dialog box appears indicating how many records will be removed.
6. Click OK.

Excel will remove duplicates and provide a summary of the number of rows that have been removed.

Remove Duplicates appears in the Tools group in the Table Design or Table Tools Design tab in the Ribbon:

You can also use the Remove Duplicates command on the Data tab for a table.

3. Remove duplicates using a formula

You can enter a formula to help you identify duplicates that may have extra spaces and are not recognized as a duplicate.

The following strategy includes:

• The TRIM function to remove spaces between words as well as leading and trailing spaces
• The CONCATENATE operator (&) to combine cells (although you can use the CONCATENATE or CONCAT functions as well)

You will need to create a new calculated column (or helper column) in your data and then you can use the Remove Duplicates command.

For example, if you wanted to combine the data from A2 and B2 and remove extra spaces in a cell (such as C2), you could create the following formula:

=TRIM(A2) & TRIM(B2)

In the following example (which includes first names and last names), we've entered a formula =TRIM(A2) & TRIM(B2) in C2 and then copied the formula down to the cells below by dragging the Fill handle on the bottom right corner of the cell:

If you want to combine cells from columns in a table (which includes columns for first name and last name) and remove extra spaces, you could create the following formula in the first data cell in a new calculated column as a structured reference formula where you refer to field names:

=TRIM([@[First Name]]) & TRIM([@[Last Name]])

Excel will populate the formula for the entire column when you press Enter.

In the table example below, the structured reference formula using TRIM and the CONCATENATE operator is entered in C2 and Excel color codes the references:

If there are no spaces in the field names, you can enter the formula in C2 in the table as follows:

=TRIM([@FirstName]) & TRIM([@LastName])

Once you have created the helper or calculated column, to remove duplicates, click in the data set or table and use the Remove Duplicates command in the Ribbon (using the methods above) and check only the calculated column in the dialog box.

For other ways to combine cells, check out How to Combine Cells in Excel Using CONCATENATE (3 Ways).

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Merge Cells in Excel (4 Ways with Shortcuts)

How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)

How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query to Get and Transform Data

Microsoft Excel: New and Essential Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix^® Learning

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca