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.
Here is a quick summary of what each action means:
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.
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:
If there is important data in any cells, other than the first cell, at the top left, consider these suggestions:
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.
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:
Merge & Centre
After you locate the Merge Center command, there are two ways to use it, described below:
1) Quick Merge & Center
If you want to MERGE the selected cells, AND horizontally center the contents, use this quick merge method:
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.
2) Merge Options
If you want to select one of the other types of merging, follow these steps:
Merge & Center
This merge cells option works exactly the same as the Merge & Center icon.
In the screen shot below,
In both merged cells, only the falue from the top left cell remains, and the results are centered horizontally
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.
Selected cells are merged into one large cell, which can span multiple rows and columns.
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:
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:
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:
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:
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:
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:
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.
Show Cell Text in Text Box
To show cell text in a worksheet textbox, follow these steps:
After the text box has been created, you can adjust it, if needed:
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.
Combine Cell Values - Example
For example, here's how to combine first and last names, with a space character between them:
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.
In this formula, the following three arguments are used:
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
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
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
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.
Last updated: February 17, 2024 2:17 PM