How To Unmerge Cells In Microsoft Excel : Excel Cell Merge Reversal Steps

by

Excel’s merged cells can be separated by selecting the range and choosing the unmerge option from the toolbar. If you’ve ever worked with spreadsheets, you know merging cells is handy for headers, but it can cause headaches when sorting or filtering data. This guide covers exactly how to unmerge cells in Microsoft Excel using multiple methods, from the ribbon to keyboard shortcuts and VBA.

Unmerging is simple once you know where to click. Whether you have a few merged cells or an entire sheet full, we’ll show you the fastest ways to clean up your data.

Why Unmerge Cells In Excel?

Merged cells look neat but break many Excel features. Sorting, filtering, and copying data often fail when cells are merged. You might also get errors in formulas or pivot tables.

Unmerging restores individual cells, making your spreadsheet functional again. It’s a common fix for messy data imported from other sources.

How To Unmerge Cells In Microsoft Excel Using The Ribbon

The ribbon method is the most straightforward. It works in Excel 2010, 2013, 2016, 2019, 2021, and Microsoft 365.

  1. Select the merged cell or range of merged cells you want to unmerge.
  2. Go to the Home tab on the ribbon.
  3. In the Alignment group, click the Merge & Center dropdown arrow.
  4. Choose Unmerge Cells from the list.

That’s it. The cells split back into their original individual cells. Only the top-left cell keeps its content; all other cells become empty.

If you selected a range that includes both merged and unmerged cells, Excel unmerges only the merged ones. The unmerged cells stay unchanged.

Quick Tip For Multiple Merged Cells

Hold Ctrl and click each merged cell to select non-adjacent ones. Then unmerge them all at once. This saves time if your merged cells are scattered.

How To Unmerge All Cells In A Worksheet

Sometimes you need to unmerge every merged cell in the entire sheet. Doing it one by one is tedious. Use this method instead.

  1. Click the triangle at the top-left corner of the sheet (above row 1 and left of column A) to select all cells.
  2. On the Home tab, click the Merge & Center dropdown.
  3. Select Unmerge Cells.

This unmerges every merged cell in the worksheet instantly. Be careful: only the top-left cell of each merged group keeps its data. The rest are cleared.

How To Unmerge Cells And Fill Values

When you unmerge, Excel keeps data only in the top-left cell. If you want the same value in all the newly unmerged cells, you need an extra step.

  1. Unmerge the cell using the ribbon method above.
  2. Select the range that was previously merged (now a group of individual cells).
  3. Press Ctrl + D to fill down, or Ctrl + R to fill right.
  4. Alternatively, type the value in the first cell, then drag the fill handle across the range.

This duplicates the original value into every cell that was part of the merge. Perfect for keeping your data intact.

Using Keyboard Shortcuts To Unmerge Cells

Excel doesn’t have a dedicated default shortcut for unmerging, but you can create one or use the Alt key sequence.

The quickest built-in method: press Alt, then H, then M, then U. This opens the Merge & Center dropdown and selects Unmerge Cells.

If you prefer a custom shortcut, add the Unmerge command to your Quick Access Toolbar (QAT). Then press Alt + number (the position of the button) to run it.

Add Unmerge To Quick Access Toolbar

  1. Click the small down arrow at the end of the QAT (top-left of Excel window).
  2. Choose More Commands.
  3. In the dropdown, select All Commands.
  4. Scroll to Unmerge Cells and click Add.
  5. Click OK.

Now press Alt + 1 (or the number assigned) to unmerge selected cells instantly.

How To Unmerge Cells In Microsoft Excel Using Find & Replace

If you don’t know where merged cells are, use Find & Replace to locate them first.

  1. Press Ctrl + F to open Find.
  2. Click Options to expand the dialog.
  3. Click the Format button.
  4. Go to the Alignment tab.
  5. Check Merge cells under Text control.
  6. Click OK.
  7. Click Find All.

Excel lists all merged cells. Press Ctrl + A to select them all, then close Find. Now use the ribbon or shortcut to unmerge.

This method is great for large sheets where merged cells are hidden.

How To Unmerge Cells In Microsoft Excel With VBA

For advanced users, VBA can unmerge cells automatically. This is useful for repetitive tasks or cleaning up imported data.

Here’s a simple macro to unmerge all merged cells in the active sheet:

Sub UnmergeAllCells()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If cell.MergeCells Then
            cell.UnMerge
        End If
    Next cell
End Sub

To use it:

  1. Press Alt + F11 to open the VBA editor.
  2. Go to Insert > Module.
  3. Paste the code.
  4. Press F5 to run.

This macro unmerges every merged cell in the used range. It’s fast and doesn’t require selecting anything.

VBA To Unmerge And Keep All Values

If you want to keep the original value in all cells after unmerging, use this modified version:

Sub UnmergeAndFill()
    Dim cell As Range
    Dim mergedRange As Range
    For Each cell In ActiveSheet.UsedRange
        If cell.MergeCells Then
            Set mergedRange = cell.MergeArea
            cell.UnMerge
            mergedRange.Value = cell.Value
        End If
    Next cell
End Sub

This fills every cell in the merged area with the top-left value. Handy for preserving data.

Common Issues When Unmerging Cells

Unmerging seems simple, but a few problems can pop up. Here’s what to watch for.

  • Data loss: Only the top-left cell keeps its content. Always copy important data before unmerging if you need it elsewhere.
  • Protected sheets: You cannot unmerge cells if the sheet is protected. Unprotect it first (Review tab > Unprotect Sheet).
  • Shared workbooks: Merged cells are not allowed in shared workbooks. Unshare before unmerging.
  • Table formatting: Merged cells inside Excel tables (Ctrl+T) cannot be unmerged directly. Convert the table to a range first.

How To Prevent Accidental Merging

If you work with data that shouldn’t be merged, disable the Merge & Center button. This stops you or others from merging cells by mistake.

  1. Right-click anywhere on the ribbon and choose Customize the Ribbon.
  2. Under Main Tabs, expand Home.
  3. Expand Alignment.
  4. Uncheck Merge & Center.
  5. Click OK.

The button disappears from the ribbon. To bring it back, re-check it.

How To Unmerge Cells In Microsoft Excel On Mac

Excel for Mac works similarly but with slight differences in the interface.

  1. Select the merged cell(s).
  2. Go to the Home tab.
  3. In the Alignment group, click Merge & Center.
  4. Choose Unmerge Cells.

Keyboard shortcut on Mac: Control + Option + M, then U. Or use the same Alt sequence if you have a Windows keyboard attached.

For unmerging all cells, select the entire sheet (click the top-left corner) and follow the same steps.

How To Unmerge Cells In Microsoft Excel Online

Excel Online (free web version) also supports unmerging. The steps are nearly identical.

  1. Select the merged cell.
  2. Click the Home tab.
  3. Click the Merge & Center button (it may show as a dropdown).
  4. Select Unmerge Cells.

Note: Excel Online has fewer features than desktop. You cannot use VBA or custom shortcuts. But basic unmerging works fine.

Best Practices For Using Merged Cells

Merged cells are useful for titles and headers, but avoid them in data areas. Here are alternatives:

  • Center Across Selection: Select the cells, right-click > Format Cells > Alignment > Horizontal > Center Across Selection. This looks like merging but keeps cells separate.
  • Use borders: Draw borders around a group of cells to visually merge them without actual merging.
  • Combine text with formulas: Use CONCATENATE or TEXTJOIN to display merged text without merging cells.

These methods preserve full functionality for sorting, filtering, and formulas.

How To Unmerge Cells In Microsoft Excel When Copying Data

When you copy data from another source (like a PDF or website), merged cells often come along. Here’s how to clean them up.

  1. Paste the data into Excel.
  2. Select the entire pasted range.
  3. Use Find & Format to locate merged cells (as shown earlier).
  4. Unmerge them using the ribbon.
  5. Fill the empty cells if needed (use Ctrl + D or fill handle).

This ensures your imported data is clean and ready for analysis.

How To Unmerge Cells In Microsoft Excel Without Losing Data

Data loss is the biggest fear when unmerging. Here’s a safe workflow.

  1. Before unmerging, copy the entire sheet (right-click tab > Move or Copy > Create a copy).
  2. On the original, select the merged cells.
  3. Unmerge them.
  4. Immediately press Ctrl + Z if you lose data you need.
  5. Or use the VBA macro above that fills all cells with the original value.

Another trick: select the merged area, copy it, paste it as values into a new location. Then unmerge the original. This preserves the data elsewhere.

How To Unmerge Cells In Microsoft Excel Using Power Query

Power Query is a data transformation tool in Excel. It can handle merged cells when loading data.

  1. Go to Data tab > Get Data > From Table/Range.
  2. In Power Query, select the column with merged cells.
  3. Go to Transform tab > Fill > Down or Up.
  4. Then remove any blank rows caused by merging.
  5. Click Close & Load.

This doesn’t technically unmerge, but it replaces merged cells with individual cells containing the same data. Useful for cleaning imported reports.

Frequently Asked Questions

Can I unmerge cells in Excel without losing data?

Only the top-left cell retains its data. To keep all values, copy the merged area before unmerging, or use a VBA macro that fills the range.

Why is the unmerge option grayed out?

This happens if the sheet is protected, the workbook is shared, or you’re in a table. Unprotect the sheet, unshare the workbook, or convert the table to a range first.

How do I unmerge all cells in a specific column?

Select the entire column by clicking its letter header. Then go to Home > Merge & Center > Unmerge Cells. This unmerges only merged cells in that column.

Is there a shortcut to unmerge cells in Excel?

No default single shortcut, but you can press Alt + H + M + U. Or add Unmerge to the Quick Access Toolbar and use Alt + number.

How to unmerge cells in Excel for Mac?

Select the cells, go to Home tab, click Merge & Center, then choose Unmerge Cells. Or use Control + Option + M, then U.

Final Thoughts On How To Unmerge Cells In Microsoft Excel

Unmerging cells is a basic but essential skill. Whether you use the ribbon, keyboard shortcuts, Find & Replace, or VBA, the process is quick once you know it. Remember to watch for data loss and always back up important sheets before making changes.

Now you know multiple ways to handle merged cells. Practice with a test file to build confidence. Your spreadsheets will be cleaner and more functional in no time.