Home > Skills > Data Entry > Merge Cells

Merge Cells or Combine Cells in Excel

How to merge cells and unmerge cells in Excel. Combine cell values, or merge worksheet cells? Tips for merged cells, and merged cell problems.

Merge Cells vs Combine Cells

There is an important difference between merging cells, and combining cell contents, in an Excel worksheet.

In most situations, outside of Excel, merge and combine can be used interchangeably -- they have almost the same definitions in the dictionary.

However, in Microsoft Excel, merge and combine mean different things, so it's important to know the difference between those actions.

This short video shows the difference between merging cells, and combining cell values, and there's more information below the video.

Quick Summary

Here is a quick summary of what each action means:

1. Merge: Join two or more adjacent cells into one larger single cell that spans across multiple rows and/or columns. For example, merge cells A1 and B1, to create one large cell that spans two columns.
   • Only the contents of the top left cell will be kept.
   • Contents of any other merged cells will be deleted.
     • In the screen shot below, only the first name remains in the merged cell
   • 
2. Combine: Join the contents of two or more cells, to display in another cell, by using a formula. For example, join first name from column A with last name from column B, and show the full name in column C.
   • 

How to Merge Cells

In this section, you'll find the steps for merging two or more cells on a worksheet, to create a single, larger cell.

• Start by checking the cells that you plan to merge, so you don't lose any important data.
• After that, follow the steps to select cells, and choose the merge type that you want.

Avoid Losing Data

Cell values could be deleted when merging multiple cells, so check the worksheet cells, before you merge them.

As you check the cells, keep these warnings in mind:

• Only the contents of the top left cell will be kept.
• Contents of any other merged cells will be deleted.

If there is important data in any cells, other than the first cell, at the top left, consider these suggestions:

• Copy the data, and paste it onto a different worksheet, or an empty area, outside of the merge range
• Instead of merging the cells, use a formula to combine the cell values

Merge Warning Message

When you try to merge cells, Excel will show the following merge warning message, if any cells will have their contents deleted.

• Warning message test: Merging cells only keeps the upper-left value and discards other values.

NOTE: You won't see this message if all cells are empty, or if only the top left cell contains a value.

Start the Cell Merge

After you check the cell contents, and you're ready to merge cells in Excel, follow these steps:

• First, select the range of cells that you want to merge
  • Selected cells must be in one contiguous range
  • You can't select cells from different parts of the worksheet
• Next, on the Excel Ribbon, go to the Home tab
• In the Alignment group, find the Merge & Center command
  • Text Label: If the Excel window is wide, you should see the text label
  • Icon: In a narrow window, only the Merge & Center icon might be showing

Merge & Centre

After you locate the Merge Center command, there are two ways to use it, described below:

1. Quick Merge & Center
2. Merge Options

1) Quick Merge & Center

If you want to MERGE the selected cells, AND horizontally center the contents, use this quick merge method:

• Click on the icon for the Merge & Center button
  • (don't click on the drop down arrow)

The selected cells are merged into a single, larger cell, spanning multiple rows and/or columns

In this screen shot, I selected cells A6, A7 and A8, then clicked the Merge & Center icon.

• Values in the second cell (A7), and third cell (A8) were deleted
• Value from cell A6 (the upper-leftmost cell) remained in the cell, vertically centred
• Other formatting from cell A6 was used for the merged cell - yellow fill, bold font, bottom aligned vertically
• 

2) Merge Options

If you want to select one of the other types of merging, follow these steps:

• First, click the drop-down arrow at the right side of the Merge & Center button
  • 
• Next, in the drop-down list, click on one of the merging options, described in the section below:
  1. Merge & Center
  2. Merge Across
  3. Merge Cells

Merge & Center

This merge cells option works exactly the same as the Merge & Center icon.

• Selected cells are merged into a single, larger cell, spanning multiple rows and/or columns
• Values are centred horizontally
• Other formatting from top left cell is used for the merged cell vertically

In the screen shot below,

• First, I used Merge & Center for cells A2 and B2.
• Later, I used Merge & Center for cells C2, C3 and C4

In both merged cells, only the falue from the top left cell remains, and the results are centered horizontally

Merge Across

This command merges the selected cells across columns, but keeps the merged cells in separate rows.

If some cells contain data, you might see the merge warning message multiple times, as Excel merges the selected cells in each row.

For example, in the screen shot below, 9 cells are selected, in three separate rows, and three columns.

After clicking the Merge Across command, the Excel merge warning message appeared 3 times - once for each row in the selection.

• Cells in each row were merged
• Formatting from the top left cell in each row was used in the merged cells

Merge Cells

Selected cells are merged into one large cell, which can span multiple rows and columns.

• Only the value from the top-left cell is kept, and all other values are deleted.
• The merged cell uses the formatting and alignment from the top left cell

Unmerge Cells in Excel

If you’ve done much work in Excel, you’ve probably encountered problems that are the result of merged cells on a worksheet.

Merging cells can seem like a good idea at the time, but can interfere with sorting and filtering, and other things that make an Excel workbook useful.

In the sections below, you'll see how you can unmerge Excel cells:

