Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Combine Rows in Excel (6 Easy Methods)

We need Excel for our day-to-day work. We process raw data and get information as per our needs. While processing data in Excel, sometimes we need to combine rows or columns. In this article, we will discuss how to combine rows in Excel.

In the following figure, we combine rows in Excel with the aid of the CONCAT function.

overview of combining rows using CONCAT function


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Methods to Combine Rows in Excel

We will discuss 6 methods to combine rows in Excel. We are going to use the following dataset consisting of 2 students’ names, IDs, and grades in a particular subject.

sample dataset

This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Combine Excel Rows Using Ampersand Symbol

We can easily combine rows using the Ampersand (&) symbol.

Steps:

  • First, double-click on Cell B8.
  • Now, input the 1st cell reference, then ampersand then 2nd cell reference, and continue. So, the input formula will look like the following:
=B4&B5&B6
  • Then press Enter.

using ampersand operator to combine rows in Excel

  • As we can see, a delimiter, like a space (” “), would make the output more meaningful. Hence, we will see how to use a delimiter in the output result.
  • Afterward, modify the formula to add space. So, the formula becomes:
=C4&" "&C5&" "&C6
  • Then press Enter.

combine values of C4, C5 and C6 cells

  • If we use a delimiter like Comma(,), the formula will be:
=D4&","&D5&","&D6
  • Then press Enter.
  • Consequently, here the result is!

get the output of ampersand operator


2. Use CONCAT Function

We can combine rows with different functions. In this section, we will discuss the CONCAT function to combine rows.

Steps:

  • First, type the following formula in Cell B8.
=CONCAT(B4,B5,B6)
  • Then press Enter.

combine rows using CONCAT function

  • When we have more than two rows of data, we can select the whole range instead of separating the text using a comma repeatedly.

The formula will be:

=CONCAT(C4:C6)
  • After entering the formula in Cell B8, press Enter.

using CONCAT function combine multiple rows

  • Afterward, pull the Fill Handle icon to the right and get the result for the rest of the cells.

final output of CONCAT function


3. Insert CONCATENATE Function to Combine Rows

The CONCATENATE function is one of the text functions to join two or more text strings into one string.

Steps:

  • First, type the following formula in Cell B8.
=CONCATENATE(B4,B5,B6)
  • Then press Enter.
  • We could add a delimiter (“,”) too. Follow the steps below.

insert CONCATENATE function to combine rows

  • Afterward, modify the formula to add space. So, the formula becomes:
=CONCATENATE(C4,",",C5,",",C6)
  • Then press Enter.

combine mutilple rows

  • We can also add space and the result would be as the following screenshot.

final output of CONCATENATE function


4. Apply TEXTJOIN Function to Amalgamate Rows

The TEXTJOIN function integrates text from numerous ranges of strings. It also contains a delimiter. This delimiter stays between the texts and then joins them.

Now, follow the steps below to see how to use the TEXTJOIN function to combine rows.

Steps:

  • First, go to cell B8.
  • Type the TEXTJOIN function.
  • Then, complete formula and the formula will be like:
=TEXTJOIN(", ",TRUE,B4:B6)
  • In the 1st argument use comma(,), as we want to use this as a delimiter.
  • We want to ignore the empty cells so select TRUE in the 2nd argument
  • In the last argument select the range we want to combine.
  • Then press Enter.

combine rows using TEXTJOIN function

  • Pull the Fill Handle towards the right and we will get the result for the rest of the rows.

output of TEXTJOIN function


5. Use Clipboard Functionality

We can easily merge the rows utilizing the Clipboard tool in Excel.

Steps:

  • First, go to the Home tab.
  • From the Clipboard, find the anchor-like symbol (shown in the following image).

click on Clipboad symbol

  • After clicking the anchor, get the Clipboard panel.

copy and paste the value to combine rows

  • Now, select the rows we want to combine.
  • Copy them by Ctrl+C.
  • Now, select any blank cell of the sheet and double-click on that.
  • We will see the Paste All option on the clipboard panel.
  • After clicking the Paste All we will get the below result.

get the output


6. Combine Rows Using Merge & Center Command

In this part, we will discuss Merge & Center for combining rows in Excel. This may be applied where the same data is typed in adjacent cells and we need a better presentation.

Steps:

  • Select the cells first. We selected B5 and B6.
  • Then go to the Home tab.
  • From the Home tab goes to Merge & Center command.
  • From the dropdown select Merge Cells.

click on Merge cells to combine rows

  • After clicking the Merge Cells, we will get a warning Pop-Up.

click on OK

  • Then press OK.

get the first output

  • Therefore, the Selected two rows are combined.
  • Again, select B7 and B8, where B8 is blank.
  • Follow the previous procedures and get the Merged result below.

get the final output

Note:

In this process, only the active cell value will sustain, and other cell data will be lost. So, we strongly recommend not using this technique when you have values in different cells and all of them are significant.


Conclusion

We’ve discussed how to combine rows in Excel here. Hope this satisfies your needs. Please give your feedback in the comment box. Please visit our website Exceldemy.com to explore more.


Related Articles

 

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo