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.
Use Merge Cells to combine the rows

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.
Choose the settings and click Merge

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.
Use CONCATENATE to merge 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.
Use formula in a helper column before running the add-in

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.
See the results of Combine Rows Wizard work

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.

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.