--1) one at a time

--2) entire worksheet, or

--3) all sheets in an entire workbook.

1) Unmerge Merged Cells

To unmerge specific cells on an Excel worksheet, and change them back into separate cells, follow these steps:

• First, select the cell or cells that you want to unmerge
• Next, on the Ribbon’s Home tab, click the drop-down arrow for Merge & Center
• In the drop-down menu, click on the Unmerge Cells command

2) Unmerge All Cells - Worksheet

If a worksheet has many merged cells, you don't have to unmerge each cell separately. Instead, use this simple method to quickly unmerge all cells on the active sheet.

To unmerge all cells on the active sheet, follow these steps:

• First, be sure to make a backup of the workbook, before you do the unmerge, and store the backup copy somewhere safe.
• Next, go to the worksheet where you want to unmerge all the cells
• On that sheet, at the top left corner, click the Select All button
• Then, on the Ribbon’s Home tab, click the drop down arrow for Merge & Center
• Click Unmerge Cells

3) Unmerge All Cells - Workbook

Did you inherit an Excel workbook that is full of merged cells, on many different workshet? If so, there's a simple way to unmerge all cells on all worksheets.

To get rid of all the merged cells, on all worksheets, in an Excel workbook, follow these steps:

• First, be sure to make a backup of the workbook, before you do the unmerge, and store the backup copy somewhere safe.
• Right-click one of the sheet tabs, and click Select All Sheets
• On the active sheet, click the Select All button, at the top left of the worksheet
• On the Ribbon’s Home tab, click the drop down arrow for Merge & Center
• Click Unmerge Cells
• And finally, right-click one of the sheet tabs, and click Ungroup Sheets

Merging Cells - Benefits, Problems

Merging cells is an Excel feature that allows you to combine two or more adjacent cells into one larger cell.

When you merge cells, here's what will happen to the selected cells:

• Only the content of the upper-left cell will be kept
• Contents of any other selected cells will be deleted

In the sections below, you'll see how to merge and unmerge cells in Excel, as well as the benefits and problems of merging cells.

Benefits of Merging Cells

Merging cells can help you improve the appearance and readability of your spreadsheets.

For example, you can use merged cells to make these layout changes:

• Create a large header that spans across multiple columns or down multiple rows
• Display a large block of text, without affecting the height or width of other cells

Problems Caused by Merged Cells

While merging cells can make your spreadsheets look better, it can also cause problems. For example, merged cells can have these negative effects:

• Prevent you from sorting, filtering, or subtotaling your data
• Row heights do not autofit, to show all the text in merged cells
• Difficult to select, copy, paste, insert, or edit within a merged range
• Create gaps or overlaps in charts or pivot tables that use merged cells as source data

Merged Cell Alternatives

To avoid merged cells, and the problems they can cause, try these alternative techniques in your Excel worksheets.

Centre Headings Without Merging

To create multi-column headings, you can center text over several columns, without merging the heading cells. For example, type the heading "Qtr 01", and center it over the columns for January, February and March.

To do this, you select two or more cells, then use the horizontal alignment setting, Center Across Selection

This short video shows the steps, and there are written steps on the Excel Worksheet Tips page.

Show Long Message in TextBox

Instead of merging cells to show a long text message, link to the cell from a worksheet TextBox.

In the screen shot below, there is a long text string in cell A11, and only part of the message is showing.

• Worksheet textbox is linked to cell A11, to show its full message.
• Linked cell can contain values, or a formula
• Textbox size can be adjusted easily, without affecting any of the row heights, or column widths.
• Textbox text can be formatted, with different fonts, font size, and colours.
• Textbox fill colour, and outline width and colours can be changed easily.

Show Cell Text in Text Box

To show cell text in a worksheet textbox, follow these steps:

• On the Excel Ribbon, go to the Insert tab
• At the right side of the tab, in the Text group, click the drop down arrow
  • The pointer changes to an upside-down T shape
• On the worksheet, drag with the pointer, to create a text box
  • While you are dragging, the pointer changes to a large white plus sign
• Release the mouse button, to see the TextBox
• With the text box still selected, click in the Formula Bar
• In formula bar, type an equal sign
• Next, click on the cell you want to link to, e.g. cell A11
• Press the Enter key, to complete the formula

After the text box has been created, you can adjust it, if needed:

• change the textbox height and width
• choose different colours for fill colour and outline
• change outlline thickness, or choose No Outline
• choose the font, font size, and font colour you prefer

How to Combine Cells

To combine the contents of two or more cells, you can use a formula in another cell.

The technical term for linking things together is concatenate.

• There is an Excel CONCATENATE function, available in all versions.
• In newer versions of Excel, there is a a CONCAT function, and a TEXTJOIN function.
• For simple joins, you can use the & (ampersand) operator

Combine Cell Values - Example

For example, here's how to combine first and last names, with a space character between them:

• Select cell C2, where the full name should be created
• Enter the following formula:
  • =A2 & " " & B2
• Press the Enter key to complete the formula

The formula result in cell C2 shows the first name, followed by a space character, and then the last name.

More Combine Formulas

