How to Merge, Find, and Unmerge in Excel: 9 Methods

Method 1 – How to Merge Cells in Excel Using Merge & Center Feature

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

Merge across the rows of the First Name and Last Name columns. Merge the cells of first and last names in each row and keep the first name only.

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.
  • 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.
  • Press the OK button repeatedly according to the size of the selected cells.

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.

Method 2 – How to Merge Values from Multiple Columns in Excel

The previously mentioned methods will potentially lose data while merging cells. We need to combine the values of multiple columns while working with a large dataset. We will 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. Combine the values from these two columns into a new column named Full Name. 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.
=CONCAT(C7," ",D7)
  • 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.

II. TEXTJOIN Function

Use the TEXTJOIN function while combining the values of the columns.

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:
=TEXTJOIN(" ",TRUE,C7:E7)
  • Press the Enter button.

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.

Method 3 – How to Merge Rows by Using Justify Feature

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

We have some Fruit’s names in the rows of the D column. Merge the data from these rows into a single cell 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.

Method 4 – How to Merge Two Tables in Excel Using XLOOKUP Function

Merge data from multiple tables into one table. 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 to display the math and physics marks 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. Copy the second one that contains the physics marks. Add the math marks from the other table to this table.
  • Select cell E13 and enter the following formula:
=XLOOKUP(B5,B5:B11,D5:D11)
  • Drag down the Fill handle to cell E19.

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.

Method 5 – How to Merge Multiple Sheets in Excel Using VBA

Steps:

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

Merged Sheet


Method 6 – How to Merge Multiple Workbooks to One Workbook in Excel

Steps:

  • 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.
  • 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.
  • 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 Merge in Excel‘s new workbook.

merged workbooks in Excel


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

Steps:

  • 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. Click on Find All.
  • 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.

Method 8 – Alternative to Merge Cells in Excel: Center Across Selection

Steps:

  • 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.
  • From the horizontal drop-down box, select Center Across Selection.
  • Select the OK button.

Selecting centre across selection

The selected cells have been merged.

Alternatives way to Merge Cells

Method 9 – How to Unmerge Cells in Excel

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


Merge in Excel: Knowledge Hub


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

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

 

 

ExcelDemy
Logo