How to Merge Rows and Columns in Excel (3 Smart Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Merge Rows and Columns in Excel: 3 Smart Ways

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.

How to Merge Rows and Columns in Excel


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 Ampersand (&) Symbol to Merge Rows and Columns

  • Similarly, apply the following formula to merge rows.
=C5&", "&C6&", "&C7&", "&C8&", "&C9&", "&C10&", "&C11

Use Ampersand (&) Symbol to Merge Rows and Columns


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.

Employ CONCATENATE Function to Merge Rows and Columns

  • Finally, AutoFill the rest cells in column E.

Read More: How to Merge Two Rows in Excel


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.

Use Notepad to Merge Columns

  • 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.

How to Merge Rows and Columns in Excel

  • 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.

How to Merge Rows and Columns in Excel

  • 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.

How to Merge Rows and Columns in Excel


Download Practice Workbook


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.


Further Readings


<< Go Back to Merge Rows in Excel  | Merge in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo