Excel Merge Cells (All You Need to Know)

Get FREE Advanced Excel Exercises with Solutions!

Merging cells means merging two or more cells into one bigger cell in Excel. Merge Cells act like one single cell.

In this tutorial, you will learn everything about merging cells in Excel.


Watch Video – Merge Multiple Cells at Once in Excel


For example, we are merging B6:B14 cells using the Merge & Center feature. Below, I’m attaching the image for your better understanding.

Excel Merge Cells

In this blog post, we will learn

– To merge cells
– To merge cells in a table
– To move merge cells
– To find merge cells
– Reasons and solutions when Merge & Center tool doesn’t work
– To combine cells with some Excel functions and feature
– An alternative option to merge cells
– To unmerge cells

Here, I have used the Microsoft 365 version of Excel to make this article. However, you can use any version of Excel to get the Merge & Center feature.

We have also used some functions to merge cells in Excel. You will find the CONCATENATE function in Excel versions from 2007, and the CONCAT function from 2016 but the TEXTJOIN function is not available in any older version than 2019.


What Does Merge Cells Mean?
Merge Cells vs Combine Cells
Things You Need to Know
Merge Cells in Excel
    Using Merge & Center Command
    Using Keyboard Shortcuts
    Merge Cells with VBA
    Use of Alignment Feature
Merge Cells in Excel Table
Move Merged Cells
Find Merged Cells
Why Can’t I Merge Cells?
Merge Cells Without Losing Data
Alternative of Merging Cells
Unmerge Cells


What Does ‘Merge Cells’ Mean in Excel?

In Excel, ‘Merge Cells’ means combining cells into a bigger one but keeping only the value of the 1st cell.

For example, you have some cells (D6:D11) having the same values next to each other, and you want to make them into one single cell, so what will you do now?

What is Merge Cells

You will merge these cells, right? By doing this, all these cells will combine into one large cell. Here, D6:D11 cells act like one single cell. However, the value from the top-left cell will stay (D6), and the rest (D7:D11) will disappear.

Merge Cells


Why Merge Cells in Excel?

Merging cells helps to organize information and make the dataset look cleaner. So, when you want a big title that covers several cells, then you need to merge.


Merge Cells vs Combine Cells

In Excel, combining cells means combining the data of multiple cells into one single cell. Merging cells means combining multiple cells into one.

You can see the difference between merged cells and combined cells in the image below.

Merge Cells vs Combine Cells

We use formulas to combine the values of multiple cells. Also, we can combine non-adjacent cells in this way. On the other hand, when we are merging cells, we use the Merge & Center feature, which works only for adjacent cells.


Things You Need to Know Before Merging Cells in Excel

There are some limitations to the Merge & Center tool:

  • When you merge cells, only the content of the top-left cell is preserved, and the data in the other cells is deleted. If you have important information in the cells you’re merging, it may be lost.
  • Merge cells can cause problems when sorting or filtering data. Excel cannot sort data with both merged and unmerged cells. So, try to avoid merging cells in columns when you need to perform data sorting or filtering.
  • In the case of referring to merged cells in the Excel formula, you may not get the correct output. Because the merging cell acts as a single cell, its index is according to the 1st left cell of all the combined cells.
  • You cannot use the Merge and Center feature directly in the Excel table. In that case, you must convert the Table to a range for merging cells.
  • When using Data Validation or Conditional Formatting, it is good practice to avoid merged cells.

How to Merge Cells in Excel: 4 Suitable Examples

We will show you four different examples of merging cells in Excel. For your better understanding, I will use the following dataset. The dataset contains some order information for some products.

Sample dataset


1. Merge Cells Using ‘Merge & Center’ Command

Now, we will use the Merge & Center command to merge cells. There are four individual options under this Merge & Center command. You can use any of them according to the data. Below, you can see different examples of how to use this Merge & Center command.


1.1 Merge Two Cells in Excel

Here, we have the same order IDs in B6 and B7 cells. So, we will merge B6 & B7 cells using the Merge & Center feature in Excel.

  • Select B6 & B7 cells >> from the Home tab >> go to Alignment group >> click on Merge & Center.

Merge Two Cells in Excel

  • You will get a warning from Microsoft Excel saying that merging cells will remove all the values except the upper left value.
  • So, if you are sure that other values are not important, then press OK to that.

Warning from Microsoft Excel about removing other values

Below, you can see the result.

Merged cells

Now, we will use this Merge & Center tool horizontally. Here, we want to merge B6 and C6 cells.

  • To do so, select B6 and C6 cells >> from the Home tab >> click on the Merge & Center tool >> get the merged cells.

Merging two cells horizontally

Notes

You can’t merge non-adjacent cells using this Merge and Center tool. This feature works only for adjacent cells.


1.2 Merge and Centre Cells of Entire Column in Excel

Let’s assume that in column B, range B6:B14 contains the same value as 101. We will merge all these cells and center the contents of these cells.

  • Select the B6 cell >> press CTRL + SHIFT + ↓ (Down-Arrow key) >> get the entire column selected.
  • From the Home tab >> press on Merge & Center.

