How to Unmerge Cells in Excel (3 Easy Ways + Shortcut)

Excel has a lot of useful tools and features. There are some features that are widely used, but I wish were not so.

The ability to merge cells is one such feature.

While I don’t use this option myself, I still find myself unmerging cells (mostly in the workbooks shared by other people).

In this tutorial, I will show you some ways to quickly unmerge cells in Excel.

While I don’t think people are going to stop merging cells anytime soon, I hope these simple ways to unmerge cells will save you some time and frustration.

Keyboard Shortcut to Unmerge Cells

The fastest way (at least for me) to unmerge cells in a worksheet is to use a keyboard shortcut.

You can choose a specific range of all cells (from which you want to unmerge cells), or you can select the entire worksheet and then unmerge all the merged cells from the entire sheet.

Below is the keyboard shortcut to unmerge cells in Excel:

ALT + H + M + C

Press each of these keys in succession (one after the other).

The above shortcut would instantly unmerge all the merged cells in the selected range.

A few things you need to know when unmerging cells in Excel:

• If you have any text in the merged cells, when you unmerge these cells, all the text would go to the top-left cell in that group of merged cells that have now been unmerged.
• In case there are no merged cells in the selection, Excel will merge all the cells. You can undo this by using Control Z, or simply using the keyboard shortcut again.

Unmerge Cells using Option in the Ribbon

Another equally fast way to unmerge cells in Excel is to use the Merge & Center option in the ribbon.

Suppose you have the dataset as shown below and you want to unmerge the merged cells in Column B.

Below are the steps to quickly unmerge these cells in Excel:

1. Select the cells/range from which you want to unmerge cells
2. Click the Home tab
3. In the Alignment group, click on the ‘Merge & Center’ icon

That’s it!

The above steps would instantly unmerge all the merged cells from the selection.

Note that the original value that was there in the merged cells goes to the top cell and the rest of the cells are empty.

This is a toggle button that is also used to merge cells (where you can simply select the cells that you want to merge and then click on this icon).

Adding the UnMerge Option in the QAT (One-click Unmerge)

Another way to quickly unmerge sales in your worksheet is to add the Merge & Center button to the Quick Access Toolbar (QAT).

This way you can get the work done with a single click (as the quick access toolbar is always visible).

Below are the steps to add the Merge & Center button to the quick access toolbar:

1. Click the Home tab
2. In the Alignment group, right-click on the ‘Merge and Center’ icon
3. Click on the ‘Add to Quick Access Toolbar’ option

The above steps would add the ‘Merge & Center’ icon to the quick access toolbar. Now when you have to unmerge cells in Excel, just make the selection and click on this icon in the QAT.

Find All the Merged Cells in the Workbook

All the methods covered so far would unmerge all the merged cells in the selection.

But what if you want to go through these merged cells and manually unmerge some of those (and not all).

You can use the ‘Find and Replace’ feature in Excel to quickly find out all the merged cells, select only the ones that you want to unmerge and then quickly unmerge these.

Below are the steps to do this:

1. Select the range that has the cells that you want to unmerge
2. With the cells selected, hold the Control key and press the F key (or Command + F if you’re using Mac). This will open the ‘Find and Replace’ dialog box
3. In the Find and Replace dialog box, click on the Format button. In case you don’t see the Format button, click on the Options button and the Format button would show up.
4. In the Find Format dialog box, click on the ‘Alignment’ tab.
5. Click on the ‘Merge cells’ option
6. Click OK
7. Click on the Find All button in the Find and Replace dialog box. This will find all the merged cells and show you the cell references of all of these cells right below the dialog box.
8. Hold the Control key and manually select the cells that you want to unmerge
9. Click on the ‘Merge & Center’ option in the ribbon to unmerge all these cells (or use any of the methods covered above)

This method allows you to selectively unmerge cells while keeping some merged cells intact.

Unmerge Cells and Fill the Blank Cells with the Original Value

One issue with unmerging cells in Excel is that the value in the merged cell gets allocated to the top-left cell (after the cells have been unmerged).

So, if you have a block of three cells that is merged and has a value in it, and you unmerge this block, the value would go to the top cell, and the remaining two cells would be empty.

Below is an example where I have unmerged the cells (highlighted in orange) and you can see that only one the top cell retains the value and the rest are empty.

What if you want to get this value in all these three cells.

You can do this easily with a little bit of a workaround.

Below are the steps you can use to unmerge cells and fill all the cells with the original value:

1. Select the cells that have the merged cells
2. Click the Home Tab
3. In the Alignment group, click on ‘Merge and Center’.
4. At this point, you will have the unmerge cells and you will see that there are some blank cells (which were earlier part of the merged cells).
5. Click the Home tab
6. In the Editing group, click on ‘Find and Select’
7. Click on Go To Special option. This will open the Go To Special dialog box
8. In the Go-To Special dialog box, select ‘Blanks’
9. Click OK

The above steps would select only those blank cells which were earlier part of the merged cells but after unmerging, these became blank.

Now follow the below steps to copy the original value into these blank cells

1. In the selected blank cells, there is 1 cell which is highlighted – it’s the active cell
2. Press the equal to sign (=) key. this will enter equal to sign in the active cell
3. Press the up arrow key. this will pick up the cell reference of the cell right above the active cell
4. Hold the control key and press the enter key. This will enter the same formula in all these selected blank cells (in such a way that every blank cell would refer to the cell above it and pick the value from there)

Now you will see that all the blank cells have the same value that it had earlier.

You can now get rid of the formulas by converting these formulas into static values.

So, these are some of the ways you can use to unmerge cells in Excel.

I hope you found this tutorial useful!

Other Excel tutorial’s you may like:

• How to Combine Cells in Excel
• How to Split Cells in Excel (separate into multiple columns)
• How to Split Multiple Lines in a Cell into a Separate Cells / Columns
• Excel AUTOFIT: Make Rows/Columns Fit the Text Automatically