![delete duplicate rows in excel delete duplicate rows in excel](https://www.exceldemy.com/wp-content/uploads/2017/02/Remove-duplicate-rows-from-table-05.png)
The CONCATENATE operator (&) to combine cells (although you can use the CONCATENATE or CONCAT functions as well).The TRIM function to remove spaces between words as well as leading and trailing spaces.
![delete duplicate rows in excel delete duplicate rows in excel](https://www.myexcelonline.com/wp-content/uploads/2017/09/Remove-Duplicates-03.png)
You can enter a formula to help you identify duplicates that may have extra spaces and are not recognized as a duplicate. You can also use the Remove Duplicates command on the Data tab for a table. Remove Duplicates appears in the Tools group in the Table Design or Table Tools Design tab in the Ribbon: Assuming your table has headers, ensure My data has headers is checked.Įxcel will remove duplicates and provide a summary of the number of rows that have been removed.Select Remove Duplicates in the Tools group.Click the Table Design or Table Tools Design tab in the Ribbon.Click in the table that contains the duplicates you want to remove.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. In the following example, the Remove Duplicates dialog box appears with 3 fields from the data set: Remove Duplicates appears in the Data Tools group on the Data tab in the Ribbon: 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). A dialog box appears indicating how many records will be removed.Įxcel will remove duplicates, keep the first record of the duplicate records and provide a summary of the number of rows that have been removed. You can select one or more fields (columns) or All. In the columns area, select or check the field(s) containing the duplicates you want to remove.Assuming your data set or list has headers, ensure My data has headers is checked.Select Remove Duplicates in the Data Tools group.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). Select a cell in the data set or list containing the duplicates you want to remove.To remove or delete duplicates from a data set using Remove Duplicates on the Data tab in the Ribbon: Removing duplicates using Remove Duplicates on the Data tab in the Ribbon In the sample data set below, the data includes unique headers and duplicate records but no blank rows or columns:īefore removing duplicates, you may want to save a copy of the worksheet or workbook so you can keep the original data. Note: Screenshots in this article are from Excel 365 but are similar in previous versions of Excel. If you are planning on using structured reference formulas in Excel tables, it's easier if the field names do not include spaces. If you have used the Subtotal feature to add subtotals, you should remove them. The process is to identify the duplicates and then delete the duplicate rows.įor all of the techniques below, the list or data set should have: Creating a formula to remove duplicates if there are extra spaces in the data.Using Remove Duplicates on the Table Design or Table Tools Design tab in the Ribbon.Using Remove Duplicates on the Data tab in the Ribbon.
![delete duplicate rows in excel delete duplicate rows in excel](https://i.stack.imgur.com/SjN2x.jpg)
![delete duplicate rows in excel delete duplicate rows in excel](https://cdn.educba.com/academy/wp-content/uploads/2019/01/Excel-Remove-Duplicates-Example-4-3-1-420x404.png)
In this article, we'll review 3 easy ways to remove or delete duplicates in Excel:
#Delete duplicate rows in excel how to#
Recommended article: How to Highlight Errors, Blanks and Duplicates in Microsoft Excelĭo you want to learn more about Excel? Check out our virtual classroom or in-person Excel courses > You'll need to decide what you consider to be a duplicate (based on one or more fields or columns). It's common to remove duplicate rows in a list or data set so that the data can be sorted, filtered and summarized. When you use the Remove Duplicates tool, Excel will keep the first instance and the remaining duplicates in the data set will be deleted. You can remove duplicates in Excel in several ways. Quickly Find and Delete Duplicates in Excel Worksheetsīy Avantix Learning Team | Updated February 20, 2022Īpplies to: Microsoft ® Excel ® 2013, 2016, 2019, 2021 and 365 (Windows)