Merge and Centre Cells of a Column in Excel

  • You will get a warning to remove all the cell values except the B6 cell.
  • After pressing OK, you will get the following output.

Merged entire column


1.3 Merge Multiple Cells Vertically

We have a long text in the B6 cell as a caution, and the following cells in this column are empty. Now, to make this text readable, we will merge B6:B14 cells.

Merge Multiple cells-data

Select B6:B14 cells >> from the Home tab >> go to Alignment group >> click on Merge & Center >> Wrap Text.

Merge Multiple Cells Vertically in Excel

You can see the output below.

Merged multiple cells

Read More: How to Merge Cells in Excel Without Merging


1.4 Merge Multiple Cells Toward a Row

Here, we will merge multiple cells toward a row using the Merge Across option of the Merge & Center feature.

  • Select B6:F6 cells >> from the Home tab >> go to Alignment group >> from the Merge & Center drop-down >> select Merge Across >> get the output.
  • Do the same for B13:F13 cells.

Merged Multiple Cells using Merge Across


Adding Merge & Center Feature to Quick Access Toolbar

You can add this Merge & Center feature to the Quick Access Toolbar.

  • From the Home tab >> go to the drop-down menu of the Merge & Center feature.
  • Righ click on the suitable option. Here, I right-clicked on the Merge Cells option.
  • From Context Menu Bar >> select Add to Quick Access Toolbar.

Adding Merge & Center Feature to Quick Access Toolbar

As a result, you will see the Merge Cells option in the Quick Access Toolbar. Now, you can choose any cells and press on this tool to get merged cells.

Added Merge & Center Feature to Quick Access Toolbar

Read More: How to Move Merged Cells in Excel


2. Merging Cells Using Keyboard Shortcuts

Some keyboard shortcuts are used to merge cells in Excel. The shortcuts given below are for Windows.

  • For merging cells vertically, select the cells >> press ALT+H+M+M.

For your better understanding, I’m attaching the video.

  • To merge the cells into the same row, press ALT+H+M+A.
  • To keep the values in the center, press ALT+H+M+C.
  • To unmerge cells, press ALT+H+M+U.
  • For Mac, press Control+Option+M to merge cells.

3. Merging Cells with VBA Code

While merging a lot of cells using the Merge & Center feature, every time getting the warning from Microsoft Excel is quite annoying. To avoid the warnings, you can use VBA code.

  • From the Developer tab >> go to Visual Basic.

Opening Visual Basic window

Then, you will get the Microsoft Visual Basic window.

  • Now, from the Insert tab >> Module >> write the following code in Module 1.
Sub merging_cells()
Application.DisplayAlerts = False
Selection.Merge
End Sub
  • Save the code.

Merge Cells with Excel VBA

  • To run the code, we will use a Button. So, go back to the worksheet >> from the Developer tab >> go to the Insert option.
  • From Form Controls >> press on Button (Form Control).

Inserting Button Form Control

  • Drag the Button to a suitable place on the worksheet.
  • Then you will get a dialog box named Assign Macro.
  • Select the sub-procedure merging_cells >> press OK.

Assigning macro to that button

Now, select the desired cells (B6:B8) >> press Button 1.

Using button to merge cells

After that, get the result.

output of VBA code

Similarly, you can merge cells by selecting them and clicking this button.

Merge Cells with Excel VBA

Read More: How to Merge Cells in Excel with Data


4. Use of Alignment Feature for Merging Cells

With the Alignment feature, you can merge cells easily.

  • Select cells >> press CTRL+1 >> this will open the Format Cells dialog box.
  • Go to the Alignment section >> under Text control >> check both the Wrap text and Merge cells.
  • Press OK.

Use of Alignment Feature to Merge Cells in Excel


How to Merge Cells in Excel Table

Let’s assume we have an Excel table. There are some repeated IDs. Now, we need to merge these cells. But we can’t merge cells in an Excel table.

So, we need to convert the table into a range and then we will do the merging.

Below, you can see that the Merge & Center feature is grayed out in the case of using an Excel table.

Merge & Center feature grayed out in Excel table

  • Select any cell of the table>> from the Table Design tab >> go to the Tools group.
  • Press Convert to Range.

Converting the table into range

  • Then, you will get a message box.
  • Press Yes to that.

Getting info from Microsoft Excel

  • After converting the range, use the Merge & Center feature to get the merged cells.

Merging cells of converted range

Notes

You can convert this range back into a table by pressing CTRL+T. However, the merged cells will be automatically unmerged then.


How to Move Merged Cells in Excel

Often, we need to move merged cells in Excel. In this case, VBA code can make our task easier.

  • To use VBA code, from Developer code >> go to Visual Basic >> get the Microsoft Visual Basic window.
  • Now, from the Insert tab >> Module >> write the following code in Module 2.
Sub move_merged_cells()
Selection.Cut Range("H6")
End Sub

Move Merge Cells using Excel VBA

Through this code, whatever you select, will be pasted in the H6 cell.

  • Select a merged cell>> the Developer tab >> Macros.
  • This will open the Macro dialog box >> select move_merged_cells >> press Run.
  • Then the merged cells will move to the H6 cell.
Moving Merge Cells in Excel

Click on the GIF to view it in full-size


How to Find Merged Cells Quickly in Excel

  • To find merged cells, press CTRL+F >> this will open the Find and Replace dialog box.
  • Press Format.

Find Merged Cells

  • In the Find Format window >> go to the Alignment section.
  • From Text control >> check Merge Cells >> press OK.

Activating Merge Cells command in Find format

  • Then press Find All in the Find and Replace dialog box.
  • Get all the merged cells of that worksheet.

Finding Merged Cells


Why Can’t I Merge Cells in Excel?

There are some reasons why the Merge & Center feature becomes grayed out.

  • If the cells are part of a table, then most likely the merge cells option won’t work on them. In this case, you first have to convert the table into normal data and then the merge cells option will work.
  • Excel doesn’t allow any users to merge cells of a protected sheet. To unprotect the worksheet, Go to Review >> Protect section >> Unprotect Sheet. After unprotecting the worksheet, you can apply cell merge with ease.
  • Shared worksheets also don’t support cell merge. To Unshare the worksheet, Go to Review >> Protect section >> Unshare Workbook.
  • Then, if a cell is in edit mode then you can’t use the Merge & Center feature. So, press ENTER to close the editing mode of Cell >> get the Merge & Center feature.

How to Merge Cells Without Losing Data

When we use the Merge & Center tool, we lose most of the selected data. Also, the Merge & Center tool doesn’t work for the non-adjacent cells. To solve this problem, we can use Excel functions and formulas.


1. Using Excel Formulas to Merge Two Cells

Below, we want to merge two non-adjacent cells B6 and E6 keeping both of the cell values. We will use a hyphen between two values. To do so, we will use the CONCATENATE function.

  • Go to a blank cell (H6) >> enter the following formula.
=CONCATENATE(B6,"-",E6)
  • Press ENTER >> get the combined values.
  • Now, drag the Fill Handle icon to paste the used formula to the other cells of the column.

use of CONCATENATE function to combine cells

  • There is another function called the CONCAT function. This also joins a list or range. So, you can use the following formula too.
=CONCAT(B6,"-",E6)

use of CONCAT function to combine cells

You can also use the ampersand operator to connect two cells keeping only the values together.

  • Go to H6 cell >> write the formula given below >> press ENTER.
=B6&E6
  • Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Use of Ampersand operator to combine cells


2. Applying TEXTJOIN Function to Combine Multiple Cells

If you want to combine multiple values, then you can use the TEXTJOIN function. This function will consider a delimiter between all the values. Here, we want to combine B6:E6 cells using a comma as the delimiter. So, the formula should be like this:

=TEXTJOIN(",",TRUE,B6:E6)

Applying TEXTJOIN Function to Combine Multiple Cells


3. Merging Cells Within One Column Using Justify Feature

In this case, we want to merge all the cell values of an entire column (B6:B12) into the uppermost cell of that column (B6 cell). To do so, we will use the Justify feature of Excel.

  • Expand the column B enough so that all the values (B6:B12) get space. If the column does not have enough width to fit all these values, then this feature will not work.
  • Then, select B6:B12 cells >> from the Home tab >> go to Editing group >> from Fill >> select Justify.
Combine Cells Within One Column Using Justify Feature

Click on the image to have a detailed view

As a result, you will get all the cell values in the B6 cell.

Combine Texts in one cell

Read More: How to Merge Data from Multiple Workbooks in Excel


An Alternative of Merging Cells in Excel (Inserting Text Box)

Instead of using merge cells, there are some alternative ways of keeping long strings in Excel. You can use the Text Box to keep the merge cells’s value.

Let’s assume we have long text in C16 and E16 cells. Now, we want to use the Text Boxes to keep these cell values.

  • To use Text Box, from Insert tab >> Text >> select Text Box.
  • Drag the Text Box to a suitable place on the sheet.

Inserting Text Box

  • Double-click on the Text Box >> go to Formula Bar.
  • In the Formula Bar, write =$C$16 >> press ENTER.

Use of Text Box as an Alternative to Merge Cells

  • Similarly, insert another Text Box >> active that box >> go to Formula Bar >> write =$E$16 >> press ENTER.

Insert another Text Box to keep long text


How to Unmerge Cells in Excel

If you need to unmerge the merge cells, then you can do that too from the ‘Merge & Center’ option.

  • Select the merge cells (B6:B8) >> from Home tab >> go to Merge & Center >> choose Unmerge Cells.

How to Unmerge Cells in Excel

As a result, the merged cells will unmerge.

Unmerged cells

Notes

When we unmerge the cells, the value of the merged cells takes the place of the uppermost left cell, and other cells remain empty.


Download Practice Workbook

In this article, we have learned how to merge cells in Excel using the Merge & Center feature. We have shown some keyboard shortcuts and the use of VBA code to merge cells. Additionally, we have also learned to merge cells in a table and how to move & find merged cells in Excel. We have also shown alternatives to merge cells in Excel. Then, we used Excel functions and the Justify feature to combine cells.  If you have any queries or suggestions, feel free to ask in the comment section below.


Excel Merge Cells: Knowledge Hub


<< Go Back to Merge in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo