Video: How to merge duplicate cells in Excel

Learn different ways you can combine values or sum numbers that refer to the same record in Excel

When you need to consolidate duplicates in Excel, the task is complicated by the unique values or numbers that you need to keep. This video describes the ways you can sum numbers and the steps to combine unique values when merging rows.

How to combine duplicate rows in Excel: video transcript

In this video we will learn how to keep unique values when merging duplicate rows in Microsoft Excel. You may have text values associated with the same record, or unique numbers that you want to count.

Consolidate duplicates in excel and sum numbers

When you deal with numbers, Excel has ready solutions for you. Let's count the number of books by different authors we have in this list.

Perhaps the best way to go is to add a pivot table. Open the Insert tab, click Pivot Table, and make sure the selected range contains all your data. Choose where you want to see the results and click ok. Now you can say what rows you want to see here: the author's name is a key column in our example, and we need to take "Quantity" for our values to see the number of books you have by each author.

Now let's go back to our data and look at two more ways that are just as simple.

Go to Data tab and click on the Consolidate icon. You can change the function to any one you prefer and then select the range with your data. Please note that you need to keep your key column leftmost because you want to use its values as labels. We add the reference and tick off "Top row" and "Left column" as our labels, and click Ok.

You can also see the summary by selecting your data and clicking on Subtotal on the same tab. Our key column goes for the first field here: "At each change in" the author's name,
select what function to use, and add Subtotal to the column with numbers that you have, Quantity in our case. Click Ok and see the results right here.

As you can see, work with numbers is designed very well in Excel, so you can easily combine numbers from duplicate rows using the provided tools.

Merge duplicates and keep unique values

Things get a little trickier when it comes to text values. It's quite a common task: if you want to keep one author per line yet list all their books in this column, there are two ways out I'm going to show you: a couple of formulas we can employ, and a special add-in designed for this.

The formulas take some time and effort, but they'll do the job if you have a simple table. One preliminary is sorting the table by your main column. You will also need two helper columns for the formulas. One will merge the book titles if the values in the main column are the same. Enter

=IF(A2=A1,C1&", "&B2,B2)

The quickest way to apply the formula to every row is pointing to the bottom-right corner of the cell until you see a black plus sign and double-clicking it.

We're going to use the second helper column for another IF statement that will look for the complete book listing. Enter

=IF(A3<>A2,"Merged values","")

And paste the formula down. Now that you have the results, copy them and paste as values.

You will need to sort the table again, this time by the last helper column, and the order shall be descending. This way you'll bring all unique rows with merged values to the top. Then you can remove the rows and columns you no longer need.

If you want to do this a lot quicker and easier, Combine Rows Wizard will be your best assistant. It also gives you the possibility to work with multiple columns and it handles many details in the background.

  • Go to Ablebits Data tab and click Combine Rows. You will see how it selects the entire range but you can change right in this field or by using the icon next to it. If you prefer to keep the original table, choose to create a "backup copy", and follow to the next step.
  • Here you pick the combination of the main columns with duplicates, which is author's first and last name in our case. This means the add-in will look for the same names to bring the rest of information to one row.
  • Click Next and choose the records you want to combine, pick the delimiter for them right here. Line break is a great option for this example. I also want to keep the IDs.

    You can change the delimiter for all selected columns at the top of the window. "Delete duplicate values" is a huge bonus: the add-in will check the values it combines and keep the same book title from appearing twice. You can also leave the blanks out with the help of this box.

    Click Finish and you'll see all the records in place.

Feel free to use the coupon code Preview to get a discount for Combine Rows wizard or the entire Ultimate Suite. If you have any questions, please write to support@ablebits.com, we'll do our best to help you.

2 Responses to "Video: How to merge duplicate cells in Excel"

  1. xyz says:

    Thank you so much. It worked for me.

  2. Nicole says:

    Re the last option, merging cells with duplicate (text) values. How do I do this on 2013 excel?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard