Alok
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.
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.
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.
- 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.
- If we use a delimiter like Comma(,), the formula will be:
=D4&","&D5&","&D6- Then press Enter.
- Consequently, here the result is!
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.
- 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.
- Afterward, pull the Fill Handle icon to the right and get the result for the rest of the cells.
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.
- Afterward, modify the formula to add space. So, the formula becomes:
=CONCATENATE(C4,",",C5,",",C6)- Then press Enter.
- We can also add space and the result would be as the following screenshot.
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.
- Pull the Fill Handle towards the right and we will get the result for the rest of the rows.
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).
- After clicking the anchor, get the Clipboard panel.
- 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.
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.
- After clicking the Merge Cells, we will get a warning Pop-Up.
- Then press OK.
- 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.
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
