How to Merge in Excel (Merge, Find, Unmerge)

Merging in Excel means combining multiple cells, columns, rows, tables, sheets, or workbooks into one.  In this free Excel tutorial, you will learn how to merge in Excel in different ways.

Here, in the following GIF, we have a dataset, and we want to give it a heading. To do this, we have merged and centered the cells to represent them in a better way. We have used Excel’s Merge & Center command under the Alignment group to merge and center the cells.

In this blog post, you will learn how to

• merge cells
• merge columns
• merge rows
• merge multiple sheets
• merge multiple workbooks
• find merged cells
• unmerge cells

Besides, we have shown the alternatives to merging cells in Excel.

⏷Why Merge in Excel?
⏷Merge Cells Using Merge & Center Feature
⏷Merge Values from Multiple Columns
 ⏵Using CONCAT Function
 ⏵Using TEXTJOIN Function
⏷Merge Rows Using Justify Feature 
⏷Merge Tables Using XLOOKUP Function 
⏷Merge Sheets Using VBA
⏷Merge Multiple Workbooks into One
⏷Find Merge Cells
⏷Alternative to Merge Cells
⏷Unmerge Cells

Why Merge in Excel?

Mostly, people merge cells in Excel to create headers or labels. To make the header larger and centered, we merge the cells. Besides, merging cells can help align and organize data. Merged cells act like one single cell. Overall, merging simplifies the presentation of data in Excel. But it can affect sorting, filtering, and some mathematical operations.

1. How to Merge Cells in Excel Using Merge & Center Feature

In this method, we will show how to merge cells by using Excel’s built-in Merge & Center feature. Merge & Center command is the easiest way to merge and center cells in Excel. Follow the instructions below to apply the method.

• Select the cells from B5 to G5.
• Go to the Home tab. From the Alignment group, click on the drop-down menu of the Merge & Center. Click on the Merge & Center command.

The selected cells will be merged into a single cell with the text centered, as seen in the picture below.

The Merge Across command merges cells only within each row of the selected range. If you select multiple rows and use the “Merge Across” command, each row’s cells will be merged independently.

Here, we want to merge across the rows of the First Name and Last Name columns. We want to merge the cells of first and last names in each row and keep the first name only.

To do this, click the Merge Across command from the Merge & Center dropdown.

• You will get a warning from Microsoft Excel saying that “Merging cells will remove all the values except the upper-left value and discard other values.”
• If you believe that the other values are unimportant, click OK.
• You should press the OK button repeatedly according to the size of the selected cells.

Similarly, you can apply the Merge Cells command to the selected cells like the previous two methods. The difference is that merging cells will merge the cells, keeping the upper left value only, and won’t center the data.

2. How to Merge Values from Multiple Columns in Excel

The previously mentioned methods will potentially lose data while merging cells. Sometimes, we need to combine the values of multiple columns while working with a large dataset. Here, we are going to show you how to merge values from columns in Excel. No data will be lost while applying this method. We will do it in two different ways:

– Using CONCAT Function
– Using the TEXTJOIN function

I. CONCAT Function

In this dataset, we have first and last names in two different columns. We want to combine these two values from these columns into a new column named Full Name. We can easily use the CONCAT function to merge the values of two columns in Excel.

Steps:

• Select cell E7.
• Paste the following formula and press the Enter button.

• Then, drag the Fill Handle icon down to paste the used formula into the other cells of the column.

After applying the formula, you can see that the values of cells D7 and C7 are all present in cell E7.

II. TEXTJOIN Function

We will use the TEXTJOIN function while combining the values of the columns.

In the following dataset, we have First Name, Middle Name, and Last Name in 3 different columns. We will merge the values of these three columns and create the full name.

Steps:

• Select the F7 cell and write the formula below in the cell:

• Then, press the Enter button.

As a result, you will observe that the data from the three cells is merged. Use the Fill Handle tool to apply the formula to the other cells.

3. How to Merge Rows by Using Justify Feature

Like merging the columns, we can also merge rows. Now, we will show you how to merge rows. It’s a very simple and handy process. We will use the Justify feature of Excel to merge multiple rows without losing data.

Here, we have some Fruit’s names in the rows of the D column. We will merge the data from these rows into a single cell now.

Now, follow the steps below.

• Select cells E6 to E9.
• Go to the Home tab >> from the Editing group >> click the drop-down of the Fill option >> select the Justify option.

You can see the values of all the rows combined into a single row.

4. How to Merge Two Tables in Excel Using XLOOKUP Function

Sometimes, you need to merge data from multiple tables into one table. In this method, you will learn how to merge two tables. We will do this by using the XLOOKUP function. But this method is applicable when both tables have similar fields.

