Merge cells in Excel

Merge and unmerge cells in Excel

In Excel, you can merge cells using the Ribbon or the Format Cells dialog box . You can also access merge commands by right-clicking or using keyboard shortcuts. Typically, when users want to merge cells, they are trying to put longer titles in one cell (such as January Actual Sales). You can merge cells horizontally between columns or vertically between rows.

If you want to combine data from cells in one worksheet (such as first and last names) in another cell, you can use the CONCATENATE function, CONCAT function, CONCATENATE operator, TEXTJOIN function, or Flash Fill.

Cell merging options are available in the Alignment group on the Home tab in the Ribbon :

Merge cells in Excel Picture 1

Merging cells will delete data

If you merge cells with text, only the text in the top left cell will be used, and the text in the cells to the right (and below) will be deleted. If there is data to the right or below in the cells you are merging, a dialog box appears warning that Excel will keep the upper left values ​​and delete the other data.

Considerations when merging cells

Merged cells cause problems in lists or data sets because Excel will have difficulty associating the data with the merged headers or the merged cells in the data with the correct headers . If you have longer headings in a list or data set, it's best to create shorter headings in single cells above the linked data. You won't be able to sort or filter a range that has merged cells.

Understand merge options in Excel

Merge options in Excel include:

1. Merge & Center – this will merge the selected cells into one cell and center the data from the top left cell across the selected cells (if the cell is to the right or below the top left cell in the selection contains data, it will be deleted).
2. Merge Across – this will merge the selected cells into one cell from the leftmost cell through the selected cells (if the cell to the right of the leftmost cell in the selection contains data, it will be deleted) . It does not automatically center the data. Merge Across only works for selections on columns, not rows.
3. Merge Cells – this will merge the selected cells into one cell from the top left cell through the selected cells (if the cell to the right or below the top left cell in the selection contains data, then that cell will be deleted). It does not automatically center the data.
4. Unmerge Cells – this will unmerge an already merged cell.
5. Center Across Selection (in the Format Cells dialog box) – this will center the data in the selected cells but will not merge the cells into one cell.

In the following example, cells A1 to D1 have been merged using Merge & Center :

Merge cells in Excel Picture 2

Merge cells using the Ribbon

To merge cells using the Ribbon in Excel:

1. Select the cells you want to merge by dragging across the cells or clicking the first cell and Shift-clicking the last cell. The cells must be adjacent to each other.
2. Click the Home tab -> Ribbon .
3. In the Alignment group , click the arrow next to Merge & Center . A drop-down menu appears.
4. Select the merge option you want. The data in the top left or top cell will be merged across the selected cells. Data in other cells will be deleted.

Options for merging cells and unmerging cells appear in the Ribbon when you click the arrow next to Merge & Center:

Merge cells in Excel Picture 3

Merge cells using keyboard shortcuts

If you want to access Ribbon commands in Excel with your keyboard, you can press Alt to display key tips and then press the displayed key(s).

To merge cells using keyboard shortcuts (key tip):

1. Select the cells you want to merge by dragging across the cells or clicking the first cell and Shift-clicking the last cell. The cells must be adjacent to each other.
2. Press Alt > H > M (this is a sequential shortcut so press Alt then H then M). A drop-down menu with merge options appears in the Ribbon.
3. Tap the character displayed for the command you want. The data in the top left or top cell will be merged across the selected cells. Data in other cells will be deleted.

Here are the keyboard shortcuts or key tips for merging and unmerging cells:

1. Merge & Center – press Alt > H > M > C.
2. Merge Across – press Alt + H + M + A.
3. Merge Cells – press Alt > H > M > M.
4. Unmerge Cells – press Alt > H > M > U.

The main tips that appear in the Ribbon when you press Alt > H > M are as follows to display merge options:

Merge cells in Excel Picture 4

Unmerge cells

You can unmerge a cell that has already been merged.

To unmerge a cell using the Ribbon:

1. Select the merged cell.
2. Click the Home tab in the Ribbon .
3. In the Alignment group , click the arrow next to Merge & Center . A drop-down menu appears.
4. Select Unmerge Cells .

You can also use the key trick shortcut method to unmerge a cell.

Use cross-region selection as an alternative to merging cells

You can also use the Format Cells dialog box in Excel to access the Center Cross Selection option. In this case, you are actually applying formatting and the cells are not merged. You can display the Format Cells dialog box easily by right-clicking or by pressing Ctrl + 1 (at the top of the keyboard).

To apply selection between regions using the Format cells dialog box:

1. Select the cells whose data you want to center (in the leftmost cell).
2. Press Ctrl + 1 or right-click the selected cells and select Format cells . A dialog box appears.
3. Click the Alignment tab .
4. From the Horizontal menu , choose Center Across Selection .
5. Click OK .

Choose between cells that appear in the Format Cells dialog box:

Merge cells in Excel Picture 5

Between options is actually the best choice for merged titles.

Isabella Humphrey

Update 01 October 2023