Excel Remove Split Merged Cells: How-To

Updated 6 November 2026 · Standard formula, shown below

This article explains how to remove merged cells in Microsoft Excel and split them back into individual cells, covering both the quick unmerge action and the more complex task of filling the resulting blank cells with the original values. You will learn the exact steps for Excel 2013 through Microsoft 365, what happens to your data when you unmerge, and how to avoid the most common pitfalls. The instructions work for Windows and Mac versions of Excel, with only minor menu differences noted where relevant.

Why Merged Cells Cause Problems

Merged cells are a common formatting choice for making a title span across several columns or to group related rows visually. However, they frequently cause headaches during data analysis, sorting, filtering, and when using pivot tables. A merged cell only retains the value from the top-left cell; all other cells in the merged range become empty. This means any formula that expects a value in every row of a column will fail or return incorrect results. For example, if you merge cells A1:A3 and type “Total” in A1, then cells A2 and A3 are effectively blank. If you later try to sort that column, Excel may warn you about merged cells or produce unpredictable results.

Another issue is that merged cells can block the use of certain features like “Fill Down” or “Flash Fill,” and they often cause problems when you try to copy and paste data into another application. Because of these issues, many users find it necessary to remove merged cells and replace them with properly repeated values.

How to Unmerge Cells in Excel

Unmerging cells is a straightforward process that takes only a few clicks. Here are the steps for both Windows and Mac:

Select the merged cell or the range of cells that contains merged cells. You can select multiple merged areas at once by holding Ctrl (Windows) or Cmd (Mac) while clicking.
On the Home tab, locate the Alignment group.
Click the small arrow next to the Merge & Center button to open the dropdown menu.
Choose Unmerge Cells. The cells will immediately split back into their original individual cells.

Alternatively, you can click the Merge & Center button itself (the one with the icon) to toggle the merge off. If the selected range contains a mix of merged and unmerged cells, clicking the button will unmerge all of them.

After unmerging, you will notice that only the top-left cell of each former merged range retains its value. All other cells in that range are now blank. This is expected behavior and is the main reason why simply unmerging is often not enough for data analysis.

How to Fill Blank Cells After Unmerging

If you need the unmerged cells to contain the same value that was in the merged cell, you must fill the blanks. This is a common requirement when preparing data for a pivot table or a mail merge. Here is a reliable method using the “Go To Special” feature:

Unmerge the cells as described above, leaving blanks in the lower cells.
Select the entire column or range that contains the blanks.
Press F5 (or Ctrl+G on Windows, Cmd+G on Mac) to open the Go To dialog box.
Click the Special button at the bottom left.
In the Go To Special dialog, choose Blanks and click OK. This selects all blank cells in your chosen range.
Type an equals sign (=) and then press the Up Arrow key. This creates a formula that references the cell directly above each blank cell. For example, if A2 is blank, the formula becomes =A1.
Press Ctrl+Enter (Windows) or Cmd+Enter (Mac) to fill all selected blank cells with that formula simultaneously.
Now the blanks contain formulas that point to the cell above. To convert these formulas into static values, copy the range and use Paste Special > Values.

This method works because the blanks are filled with the value from the cell directly above, which, in a column of merged cells, is the original value. For example, if you had merged A1:A3 with “North”, after unmerging and filling, A1, A2, and A3 will all contain “North”.

Using Find & Replace to Unmerge All at Once

If your worksheet has many merged cells scattered across different areas, you can unmerge them all in one go without manually selecting each one. Excel’s Find & Replace feature can help:

Press Ctrl+H (Windows) or Cmd+H (Mac) to open the Find and Replace dialog.
Click the Options button to expand the dialog.
Click the Format button next to the Find what field.
In the Find Format dialog, go to the Alignment tab.
Under Text control, check the Merge cells checkbox. Leave all other fields blank.
Click OK to close the Find Format dialog.
In the main Find and Replace dialog, click Find All. Excel will list all cells that have the merged format.
Press Ctrl+A to select all the found items in the list. This selects all merged cells in the worksheet.
Click Close on the Find and Replace dialog. The merged cells remain selected.
Now go to the Home tab, click the Merge & Center dropdown, and choose Unmerge Cells.

This technique is especially useful when you inherit a spreadsheet from someone else and want to clean it up quickly. After unmerging, you can then use the fill-blanks method to restore values if needed.

Common Problems and How to Avoid Them

Even though unmerging is simple, users often run into a few recurring issues. Here are the most common ones and how to handle them:

Data loss after unmerge: Only the top-left value is kept. If you need the other values, you must have stored them elsewhere or you must use the fill-blanks method immediately after unmerging. There is no built-in “undo” that restores the lost values, so always make a backup copy of the file before unmerging if you are unsure.
Merged cells in a table: Excel tables (Insert > Table) do not allow merged cells within the table range. You must unmerge them before creating a table. If you try to merge cells inside a table, Excel will block the action.
Sorting issues: If you attempt to sort a column that contains merged cells, Excel will either refuse or give a warning. The only reliable fix is to unmerge all cells in the sort range first.
Copy-paste problems: When you copy a merged range and paste it elsewhere, the paste may not preserve the merge format, or it may merge the pasted range. To avoid this, use Paste Special > Values if you only need the data.
Printing issues: Merged cells can cause pages to break awkwardly. Unmerging and using center-across-selection (see below) is a safer alternative for printing.

Alternative: Center Across Selection

If your goal is purely visual—such as centering a title over several columns without merging—Excel offers a better alternative called Center Across Selection. This feature visually centers text across a range of cells but does not actually merge them, so all cells retain their individual values and functionality. Here is how to use it:

Select the range of cells where you want the text centered (e.g., A1:D1).
Right-click and choose Format Cells (or press Ctrl+1).
Go to the Alignment tab.
In the Horizontal dropdown, choose Center Across Selection.
Click OK.

This method is especially recommended for reports and dashboards because it avoids all the data problems associated with merged cells. The text appears centered, but each cell in the range is still independent. You can sort, filter, and use formulas without any issues.

Frequently Asked Questions

How do I unmerge cells in Excel without losing data?

To unmerge without losing the data that was in the merged cell, you must fill the blank cells after unmerging. Use the Go To Special > Blanks method described above: unmerge, select the range, press F5, choose Blanks, type = and press the Up Arrow, then press Ctrl+Enter. This copies the value from the cell above into each blank cell. After that, convert the formulas to values using Paste Special > Values.

Can I unmerge all merged cells in a worksheet at once?

Yes, you can unmerge all merged cells at once using Find & Replace. Open Find and Replace (Ctrl+H), click Options, set the format to find cells with the Merge cells alignment, click Find All, then press Ctrl+A to select all found cells. Close the dialog and click Unmerge Cells on the Home tab.

Why does Excel say “This action won’t work on multiple selections” when I try to unmerge?

This error occurs when you select a range that contains both merged and unmerged cells and then try to merge or unmerge them. To fix it, select only the merged cells (use Find & Replace to select them all) or unmerge them one by one. Alternatively, select a single merged cell and unmerge it, then repeat for others.

Is there a keyboard shortcut to unmerge cells?

There is no default keyboard shortcut for unmerging, but you can add the Unmerge command to the Quick Access Toolbar and then press Alt+1 (or the number that corresponds to its position) to trigger it. To add it, right-click the Quick Access Toolbar, choose More Commands, select All Commands, find Unmerge Cells, and add it.

Does unmerging cells affect formulas that reference the merged cell?

Yes, it can. If a formula references a merged cell, it references the top-left cell of the merged range. After unmerging, the formula still references that same cell, but the other cells in the former merged range become blank. If the formula was expecting a value in a cell that is now blank, it may return a #REF! error or a wrong result. Always check formulas after unmerging.

What is the difference between “Merge Across” and “Merge & Center”?

“Merge & Center” merges a selected range of cells into one cell and centers the content horizontally. “Merge Across” merges cells in each row of the selected range separately, but does not merge the rows together. For example, if you select A1:C2 and use Merge Across, you get two merged cells: A1:C1 and A2:C2. “Merge Across” is useful for creating headers that span multiple columns but keep each row separate.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.