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

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, we often have to merge two or more cells in Excel at once for various purposes. Here in this article, I will show you 3 quick ways to merge multiple cells in Excel at once.


Download Practice Workbook

You can download our practice workbook from here for free!


Watch Video – Merge Multiple Cells in Excel at Once


3 Quick Ways to Merge Multiple Cells in Excel at Once

Say, we have at hand a dataset of employee records of Toyota Company.

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

Sample Dataset to Merge Multiple Cells in Excel at Once

Now, we want to merge all the data into one column.

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

For example, how to merge the cells B5, C5, D5, and E5 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

You can merge multiple cells in Excel at once by using the Excel toolbar. In this process, you can merge cells in three ways. Go through the steps below to learn all of these ways.

1.1 Merge & Center

The Merge & Center option merges all the cells in one cell. But only the content of the topmost left cell will remain in the center. Follow the steps below to perform this process.

📌 Steps:

  • First and foremost, select the cells (B5:E5 here) you want to merge.
  • Following, go to the Home tab >> Alignment group >> Merge & Center Tool >> Merge & Center option.

Choose the Merge & Center Option to Merge Multiple Cells in Excel at Once

Thus, we will find a merged cell containing only the content of cell B5, Morris.

Merged Cell

Note:

  • Only the content from the topmost left cell remains by this procedure.
  • You can also merge cells vertically in this way. During this, the merged cells will take the topmost value to show.

Read More: How to Merge and Center Cells in Excel (3 Easy Methods)


1.2 Merge Across

The Merge Across option also merges all the cells in one cell. But only the contents from the leftmost columns will remain in this way. Go through the steps below to accomplish this.

📌 Steps:

  • Initially, select the cells (B5:E7) that you want to merge.
  • Subsequently, go to the Home tab >> Alignment group >> Merge & Center tool >> Merge Across option.

Choose the Merge Across Option to Merge Multiple Cells in Excel at Once

As a result, all the cells B5:E7 will be merged to the leftmost content of their respective rows. And, the output would look like this.

Merged Cells

Note: 

  • Only the contents from the leftmost column remain and the rest are overwritten.
  • You can’t merge cells vertically in this way.

Read More: How to Merge Cells in Excel with Data (3 Ways)


1.3 Merge Cells

The Merge Cells option also merges all the cells in one cell. But only the content of the topmost left cell will remain in this way. Follow the steps below to do this.

📌 Steps:

  • At the very beginning, select the cells (B5:E7) that you wanna merge.
  • Following, go to the Home tab >> Alignment group >> Merge & Center tool >> Merge Cells option.

Choose the Merge Cells Option to Merge Multiple Cells in Excel at Once

As a result, you will get the cells B5:E7 merged bearing the top-left content of your selection, Morris. For example, the outcome should look like this.

Merged Cell

Note: 

  • Only the content from the topmost left cell remains and the rest are overwritten.
  • You can merge cells vertically in this way and the top cell’s value will be shown.

Read More: How to Merge Cells in Excel Table (7 Ways)


2. Use Ampersand Operator to Merge Multiple Cells at Once

Now we will merge contents from multiple cells into one separate cell. Here we will merge contents data from columns B, C, D, and E and merge those into column F.

For example, cell F5 will have content like this: Morris Johnson,10,$10000. Follow the steps below to achieve this target.

📌 Steps:

  • First and foremost, select the target cell where you want to merge. Here I am selecting cell F5.
  • Afterward, insert the formula below with the Ampersand (&) symbol in the cell.
=B5&C5&D5&E5
  • Following, hit the Enter key.

Formula to Merge Multiple Cells in Excel at Once

  • Now, look at cell F5. It is written MorrisJohnson1010000, 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 the formula below with spaces and commas where you want, separated by the Ampersand symbol (&).
=B5&" "&C5&","&D5&","&E5
  • Subsequently, hit the Enter key.

Formula to Merge Space, Commas and Cells

  • As a result, you will get Morris Johnson,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.
  • To do this, move your mouse cursor to the bottom right corner of cell F5, and you will find a plus(+) sign.
  • Subsequently, drag it below through the whole column.

Drag Fill Handle Below

As a result, You will be able to merge multiple cells in Excel at once for your whole dataset. And, the output should look like this.

Multiple Cells Merged 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 Accounting format, but column F is in General format. When you merge two or more columns in Excel, the merged column becomes General, by default.

Read More: How to Merge Two Cells in Excel Without Losing Data (2 Ways)


3. Use the CONCATENATE Function

You can also merge contents from multiple cells into one cell in Excel at once by Excel’s CONCATENATE function. You can merge texts also in this way. Go through the steps below to accomplish this.

📌 Steps:

  • First, go to the cell (F5 here) where you want to merge.
  • Following, insert the formula below and press the Enter key.
=CONCATENATE(B5,C5,D5,E5)

CONCATENATE Formula to Merge Multiple Cells in Excel at Once

  • As a result, you will get the output as MorrisJohnson1010000. But we want to insert spaces and commas(,) in between. So, put spaces and commas inside the CONCATENATE function.
  • To do this, Insert the following formula in cell F5 and hit the Enter key.
=CONCATENATE(B5," ",C5,",",D5,",",E5)

Using CONCATENATE Function to Merge Space, Comma and Cells

  • And, the output is Morris Johnson,10,10000.
  • Afterward, move your mouse cursor to the bottom right corner of cell F4, and you will find a plus(+) sign.
  • Subsequently, drag it down through the whole column.

Drag Fill Handle Below

Consequently, You will find all the cells filled automatically like this. And, the final output should look like this.

Merged Cells

Note: You can also do the same thing with the CONCAT function.

Read More: How to Merge Text from Two Cells in Excel (7 Methods)


Similar Readings


Shortcuts to Merge Multiple Cells in Excel

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

📌 Steps: 

  • First, select the cells you want to merge.
  • Next, press Alt on your keyboard.

Press the Alt Key

  • Afterward, press H. It selects the Home tab.

Access Home Tab

  • Finally, press M. It selects the Merge and Center option.

Access Merge & Center Tool

  • Then press any of the following according to your needs.
  • Press C for the Merge and Center option; A for the Merge Across option; Press M for the Merge Cells option; And U for the Unmerge Cells option.

Choose Merging Options


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. Follow the steps below to do this.

📌 Steps:

  • Go to Home tab>> Editing group >> Find and Select tool >> Find option.

Access the Find and Replace Dialogue Box

  • As a result, the Find and Replace dialogue box will appear.
  • Subsequently, click on the Format… button.

Choose the Format Option

  • Consequently, the Find Format window will appear.
  • Following, go to the Alignment tab >> tick on the Merge Cells option >> click on the OK button.

Find Format Window

  • Afterward, the Find and Replace dialogue box will appear again.
  • Choose the Workbook option in the Within: options list and click on the Find All button.

Click on the Find All Button

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

Merged Cells Location


How to Unmerge Cells in Excel

Unmerging cells is as easy as merging cells. Follow the steps below to do this.

  • First, select the merged cells (B5:B6 here).
  • Subsequently, go to the Home tab >> Alignment group >> Merge & Center tool >> Unmerge Cells option.

Choose the Unmerge Cells Option

Thus, you will find the selected merged cells have been unmerged in this way and look like the following figure now.

Unmerged Cells


Conclusion

So, in this article, I have shown you 3 quick ways to merge multiple cells in Excel at once. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

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 the 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo