One of the most commonly used Excel features is Merge. Merge is combining multiple cells into one cell. The basic Merge option combines multiple cells into one. But it doesn’t give reliable results for merging rows and columns in Excel. For this, we are going to approach it differently. In this article, I will try to explain 3 smart ways on the topic of how to merge rows and columns in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.
Download Practice Workbook
3 Smart Ways to Merge Rows and Columns in Excel
In order to merge rows and columns in Excel, we can use the Ampersand (&) sign, the CONCATENATE function or with the help of Notepad. To make it more clear, I am going to explain the whole procedure with the following dataset where I have arranged personal informations in ID, First Name, Last Name, and Country columns. I am going to merge the cell values in the First Name and Last Name columns to have full name in the Name column. Let’s march forward to have the full procedures.
1. Use Ampersand (&) Symbol to Merge Rows and Columns
The simplest way to merge rows and columns in Excel is by using the Ampersand (&) symbol. It will connect all the cell values into one by merging. See the following section to have clear vision.
Steps:
- Select a cell and insert the following formula in that cell to merge the rows and columns.
=C5 & " " &D5- Next, press ENTER to have the merged result.
- Use Fill Handle to AutoFill the rest cells in column E.
- Similarly, apply the following formula to merge rows.
=C5&", "&C6&", "&C7&", "&C8&", "&C9&", "&C10&", "&C11
Read More: Merge Two Columns in Excel with First Name and Last Name
2. Employ CONCATENATE Function to Merge Rows and Columns
Another smartest way to merge rows and columns is to employ the CONCATENATE function. Follow the following steps to do it perfectly.
Steps:
- Apply the following formula in your preferred cell to merge the rows and columns.
=CONCATENATE(C5," ",D5)- Followingly, hit ENTER to have the merged result.
- Finally, AutoFill the rest cells in column E.
Read More: How to Merge Two Rows in Excel (4 Easy Ways)
3. Use Notepad to Merge Columns in Excel
We can also use the Notepad software to merge columns. It’s not too difficult at all. Let’s go through the whole process.
Steps:
- First of all, select all the cells that you want to merge and press CTRL + C to copy them.
- Open the Notepad software and hit CTRL + V to paste the copied values.
- There will be space between the values of the two columns. Select the entire space between those cells and copy it.
- After that, select all the values in the Notepad.
- Followingly, go to the Edit tab and pick Replace…
- Alternatively, you can just press CTRL + H to have the Replace… option.
- Now, paste the copied space in the Find what section and input space in the Replace with section.
- After that, click on Replace All.
- We will have the merged values in the Notepad.
- Now, copy the merged values.
- Finally, paste the copied values in your preferred location to have the merged column values.
Read More: How To Merge Two Columns In Excel (6 Easy Ways)
Conclusion
At the end of this article, I like to add that I have tried to explain 3 smart ways on the topic of how to merge rows and columns in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
