How to Merge Multiple Cells in Excel at Once (3 Quick Ways)

Multiple Cells Merged in a Cell in Excel

While working in Excel, we often have to merge multiple cells in Excel at once for various purposes. Here in this article, I am showing some easy ways to merge two or more cells in Excel at once.


Download File


3 Quick Ways to Merge Multiple Cells in Excel at Once

We have at hand a dataset of employee records of Toyota Company.

This contains the first name of the employees, the last names, starting days, per day duty, and total salary listed in an Excel worksheet, all in different columns. See the image below.

Main data set to Merge Multiple Cells in Excel at Once

For some reason, we want to merge all the data in one column.

First I will show how to merge one or more cells in the same location by using Excel Toolbar.

For example, how to merge the cells A4, B4, C4, and D4 together in the same location.

Then I will show how to merge the contents of two or more cells into one single text in another cell using Excel Formulas.


1. Merge Multiple Cells in Excel at Once by Using Excel Toolbar

In the Excel Toolbar, under the Home tab, there is an option called the Merge and Center.

Merge and Center option to Merge Multiple Cells in Excel at Once

Click this and you will find four options.

  • First, Merge and Center
  • Then, Merge Across
  • Then, Merge Cells
  • And finally, Unmerge Cells

Merge and Center:

The Merge and Center option merges all the cells in one cell.

But only the content of the topmost left cell will remain in the center.

Here I have selected cells A4, B4, C4, and D4 and clicked on the Merge and center. 

And found a merged cell containing only the content of A4, Morris.

Limitation: Only the content from the topmost left cell remains by this.

Merge Across:

The Merge Across option also merges all the cells in one cell.

But only the contents from the leftmost columns will remain aligned on the left.

Here I have selected cell A4 to C6 and clicked Merge Across, and see what has happened.

Output of Merge Across Option to Merge Multiple Cells in Excel at Once

Limitation: Only the contents from the leftmost column remain and the rest are overwritten.

Merge Cells:

The Merge Cell option also merges all the cells in one cell.

But only the content of the topmost left cell will remain aligned in the leftmost bottom corner.

Here I have selected cells A4 to C6 and clicked Merge Cells, and see what has happened.

Output of Merge Cells Option to Merge Multiple Cells in Excel at Once

Limitation: Only the content from the topmost left cell remains and the rest are overwritten.

Note: You can use your keyboard shortcut for navigating to these Merge Options. Follow these steps:

First, select the cells you want to merge.

Then press Alt on your keyboard.

Then press H. It selects the Home tab.

Finally, press M.

It selects the Merge and Center option.

Then press any of the following:

  • Press C for Merge and Center.
  • A for Merge Across.
  • Press M for Merge Cells.
  • And U for Unmerge Cells.


2. Merge Multiple Cells in Excel at Once by Using the Ampersand symbol (&)

Now we will merge contents from multiple cells into one separate cell.

Here we will merge contents data from columns A, B, C, and D and merge those into column F.

For example, the cell F4 will have the content like this, Morris Johnson, 23/02/2011, 10, $10000.00

First of all, select the target cell where you want to merge. Here I am selecting cell F4.

First Cell of Merged Column to Merge Multiple Cells in Excel at Once

Now write the references of the cells which you want to merge, separated by the Ampersand symbol (&).

Notice the Formula Bar in the image below.

Formula of Merging Cells to Merge Multiple Cells in Excel at Once

Now, look at cell F4. It is written MorrisJohnson23/02/201110$10000.00, which is literally gibberish.

So, we want them separated by space and comma(,). How do we achieve this?

It is easy. Go to the cell again and put spaces and commas where you want, separated by the Ampersand symbol (&).

Notice the Formula Bar again in the image below.

Now see, we have got Morris Johnson, 23/02/2011, 10, 10000. Just the thing we want. 

Now we want all the remaining cells of column F filled in this way. This is easy too.

Move your mouse cursor on the bottom right corner of cell F4, and you will find a plus(+) sign.

Drag it through the whole column. You will find all the cells filled automatically like this.

Dragging through a Column to Merge Multiple Cells in Excel at Once

Note: If you look carefully, you will find that the Dollar Sign ($) in column E is absent in the merged column F.

It is due to the fact that column E was in Number format (Currency), but column F is in General Text format.

When you merge two or more columns in Excel, the merged column becomes General Text, by default.


Similar Readings:


3. Merge Multiple Cells in Excel at Once by Using the CONCATENATE function

You can also merge contents from multiple cells into one cell by Excel’s CONCATENATE function.

First, go to the cell where you want to merge. I am going to cell F4.

Using the CONCATENATE function is very simple.

It takes the references of the cells that are to be merged as arguments and returns their merged text as output. 

Here I am writing:

=CONCATENATE(A4,B4,C4,D4,E4)

And get the output as MorrisJohnson23/3/20101010000. Look at the image below.

Note: You can also write it as a CONCAT function.

CONCATENATE Formula to Merge Multiple Cells in Excel at Once

But we want to insert spaces and commas(,) in between. How to put these?

Quite easy. The CONCATENATE function also takes text strings as arguments.

So put spaces and commas inside the CONCATENATE function.

Here I am writing:

=CONCATENATE(A4," ",B4,",",C4,",",D4,",",E4)

And the output is Morris Johnson,23/2/2010,10,10000.

Now we want all the remaining cells of column F filled in this way.

Move your mouse cursor on the bottom right corner of cell F4, and you will find a plus(+) sign.

Drag it through the whole column. You will find all the cells filled automatically like this.

Dragging through a Column to Merge Multiple Cells in Excel at Once


Tips Regarding the Merged Cells in Excel


Tip 1: How to Quickly Find the Merged Cells in Excel

You can quickly find the merged cells in your Excel worksheet by using the Excel Toolbar. Go to Home> Find and select.

Find and Select Option to Merge Multiple Cells in Excel at Once

Then click Find. You will get a small dialogue box like this.

Click Options>Format>Format. You will get another dialogue box like this.

Format Box to Merge Multiple Cells in Excel at Once

Go to Alignment. Put a Check on Merge Cells. Then click OK. Then click Find All in the Find and Replace box.

You will find all the locations of the merged cells with Sheet names, Cell references, and Values. Just like this.

Merged Cells Location to Merge Multiple Cells in Excel at Once


Tip 2: How to Unmerge Cells in Excel

Unmerging cells is as easy as merging cells. First, select the merged cells. 

Then in the Excel Toolbar, go to Home>Merge and Center. Click it.

You will find four options. Click Unmerge Cells. 

Unmerged Cells to Merge Multiple Cells in Excel at Once

You will find the selected merged cells have been unmerged in this way.


Conclusion

Thus, using these methods, you can merge or unmerge two or more cells in one cell in an Excel file very easily. Do you know any other option? If you know, let us know in the comment section.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo