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

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

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.

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

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.

Employ CONCATENATE Function to Merge Rows and Columns

  • Finally, AutoFill the rest cells in column E.


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

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.


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo