Merge and unmerge cells

You can't split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it. For example, you want to split cell A2 into three cells that will appear, side-by-side, under cell A1 (you want to use cell A1 as a heading). It is not possible to split cell A2, but you can achieve a similar effect by merging cells A1, B1, and C1 into one, single cell. You then enter your data in cells A2, B2, and C2. These three cells appear as if they are split under one larger cell (A1) that acts as a heading.

When you merge two or more adjacent horizontal or vertical cells, the cells become one larger cell that is displayed across multiple columns or rows. In the following example, using the Merge & Center command centers the text in the merged cell.

text in a merged cell

Important:  When you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

After merging cells, you can split a merged cell into separate cells again. If you don't remember where you have merged cells, you can use the Find command to quickly locate merged cells.

Note:  You cannot split an unmerged cell. If you are looking for information about how to split the contents of an unmerged cell across multiple cells, see Distribute the contents of a cell into adjacent columns.

Click on one of the tabs below for instructions specific to your Excel version (topics for Excel for Windows are listed first, then Mac).

Merge cells

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns. For example, here cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information in rows 2 through 7.

Merge cells above other cells

  1. Select two or more adjacent cells you want to merge.

    Important: Make sure the data you want to end up in the merged cell is in the upper-left cell. All data in the other merged cells will be deleted. To keep any data from the other cells, copy it to another spot in the worksheet before you merge.

  2. Click Home > Merge & Center.

    Merge and Center button

    If Merge and Center is dimmed, make sure you’re not editing a cell and the cells you want to merge aren’t inside a table.

    Tip:  To merge cells without centering, click the arrow next to Merge & Center, and then click Merge Across or Merge Cells.

    If you change your mind, you can always split the cells you merged.

Split merged cells

To unmerge cells immediately after merging them, press Ctrl +Z. Otherwise, click Merge and Center to split merged cells.

  1. Select the merged cell you want to unmerge.

Tip:  Use the Find command to find all of the merged cells in your worksheet.

  1. Click Home > Merge and Center.

Merge and Center button

Tip:  You can also click the arrow next to Merge and Center, and then click Unmerge Cells.

Whatever is in the merged cell moves to the upper-left cell when the merged cell separates.

Before and after unmerging cells

Top of Page

Merge adjacent cells

  1. Select two or more adjacent cells that you want to merge.

    Note: Make sure that the data that you want to display in the merged cell is contained in the upper-left cell of the selected range. Only the data in the upper-left cell will remain in the merged cell. Data in all the other cells of the selected range will be deleted. Copy any other data you need to another location on the worksheet before merging.

  2. On the Home tab, in the Alignment group, click Merge and Center.

    Merge and Center button in the Alignment group

    The cells will be merged in a row or column, and the cell contents will be centered in the merged cell. To merge cells without centering, click the arrow next to Merge and Center, and then click Merge Across or Merge Cells.

    Note:  If the Merge and Center button is unavailable, the selected cells might be in Edit mode, or the cells might be inside an Excel table. To cancel Edit mode, press ENTER. You cannot merge cells that are inside an Excel table.

  3. To change the text alignment in the merged cell, select the cell, and then click any of the alignment buttons in the Alignment group on the Home tab.

    Note: 

  4. Formula references in other cells are adjusted automatically to use the cell reference of the merged cell.

  5. Because sorting requires that all cells to be sorted are using the same size, you cannot sort a range that contains a combination of merged and unmerged cells.

Split a merged cell

To split a merged cell, you must select it first.

  1. Select the merged cell that you want to unmerge.

    When you select a merged cell, the Merge and Center button Button image also appears selected in the Alignment group on the Home tab.

    Merge and Center button in the Alignment group

  2. To split the merged cell, click Merge and Center Button image , or click the arrow next to Merge and Center, and then click Unmerge Cells.

    Tip:  Immediately after merging cells, you can also unmerge them by clicking Undo on the Quick Access Toolbar, or by pressing CTRL+Z.

    The contents of the merged cell will appear in the upper-left cell of the range of split cells.

Find merged cells

  1. On the Home tab, in the Editing group, click Find & Select.

    Excel Ribbon Image

  2. Click Find.

  3. On the Find tab, click Options, and then click Format.

    Note:  If you don't see the Format button, click Options.

  4. On the Alignment tab, under Text control, select the Merge cells check box, and then click OK.

  5. Do one of the following:

    • To find the next occurrence of a merged cell, click Find Next.

      Excel selects the next merged cell on the worksheet.

    • To find all merged cells, click Find All.

      Excel displays a list of all merged cells in the bottom section of the Find and Replace dialog box. When you select a merged cell in this list, Excel selects that merged cell on the worksheet.

Top of Page

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns.

For example, here cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information in rows 2 through 7.

Merge cells above other cells

Merge cells

  1. Select two or more adjacent cells you want to merge.

    Important: Make sure the data you want to end up in the merged cell is in the upper-left cell. All data in the other merged cells will be deleted. To keep any data from the other cells, copy it to another spot in the worksheet before you merge.

  2. On the Home tab, select Merge & Center.

    On the Home tab, select Merge & Center

Tips: 

  • If Merge & Center is dimmed, make sure you’re not editing a cell and the cells you want to merge aren’t formatted as an Excel table. Cells formatted as a table usually have alternating shaded rows and filters on the column headings.

  • To merge cells without centering, click the arrow next to Merge and Center, and then click Merge Across or Merge Cells.

  • If you change your mind, you can always split the cells you merged.

Split text into different cells

You can take the text in one or more cells, and spread it out across multiple cells. This is the opposite of concatenate, where you can combine text from two or more cells into one cell. For example, if you have a column of full names, you can split that column into separate first name and last name columns, like the following:

Before and after of text split into different columns

Go to Data > Text to Columns, and the wizard will walk you through the process. Here’s a full breakdown of how it works:

  1. Select the cell or column that contains the text you want to split.

    Note:  Select as many rows as you want, but no more than one column. Make sure there’s enough empty columns to the right so nothing over there gets overwritten. If you don’t have enough empty columns, add them.

  2. Click Data >Text to Columns.

    Click the Data tab, and then click Text to Columns

  3. This starts the Convert Text to Columns Wizard. Click Delimited > Next.

  4. Check Space, and clear the rest of the boxes, or check Comma and Space if that is how your text is split (Mayer, Linda, with a comma and space between the names). You can see a preview of your data in the Data preview window.

    Step 2 in the wizard, Under Delimeters, pick how your data is split; Under Preview, you can see a preview of your data

  5. Click Next.

  6. In this step, you pick the format for your new columns, or you can let Excel do it for you. If you want to pick your own format, select the format you want, such as Text, click the second column of data in the Data preview window, and click the same format again. Repeat for all the columns in the preview window.

    Step 3 in the wizard, Text option selected

  7. Click the Collapse Dialog Box button image button to the right of the Destination box to collapse the dialog box.

  8. Select the cells in your workbook where you want to paste your split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.

    Select the cells where you want to paste your split cells

  9. Click the Expand button button to expand the dialog box, and then click Finish.

    Click the Expand button

Top of Page

Merging cells is useful to reformat a worksheet. You can merge two or more adjacent cells into one cell and display just the contents of the leftmost or uppermost cell from the original cell range in the merged cell. You can also combine the content of several cells and display the combined content in one cell.

Example of text in a merged cell

Example of text in a merged cell

After you merge cells in a range, you may no longer be able to sort the range unless you unmerge the cells.

Merge cells

Important: Only the data in the upper-left cell from a range of selected cells remains in the merged cell. Excel deletes data in the other cells of the selected range.

  1. If the data that you want to display in the merged cell is not the data in the upper-left cell from the range that you want to merge, copy the data that you want to display to the upper-left cell.

  2. Select the range of cells that you want to merge.

    Note: The cells that you select must be adjacent.

  3. On the Home tab, under Alignment, click Merge.

Split merged cells

You can split only a merged cell. There are other methods to divide the content of a cell or range of cells across other cells.

  1. Select the merged cell.

  2. On the Home tab, under Alignment, point to Merge, and click Unmerge Cells.

    The content of the merged cell will appear in the upper-left cell in the range of split cells.

Combine the text of multiple cells into one cell

To combine text from multiple cells into one cell, use the join (&) operator.

  1. On a blank sheet, type a list of first names in column A, and then type a corresponding list of last names in the adjacent cells in column B.

    For example, in cell A1, type Jeff, and then in cell B1, type Price.

  2. In cell C1, type =A1&" "&B1.

    The formula combines the names in cell A1 and cell B1 and separates them by using a space (Jeff Price).

  3. In cell D1, type =B1&", "&A1.

    The formula combines the names in cell A1 and cell B1, putting the last name first and separating them by using a comma followed by a space (Price, Jeff).

    Notes: 

    • You can use quotation marks (" ") to include any literal text — that is, text that does not change — in the result.

    • You can also use the CONCATENATE function to combine text from multiple cells into one cell. To learn more about this function, see CONCATENATE function.

Divide the content of a cell or range of cells across other cells

Use this technique to separate first names from last names, or separate state names from addresses.

Divide the content of cells

Note: This procedure is only for dividing the content of unmerged cells and distributing the divided content across other cells. It does not apply to splitting merged cells.

  1. Select the cell, the range of cells, or the column of cells that contains the text values that you want to divide across other cells.

    Make sure that you have a blank cell to the right of the selected cells.

    Note: A range of cells can be any number of rows, but no more than one column.

  2. On the Data tab, under Tools, click Text to Columns.

    Data tab, Tools group

  3. Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

    The first step in the Convert Text to Columns Wizard determines whether the data is delimited, that is, separated by commas, tabs, or other characters. For example, if your data is first and last names (as in the previous image), the Convert Text to Columns Wizard recognizes the data as delimited (instead of formatted as a fixed width). In the second step of the wizard you select the kind of character that separates the data. In the example image, the type of character is "space." The third step gives you a preview of how the data will appear after it is divided into columns.

    Important: Excel overwrites data to the right of the selected column unless there are one or more blank columns to the right of the selected column.

Top of Page

Merging combines two or more cells to create a single, larger cell. This is a great way to create a label that spans several columns. To merge cells, follow these steps:

  1. Drag the selection handle Selection handle to select the cells that you want to merge. For example, drag the handle from cell A1 to cell C1.

  2. On your iPad, tap Merge.

    Unmerging cells

    On your iPhone, tap the Edit icon Tap to see the ribbon , tap Home if it isn't already selected, and then tap Merge & Center.

    Merge and center command

Important: Only the data in the upper-left cell from a range of selected cells remains in the merged cell. Excel deletes data in the other cells of the selected range.

If Merge or Merge & Center is not available on the Home tab, make sure that the cells you want to merge aren’t inside a table.

If you change your mind, you can always unmerge the cells.

  1. Select a merged cell.

  2. On the Home tab, tap Merge or Merge & Center again.

If Merge or Merge & Center is not available on the Home tab, then the selected cell might not be a merged cell.

Top of Page

Did you know?

If you don't have an Office 365 subscription or the latest Office version, you can try it now:

Try Office 365 or the latest version of Excel

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×