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

Get FREE Advanced Excel Exercises with Solutions!

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.

Merge in Excel

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

Note: While making this tutorial, we used Microsoft 365. However, you can use any version of Excel to get the Merge & Center feature.

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.

Applying the Merge & Center command

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

After applying the Merge & Center Feature

Note: Press the keyboard shortcut Alt+H+M+C serially to apply Merge & Center for Windows users.

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.

Applying the Merge Across feature

Note: Press Alt+H+M+A serially to apply Merge Across for Windows users.
  • 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.

Note: The keyboard shortcut to apply merge cells for Windows users is to press Alt+H+M+M serially.

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.


  • Select cell E7.
  • Paste the following formula and press the Enter button.
=CONCAT(C7," ",D7)
  • 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.

Applying CONCAT function

Note: You can only apply this CONCAT function using Microsoft 365, Microsoft Excel 2016, or the above versions.


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.


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

Applying TEXTJOIN function

Note: This TEXTJOIN function is available on Windows if you have Office 2019 or a Microsoft 365 subscription.

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.

Merging multiple rows

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

Applying Justify feature

Note: Keep proper space on the uppermost cell so that all the values can fit properly. Otherwise, the Justify feature won’t work properly.

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.

Dataset of two tables

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.

Applying XLOOKUP function

Note: Only Excel for Microsoft 365, Excel 2021, and Excel for the Web support the XLOOKUP function.

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.

Selecting two datasheets


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

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

Merged Sheet

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.


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

merge workbooks in Excel

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

Merging with other workbooks

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

merged workbooks 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.


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

Going to the Find feature

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

Clicking on the Format command

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

Checked on the Merge cells box

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

Finding merged cells

Note: You can directly go to the Find and Replace window by pressing the keyboard shortcut Ctrl+F.

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.


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

Going to Alignment Settings

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

Selecting centre across selection

The selected cells have been merged.

Alternatives way to Merge Cells

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.

unmerging cells in Excel

The merged cells are unmerged now.

Unmerged cells

Note: You can apply the keyboard shortcut to unmerge cells by pressing ALT+H+M+U. The contents of all the cells will show in the top-left cell when they have been unmerged. The other cells will remain empty.

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

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.


Mizbahul Abedin
Mizbahul Abedin

Md Mizbahul Abedin, BSc, Textile Engineering and Management, Bangladesh University of Textiles, has been working with the ExcelDemy project for 11 months. Currently working as an Excel and VBA Content Developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He has published almost 20 articles in ExcelDemy. He has passions for learning new things about Microsoft Office Suite and Data analysis. Besides, he also likes to travel, photography, international politics, and read... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF