Combine duplicate rows in Excel

Select an Excel table

Follow the steps below to merge duplicated rows in Excel.

1. Open the workbook with the table where you need to combine duplicate rows into one record. Then open Combine Rows Wizard by clicking on its icon.
   
   When working with the add-in, on the left you can see the step you are on.
2. You can select your table by clicking the select range icon or by typing its address in the Select your table field manually.
   

   Note

   If you have standard auto filter option switched on, the add-in will merge only visible filtered duplicate rows in Excel. If you need to work with the entire table, turn the filter off before you run the add-in. If you want to work with some part of the table only, apply auto filter to it and then only the visible rows of your table will be processed and merged.

3. Click the Next button to combine duplicate records in Excel.

Select key columns to check for duplicates

Key columns are the columns that will be checked for duplicate entries. You can select one or more columns to merge duplicate records into one.

1. Tick the checkboxes next to the columns with the duplicated values.
2. If you have a lot of columns in your table, take advantage of the Select All button and all the checkboxes in the table with the list of columns will be checked. Then you can deselect those you don't need to match.
3. The 1st row content column next to Columns shows the 1st item of each column in your table. It can be useful if your worksheet doesn't have headers so you can see what kind of information is in the columns.
4. On the Select key columns step you can also choose to ignore extra spaces, indicate whether your table has headers or ignore empty cells.
   You should tick the Ignore extra spaces checkbox if your data may have some extra leading or trailing spaces. This option will prevent the add-in from missing the duplicate values.

   Tip	Tip: We always recommend using our free tool Trim Spaces for Microsoft Excel before combining duplicate rows into one record; it will remove all leading and trailing spaces in your table instead of just ignoring them.

5. It is also possible to indicate if there are header rows in your table. As a rule, the add-in indicates headers automatically. If it doesn't, you can check My table has headers. You can also uncheck this option if you want to include the first row.
   
6. On this step you can choose to skip empty cells. Select the Ignore empty cells option and the add-in will not take them into consideration.
7. Click on the Next button on the to merge duplicate rows in your Excel worksheet into one.

Select columns to merge

On this step you choose one or more columns with duplicate values in your Excel to combine into one record. You will see a list of columns in the table that were not selected as key columns.

1. If you have a really big table with numerous columns, you can benefit from the Select All and Unselect All buttons.
2. Tick the checkbox(es) next to the column(s) with the data you need to merge.
3. Choose a delimiter that will separate the merged values. You can either enter your own delimiter or select it from the predefined options.
   When you select a column in the list, you can see an arrow next to the name of the column in the Delimiters section. Click on the arrow and pick the delimiter you need to separate the joined values from the drop-down list. You can choose among semicolon, comma, space or line break. It is also possible to enter any delimiter of your choice. To do it, click in the Delimiter field next to the column name and type the separator you need.
4. On this step you can choose to remove duplicates or skip empty values.
   • Tick the Delete duplicate values checkbox if the columns with the matching values you need to combine into one record may contain identical items.
   • Select the Skip empty values option to avoid merging empty cells which can further result in extra blanks in your spreadsheet.
5. Then click the Finish button and see that all Excel duplicate rows are perfectly merged.

The Combine Rows Wizard joined 37 duplicate rows in under 1 minute!