Nazmus Sakib
Merging cells is one of the basic and widely used tasks that all of us need to perform in Excel. Here, we will demonstrate how to merge multiple cells in Excel at once. First, we will use the Excel Ribbon to merge cells, and later we will apply formulas to merge data.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Merge Multiple Cells at Once in Excel with These 6 Easy Approaches
In this article, we will learn to merge multiple cells in Excel using the Excel Ribbon along with formulas. We will discuss the methods using the following sample dataset.
1. Using Merge & Center Option
In our first approach, we will show how to use Merge & Center from the Alignment group.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cells B6 to E6.
- Then, navigate to Home Under the Alignment group, click Merge & Center.
- A warning dialog box will appear stating Merging cells only keeps the upper-left value and discards other values.
- If you click OK, the cells will merge into one cell containing the value stored in cell B6. The cell will also be referred to as cell B6 in the Name Box.
2. Applying Merge Across to Multiple Rows
In our second approach, we will use Merge Across option to merge multiple rows.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cells B6 to E8.
- Now, select Home tab and from the Merge & Center drop-down menu, select Merge Across.
- Just like the previous method, a warning dialog box will appear on the screen.
- Keep clicking OK until Excel merges the rows containing only the leftmost values just like in the screenshot below.
3. Integrating Cells into One Cell Utilizing Merge Cells
Now, we will show how to apply Merge Cells option to merge multiple cells.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cells B6 to E7, and from Home tab, using the Merge & Center drop-down menu, click Merge Cells.
- Just like in the previous methods, a warning dialog box will come. Click OK.
- Now you will find the merged cell containing the value of cell B6 only.
4. Applying Ampersand Operator
Next, we will utilize Ampersand (&) operator in a formula to merge data.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell F6 and Enter the following formula:
=B6&", "&C6&", "&D6
- Next, use the Fill Handle icon from bottom right corner of the cell and double-click.
- All the values in the first three columns are merged. We added a comma and a space between the values.
Note: The merged data will be in the General Format, albeit the values in the Cost column are in Currency Format.
5. Implementing CONCAT Function
Now it is time to use the CONCAT function to merge data.
⬇️⬇️ STEPS ⬇️⬇️
- Now, select cell F6 and type in the following formula, and press Enter:
=CONCAT(B6," ",C6," ",D6)
- Use the Fill Handle icon just like before and you have successfully merged the data into one cell.
6. Employing TEXTJOIN Function
The TEXTJOIN function is kind of an upgrade to the CONCAT function. Let’s see how to use that.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell F6 and Enter the formula below:
=TEXTJOIN(", ", TRUE, B6:D6)
- Use the Fill Handle icon.
- You’re all set.
🔨 Formula Breakdown
TEXTJOIN(“, “, TRUE, B6:D6)
👉 First, we used a comma as the Delimiter.
👉 Next, we wrote TRUE to ignore the empty cells.
👉 Finally, we selected cells B6 to D6 to merge.
How to Merge Multiple Rows in Excel at Once
Now we will learn how to merge multiple rows using the CONCATENATE function.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell B13 and Enter the formula below:
=CONCATENATE(B6," ",B7," ",B8," ",B9," ",B10)
- Done! We have merged the values from multiple rows.
- Here, we merged cell B6 to E6 to show the result clearly using the Merge & Center option.
📝 Takeaways from This Article
📌 Firstly, we showed how to merge multiple cells using the Merge & Center option.
📌 Secondly, we applied Merge Across option to merge multiple rows.
📌 Later, we merged cells by utilizing the Merge Cells option.
📌 Next, we used the Ampersand operator to merge data.
📌 Afterward, we used the CONCAT function to merge data.
📌 Then, we employed the TEXTJOIN function to merge data efficiently.
📌 Finally, we learned to use the CONCATENATE function to merge multiple rows.
Conclusion
That concludes the discussion for today. These are some convenient methods to merge multiple cells in Excel at once. If you want to give any recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