You can find more concatenation formula examples on the How to Combine Cells in Excel page.

For example, you'll see how to use the TEXT function in your combine formulas, to combine text with formatted numbers and dates.

Video: Combine Cell Contents

Here's one of the videos from the How to Combine Cells in Excel page. The video shows the steps for combining cell contents in Excel, by using formulas.

Combine Cell Values with TEXTJOIN

If you're using Excel 365, or Excel 2019 or later, the new TEXTJOIN formula makes it easy to concatenate all the text in a range of cells. You con't need to select each cell separately - just select the entire range of cells.

In the screen shot shown below, the following formula is in cell D3, and it returns all the days in the list, separated by comma space.

• =TEXTJOIN(", ",TRUE,A2:A8)

In this formula, the following three arguments are used:

1. delimiter (separator) is a comma and space -- the first argument is ", "
2. blank cells will be ignored -- the second argument is TRUE
3. items in cells A2:A8 will be combined -- third argument is A2:A8

Macro: AutoFit Merged Cell Row Height

One common problem with merged cells is that the row height does not adjust automatically, when you double-click on the row button border, or if you use the Autofit command on the Excel Ribbon.

This can result in hidden text on your worksheet, like the order form note in the screen shot below.

If I double-click the line between row buttons 10 and 11, the row height gets smaller, to fit one line of text, instead of getting taller, to AutoFit all 3 lines of text.

To show all of the merged cell's text, I can

• manually adjust the row height, or use the Row Height command on the Excel Ribbon.
• OR, use a macro like the one shown below, to adjust the row height where there are merged cells.

Merged Cell AutoFit Macro Code

The code below was posted by Smallman, in response to comments on one of my Contextures Blog posts. My original macro for merged cell row autofit only worked when there was one merged cell in the same row. Smallman's code looks at all cells in a given row and calculates which cell has the ‘most’ text. It uses that cell to set the row's height

• NOTE: Like other macros that change the worksheet, this code will wipe out the Undo stack, so you won’t be able to undo any steps you’ve previously taken. If other people will be using the code, let them know about this!

Copy this code into a regular code module in your Excel file. Then, to run the code, you can assign it to a worksheet button, or go to the View tab on the Excel Ribbon, and use the Macros command.

NOTE: This macro checks the cells in the current region of cell A1. You can adjust that range to suit your worksheet's data range.

Sub MergedAreaRowAutofit()
'code posted by Smallman
Dim j As Long
Dim n As Long
Dim i As Long
Dim MW As Double 'merge width
Dim RH As Double 'row height
Dim MaxRH As Double
Dim rngMArea As Range
Dim rng As Range

Const SpareCol  As Long = 26
Set rng = Range("A1").CurrentRegion

With rng
  For j = 1 To .Rows.Count
  'if the row is not hidden
    If Not .Parent.Rows(.Cells(j, 1).Row) _
    .Hidden Then
    'if the cells have data
     If Application.WorksheetFunction _
      .CountA(.Rows(j)) Then
      MaxRH = 0
      For n = .Columns.Count To 1 Step -1
      If Len(.Cells(j, n).Value) Then
      'mergecells
      If .Cells(j, n).MergeCells Then
      Set rngMArea = _
      .Cells(j, n).MergeArea
      With rngMArea
      MW = 0
      If .WrapText Then
      'get the total width
      For i = 1 To .Cells.Count
       MW = MW + _
       .Columns(i).ColumnWidth
      Next
      MW = MW + .Cells.Count * 0.66
      'use the spare column
      'and put the value,
      'make autofit,
      'get the row height
      With .Parent.Cells(.Row, SpareCol)
       .Value = rngMArea.Value
       .ColumnWidth = MW
       .WrapText = True
       .EntireRow.AutoFit
       RH = .RowHeight
       MaxRH = Application.Max(RH, MaxRH)
       .Value = vbNullString
       .WrapText = False
       .ColumnWidth = 8.43
       End With
       .RowHeight = MaxRH
       End If
      End With
      ElseIf .Cells(j, n).WrapText Then
       RH = .Cells(j, n).RowHeight
       .Cells(j, n).EntireRow.AutoFit
       If .Cells(j, n).RowHeight < RH Then _
        .Cells(j, n).RowHeight = RH
       End If
      End If
      Next
     End If
    End If
  Next
  .Parent.Parent _
   .Worksheets(.Parent.Name).UsedRange
End With
End Sub

Get the Sample Files

Combine Basic: The workbook has the Combine text examples with the & operator, and number formatting. The zipped file is in xlsx format, and does not contain macros.

Merge Examples: To see merged cell examples, and the Merged Cell Autofit code from this page, download the Merged Cell Examples and Macro workbook. The zipped file is in xlsm format, and contains the autofit macro, and a button to run that macro.

AutoFit Macro: To see Smallman's code, go to the AutoFit Merged Cells with VBA page on Smallman’s site, to download his sample file.

Avoid Merging: Click here to get the Center Without Merging sample file. It is zipped, and in xlsx format, and does not contain any macros.

More Tutorials

Combine Cell Values

TEXTJOIN Function

TEXTSPLIT Function