Unmerge Cells in Excel: 4 Quick Ways

To unmerge cells in Microsoft Excel, select the merged cell and click the “Merge & Center” button in the Alignment group on the Home tab—this toggles the merge off and returns all data to the top-left cell. If you need to unmerge multiple cells at once, select the whole range before clicking the button. This guide covers every built-in method, what happens to your data, and how to avoid common pitfalls.

Why Merged Cells Cause Problems

Merged cells are a common source of frustration in Excel. They happen when two or more adjacent cells are combined into a single cell, which is handy for titles or group headers. However, they break many standard operations: sorting, filtering, copying/pasting, and using formulas like VLOOKUP or SUM over a range. When you merge cells, only the upper-left value is kept; all other values are discarded. That’s why unmerging is often the first step in cleaning up a messy spreadsheet.

Method 1: Unmerge a Single Cell

The quickest way to unmerge one cell is with the same button you used to merge it. Here’s the step-by-step:

1. Click on the merged cell to select it.
2. Go to the Home tab on the ribbon.
3. In the Alignment group, click the Merge & Center button (it looks like a box with arrows pointing inward).
4. The cell instantly splits back into its original individual cells.

If the merged cell was centered, unmerging will return the text to the left-aligned top-left cell. The other cells will be empty. This method works in Excel 2010, 2013, 2016, 2019, 2021, and Microsoft 365.

Method 2: Unmerge Multiple Cells at Once

If your sheet has many merged cells, you don’t have to click each one. You can unmerge them all in a single action:

1. Select the entire range that contains merged cells—you can click and drag, or press Ctrl+A to select the whole sheet.
2. Click the Merge & Center button once.
3. All merged cells within that selection are unmerged simultaneously.

This trick works even if the selection includes non-merged cells; those are simply ignored. It’s the fastest way to clean up a large report or a downloaded template.

Method 3: Use the Drop-Down Menu Options

The Merge & Center button has a small drop-down arrow next to it. Clicking that arrow reveals several options:

• Merge & Center – merges and centers the text.
• Merge Across – merges each row in the selection separately.
• Merge Cells – merges without centering.
• Unmerge Cells – this is the explicit command to split merged cells.

Choosing “Unmerge Cells” does exactly the same thing as clicking the main button, but it’s clearer when you’re working with a selection that might include both merged and unmerged cells. Some users prefer this option because it’s unambiguous.

Method 4: Keyboard Shortcut (with a Caveat)

There is no single built-in keyboard shortcut that directly unmerges cells. However, you can create a quick sequence using the Alt key:

1. Select the merged cell(s).
2. Press Alt, then H, then M, then U.

That sequence triggers: Home tab (H), Merge & Center menu (M), Unmerge Cells (U). It’s fast once you memorize it. Alternatively, you can add the “Unmerge Cells” command to the Quick Access Toolbar (QAT) for a one-click button. Right-click the command and choose “Add to Quick Access Toolbar.”

What Happens to Your Data When You Unmerge?

This is the most important thing to understand. When you unmerge a cell that contains data, the value stays in the top-left cell of the former merged range. All other cells become empty. If you had data in the other cells before merging, that data was permanently lost at the time of merging—unmerging does not bring it back.

For example, if you merged A1:C1 and typed “Total”, unmerging gives you “Total” in A1, and B1 and C1 are blank. If you need to distribute the value across all cells, you’ll have to re-enter it or use a formula. A common workaround is to use Center Across Selection instead of merging—it visually centers text across cells without actually merging them, so the data remains intact in the original cell.

Common Problems and How to Avoid Them

Unmerging can sometimes cause unexpected issues. Here are the most frequent ones and their solutions:

• Data loss warning: If you try to unmerge a cell that has content in multiple cells (which shouldn’t happen after a normal merge), Excel will warn you. This usually occurs if you copied and pasted over a merged area. Always check the warning before clicking OK.
• Formulas referencing merged cells: If a formula refers to a merged cell, unmerging can break the reference or return the wrong value. After unmerging, double-check formulas that pointed to the merged range.
• Merged cells in tables: Excel tables (created with Ctrl+T) do not allow merged cells. If you have merged cells inside a table, you’ll need to unmerge them before you can use table features.
• Print layout changes: Unmerging can change how your sheet prints, especially if you merged cells to create a wide header. You may need to adjust column widths or print settings after unmerging.

Alternatives to Merging (and Unmerging)

If you’re tired of dealing with merged cells, consider these alternatives that avoid the problem entirely:

• Center Across Selection: Select the cells, right-click, choose Format Cells, go to the Alignment tab, and under Horizontal, choose “Center Across Selection.” This visually centers text without merging, so all cells keep their data.
• Use a single cell with wrapping: Instead of merging to fit long text, widen the column or enable text wrapping in one cell.
• Use merged cells only for final presentation: If you must merge, do it as the last step before sharing or printing, and keep a backup copy of the unmerged data.

Frequently Asked Questions

Can I unmerge cells without losing the data in the other cells?

No. Once cells are merged, any data in cells other than the top-left is permanently deleted. Unmerging only restores the top-left value. To keep all data, you must unmerge before merging, or use Center Across Selection instead.

How do I unmerge all merged cells in a worksheet at once?

Select the entire worksheet by clicking the triangle at the top-left corner (or press Ctrl+A twice), then click the Merge & Center button once. All merged cells in that sheet will be unmerged.

Why is the Merge & Center button grayed out?

This usually happens when you’re editing a cell (press Enter to finish), or when the sheet is protected. If the sheet is protected, you’ll need the password to unprotect it before you can unmerge cells.

Does unmerging affect my formulas?

Yes, potentially. If a formula references a merged cell, unmerging can change the reference or cause a #REF! error. Always check formulas that point to previously merged ranges.

Can I unmerge cells in Excel Online or Google Sheets?

Yes. In Excel Online, the same Merge & Center button works. In Google Sheets, the button is called “Merge cells” and has a drop-down with “Unmerge” as an option. The behavior is the same—only the top-left value remains.

Is there a way to unmerge cells and keep the same text in all cells?

Not directly. You’d need to unmerge first, then re-enter the text in each cell, or use a formula like =A1 in the other cells. A quick trick: copy the merged cell, unmerge, then paste into the range with the “Fill” option (Ctrl+Enter after typing).

Related

• Insert Multiple Columns in Google Sheets
• Google Sheets Daily Schedule: Step-by-Step Guide
• How to Print WhatsApp Messages on iPhone