We have two tables that show the marks obtained by six students in their math and physics exams. These two tables will be combined so that the math and physics marks can be displayed on the same table.

Follow the steps below to merge the tables:

• Copy any one table and paste it into your preferred place on the worksheet. We will copy the second one that contains the physics marks. Now, we will bring the math marks to this table from the other table.
• Now, select cell E13 and enter the following formula:

• Next, drag down the Fill handle to cell E19.

In the following image, you can see that the two tables have been merged.

5. How to Merge Multiple Sheets in Excel Using VBA

In this section, we will show you how to merge two sheets into a separate sheet in Excel. We have two worksheets named Physics and Math. We will apply a VBA code to merge these two worksheets. The two sheets will combine to form a new worksheet, Merged Sheet.

Steps:

• First, press Alt+F11, which will open a new window named Microsoft Visual Basic for Applications.
• Go to Insert and click on Module.

• A module window will appear. Now paste the following code:

Sub Merge_Multiple_Sheets()
Dim Worksheet() As String
ReDim Worksheet(Sheets.Count)
For i = 0 To Sheets.Count - 1
    Worksheet(i) = Sheets(i + 1).Name
Next i
Sheets.Add.Name = "Merged Sheet"
Dim RowIndex As Integer
RowIndex = Worksheets(1).UsedRange.Cells(1, 1).Row
Dim ColumnIndex As Integer
ColumnIndex = 0
For i = 0 To Sheets.Count - 2
    Set Rng = Worksheets(Worksheet(i)).UsedRange
Rng.Copy
    Worksheets("Merged Sheet").Cells(RowIndex, ColumnIndex + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    ColumnIndex = ColumnIndex + Rng.Columns.Count + 1
Next i
Application.CutCopyMode = False
End Sub
			

• Click on the play button or press F5 to run the code.
• After running the code, you will see a new sheet has been created named Merged Sheet. The previous two sheets have been merged.

6. How to Merge Multiple Workbooks to One Workbook in Excel

Sometimes, we have to work across multiple workbooks simultaneously. We can complete the task more conveniently by combining the workbooks. So, in this section, you will learn how to combine multiple workbooks into one workbook in Excel.

In the image below, you can see that we have selected two worksheets under a workbook named Workbook 1. We will merge these two sheets with another workbook.

Steps:

• First, select the two sheets: Physics (2) and Math (2).
• Right-click on your mouse and select Move or Copy.

• A pop-up window named Move or Copy will appear.
• Go To book: drop-down box >> Select the workbook where you want to move or copy.
• Afterward, go to Before sheet: options >> Select the name of the sheet before which you want to paste the workbooks. position of the copied or moved worksheets in the new workbook.
• Then, mark a tick on the Create a copy check box and press the OK button.

• Those two sheets have been copied to the new workbook named Merge in Excel.

7.  How to Find Merged Cells in Excel Using Find and Replace Feature

We have discussed everything about how to merge in Excel. However, there are situations where you might need to find the merged cell.

You will learn how to find the merged cell in Excel.

Steps:

• Go to the Home tab >> the Editing group >> select the Find & Select drop-down >> click on the Find… option.

• A pop-up window named Find and Replace will appear.
• Select the Format drop-down box.

• The Alignment tab of the Find Format pop-up box will appear.
• Check the box for Merge cells under Text Control and press OK.

• Find and Replace, a pop-up box will appear. From there, click on Find All.
• After that, you will see a list of all merged cells in your worksheet.

8. Alternative to Merge Cells in Excel: Center Across Selection

Merged cells can make your Excel spreadsheet content look more effective and meaningful. But there are some side effects that you may need to be made aware of. You can’t use the Fill Handle tool, can’t sort a column with merged cells, etc.

So, we will show you an alternative method here.

Steps:

• Select the cells B5:G5.
• Go to the Home tab and click on the Alignment Settings button.

• The Format Cells window will appear.
• Now, from the horizontal drop-down box, select Center Across Selection.
• Then select the OK button.

The selected cells have been merged.

9. How to Unmerge Cells in Excel

We may need to unmerge the merged cells in many cases. To do this, follow the steps:

• First, select the merged cell B5.
• Go to the Home tab >> Click on Merge & Center >> Select Unmerge Cells.

The merged cells are unmerged now.

Download Practice Workbook

Download the practice workbooks to exercise while you are reading this article.

In this article, you learned how to merge in Excel. We have shown you how to merge cells, columns, rows, multiple sheets, and multiple workbooks. In this tutorial, we have also discussed finding the merged cells, an alternative to merging cells and unmerging the cells.

We hope this article has helped you. If you have any queries or suggestions, feel free to ask in the comment section below.

Merge in Excel: Knowledge Hub

<< Go Back to Learn Excel