Rifat Hassan
While working in Excel, we often have to merge two or more cells in Excel at once for various purposes. Here in this article, we’ll show you 3 quick ways to merge multiple cells in Excel at once.
Watch Video – 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.
Now, we want to merge all the data into one column.
First we’ll 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 we’ll show how to merge the contents of two or more cells into one single text in another cell using Excel Formulas.
1. Merging 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 Applying 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.
Thus, we will find a merged cell containing only the content of cell B5, Morris.
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
1.2 Using 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.
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.
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
1.3 Using 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.
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.
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
2. Using Ampersand Operator to Merge Multiple Cells in Excel 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.
- 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.
- 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.
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.
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.
3. Inserting CONCATENATE Function to Merge Multiple Cells at Once
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)
- 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)
- 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.
Consequently, You will find all the cells filled automatically like this. And, the final output should look like this.
Similar Readings
Shortcuts to Merge Multiple Cells in Excel
You can use your keyboard shortcut to navigate to these Merge Options. Follow the steps below:
📌 Steps:Â
- First, select the cells you want to merge.
- Next, press Alt on your keyboard.
- Afterward, press H. It selects the Home tab.
- Finally, press M. It selects the Merge and Center option.
- 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.
Read More: How to Merge Multiple Cells without Losing Data in Excel
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.
- As a result, the Find and Replace dialogue box will appear.
- Subsequently, click on the Format… button.
- Consequently, the Find Format window will appear.
- Following, go to the Alignment tab >> tick on the Merge Cells option >> click on the OK button.
- 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.
Consequently, you will find all the locations of the merged cells with Sheet names, Cell references, and Values. Just like this.
Read More: How to Merge Cells in Excel Without Merging Actually
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.
Thus, you will find the selected merged cells have been unmerged in this way and look like the following figure now.
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
So, in this article, we have shown you 3 quick ways to merge multiple cells in Excel at once. We suggest you read the full article carefully and practice accordingly. We hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.
