How to Merge Rows and Columns in Excel (2 Ways)

One of the most commonly used Excel functions is the Merge function. Merge is combining multiple cells into one cell. In this tutorial, we are gonna show how to merge rows and columns in excel.

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.


Download Practice Workbook


Basic Merge of Rows and Columns in Excel

We can merge cells using the Merge & Center option from the Home tab. 

Look at this dataset:

Basic Data for merge rows in excel

In the C3 cell, we have a value Microsoft Excel. We want to combine multiple cells to show this value.

First, select all the cells that you want to combine. Here, we have selected B3 to E3.

basic dataset highlighting to merge

Then, go to the Home tab and click Merge & Center.

Merge & Center option in Home Tab

After that, your value is combined into one cell.

Basic Dataset Merged


Merge Rows in Excel

Merging rows doesn’t work like the previous example. Let’s look at this dataset. Here Microsoft Excel is divided into two different cells. Microsoft is in cell B2 and Excel is in cell C2. 

Basic Data Different cells

Select all and click Merge & Center.

Basic data merge

After that, it will show you this prompt.

Prompt

This is not very useful in this case. It only keeps the upper-leftmost value in the cell and discards every other value. 

To solve this problem, we are gonna use the CONCATENATE function. This function concatenates two cells along with their data. 

Here is our data set of multiple rows.

Merge rows data

We want to merge it into a single cell. For that, first, select any cell outside this.

Then type the formula:

=CONCATENATE(C3," ",C4," ", C5," ",C6," ", C7," ", C8,)

Select Rows to merge

Then press Enter.

How to merge rows and columns in excel

Our data is now combined in a single cell.


Merge Columns in Excel

Now, merging columns also doesn’t work by using the Merging option in Excel. The CONCATENATE function will also work to merge columns. We are also showing a method using notepad which is a little bit sophisticated but it will also give you the same result.

1. Merge Columns Using Formula

Here is our dataset of some actors.

Merge Columns Dataset

We want to merge the First Name and Last Name into one column.

First, insert a new column “Full Name” before the Country column. Then in the first cell write this formula:

=CONCATENATE(C3," ",D3)

formula to merge columns in excel

Then Press Enter. It will merge First Name and Last Name into the Full name column.

Result of merged columns

Then, drag the fill handle (the + sign) across all the columns to copy the formula.

How to merge rows and columns in excel

Here, the Full name column contains all the values from the First Name and Last Name.

2. Merge Columns Using Notepad

This is a little bit complex but it will be easier after you practice this process.

Here we will also work on the same dataset of actors.

First Copy all the values of First Name and Last Name.

Copy Data to paste in notepad

Then open a Notepad and paste it. It will look like this.

Paste Data to merged

Now, somewhere in Notepad, press Tab. And copy that space with Ctrl+C. 

Select all the text and go to the option File.

Edit option after selecting all

Now click Replace.

Find Tab to enter value

Paste the Tab value that you copied by Ctrl+V.

Now in the replace with box, enter a single Space. 

Then click Replace All.

Data without tab space

Here all the Tab is replaced with a single space. Now copy all the data.

Create a new column “Name”.

New column to show merged values

Click one the first element.

paste data to merge

Paste all the values that you copied from the notepad. Press Enter. All the values will be in a single column. Later you can delete the First Name and Last Name column.

Merged columns after pasting from notepad


Conclusion

Merging columns and rows is the most commonly used scenario that you will see. These formulas will help you to merge rows and columns easily. Make sure you download our practice workbook and practice it. Also, check out our website Exceldemy.com other articles on various Excel-related problems.


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