Video: how to merge and combine rows in Excel

See how you can merge multiple rows in Excel with the help of formulas or an add-in, learn a way to merge blocks of rows, and combine duplicate rows in Excel.

There are different ways you may need to bring data together in Excel. First we'll look at two solutions for merging multiple rows of data, then you'll see how to repeatedly merge blocks of rows, and we will also consider such task as consolidating rows that refer to the same record. Learn what formulas and tools you can use to combine rows in Excel and keep all important information intact.

How to merge rows in Excel: four quick solutions - video transcript

In our previous video we showed ways of merging columns of data, now let's consider a slightly different task and see how we can combine rows.

Let's look at our first example. Each column has some records that take several rows, while I want them in one cell. As you know, trying to do this with the Merge and Center option will not work because we will lose all our data, so we can either work with formulas or an add-in. The quickest solution we can use is the Merge Cells add-in that our developers created for this purpose.
I go to Ablebits data tab and click on the Merge Cells icon to run the tool. There are three settings we need:

  1. We choose to merge the cells "column by column"; you can see this hint to make sure you select the right option.
  2. Then we enter the delimiter for the values. It can be any character, but we'll select a line break here.
  3. We can also choose where to place the results: into the top or the bottom row.

Of all the options below, I want to make sure it ignores blanks, clears the cells that we use for the merge, and wraps text. I will also choose to create a backup copy of my sheet as you can't cancel the changes made by add-ins. Click Merge and bring all values from different rows together.

You can do the same with the concatenate function. We'll need to use a helper row for the formula, and enter CONCATENATE, open brackets, add the first cell with the value, use commas to add a delimiter to it, and then add the second cell with the value, add the second delimiter with commas around, and the third cell. Once we enter the first formula, we can copy it to the right to apply it to all similar cells.

The task gets a little trickier if you need to do it for every certain number of rows, so you need to get a row-per-record kind of table and bring together all the information that takes three lines. You may or may not have this first column, so we won't rely on it. It may sound like a simple task, but, surprisingly, there are no simple solutions.

So what you can do, is use an Excel formula in combination with an add-in. First you need to create a helper column. What we'll do here is assign a unique number to each block of rows that we want to merge. If you have a header row, then enter the formula into the second cell in your new column: =INT - this will let us get the nearest integers for the calculation results in brackets, then we open brackets again, and write ROW to get our row number, and add a reference to our cell in brackets. Then we subtract 2 from our row number because we want to start counting from 0 and close brackets. Then we divide this number we get by the number of rows in each block - so as we want to merge every 3 rows, we use number 3.

We copy the formula down, or simply double-click on the black cross in the bottom-right corner of the cells, and as you can see we get a unique number assigned to each block of rows.

Now our add-in, Combine Rows Wizard, can use this helper column to identify the same records and bring values in other columns together. Let's see how it works: we start the add-in from Ablebits Data tab and follow three simple steps:

  1. First we make sure the entire table is selected;
  2. On the second step we pick the key column, which is the one with your formula;
  3. Then we choose the columns with the values we want to bring together and pick a delimiter, or enter a custom one.

Here you go.

Let's look at another example. When you have a table with several rows that refer to the same record, and I want to see one row per each account number that simply lists all books in one cell. You can use Combine Rows Wizard add-in in this case as well.

We click on the icon to start it, choose the range with the table, select our key column, which is the account number in our case, and then we choose the columns with the data to combine. Pick a delimiter, and that's it!

Depending on the way you need to merge your rows, you can work with the standard functions, use the add-ins, or combine both solutions. You can get 15% off our add-ins if you use the coupon code preview.

Feel free to share your questions and comments if there is anything you'd like to clarify, and subscribe to our channel for more tips and tricks on Microsoft Excel.

See also

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