How to unmerge cells in Excel

This concise guide demonstrates how to quickly unmerge cells in Microsoft Excel, locate all merged cells within a worksheet, and restore original values to individual cells after unmerging.

Merging cells, while sometimes convenient for alignment or comparison, often hinders basic worksheet operations like sorting and filtering. This tutorial offers several efficient solutions.

Unmerging Cells

Unmerging is straightforward:

1. Select the cells to unmerge.
2. On the Home tab, in the Alignment group, click the Merge & Center button's dropdown arrow and choose Unmerge Cells.

This places the merged cell's content in the top-left cell; others become empty.

Unmerging All Cells in a Worksheet

To unmerge all merged cells on a sheet:

1. Select the entire worksheet (Ctrl A or click the top-left triangle).
2. If the Merge & Center button is highlighted, click it to unmerge all merged cells. If not highlighted, no merged cells exist.

Unmerging and Restoring Original Values

To unmerge and fill each cell with the original value:

1. Select the table (or relevant columns), click Merge & Center to unmerge. Only the top-left cells retain data.
2. Reselect the table. Go to Home > Editing > Find & Select > Go To Special…

3. Check Blanks, click OK.
4. Type = and press the Up Arrow key. This creates a formula copying the value above.
5. Press Ctrl Enter to apply the formula to all selected blank cells.

Finally, for values only, use Paste Special > Values to replace formulas with their results.

Splitting Merged Cell Contents

For merged cells containing multiple data points, consider these methods: Text to Columns, Flash Fill, formulas, or the Split Text tool, depending on your data's structure. After splitting, unmerge or delete the original merged cells.

Finding Merged Cells

To locate merged cells:

1. Open the Find dialog (Ctrl F or Home > Find & Select > Find).
2. Click Options, then Format….
3. On the Alignment tab, check Merge cells, click OK.
4. Use Find Next or Find All.

Alternatively, for a specific range, check the Merge & Center button; a highlighted button indicates merged cells within the selection.

The above is the detailed content of How to unmerge cells in Excel. For more information, please follow other related articles on the PHP Chinese website!