6 Easy Ways to Merge Multiple Cells at Once in Excel

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.


Approach

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.

how to merge multiple cells at once using merge & center in excel

  • 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.

how to merge multiple cells at once applying merge & center in excel


Approach

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.

how to merge multiple cells at once utilizing merge across in excel


Approach

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.

how to merge multiple cells at once employing merge cells in excel


Approach

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

how to merge multiple cells at once using ampersand operator in excel

  • 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.


Approach

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)

how to merge multiple cells at once executing the concat function in excel

  • Use the Fill Handle icon just like before and you have successfully merged the data into one cell.


Approach

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)

how to merge multiple cells at once applying thee textjoin function in excel

  • 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)

how to merge multiple rows in excel using the concatenate function

  • 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.

(Visited 81 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo