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:
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.
Then, go to the Home tab and click Merge & Center.
After that, your value is combined into one cell.
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.
Select all and click Merge & Center.
After that, it will show you this 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.
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,)
Then press Enter.
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.
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:
Then Press Enter. It will merge First Name and Last Name into the Full name column.
Then, drag the fill handle (the + sign) across all the columns to copy the formula.
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.
Then open a Notepad and paste it. It will look like this.
Now, somewhere in Notepad, press Tab. And copy that space with Ctrl+C.
Select all the text and go to the option File.
Now click Replace.
Paste the Tab value that you copied by Ctrl+V.
Now in the replace with box, enter a single Space.
Then click Replace All.
Here all the Tab is replaced with a single space. Now copy all the data.
Create a new column “Name”.
Click one the first element.
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.
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.