How to Unmerge Cells in Excel (7 Easy Methods)

In this article, we will show you different methods to unmerge cells in Excel. To make our work easy we often merge two or more cells. Merging does nothing but combine two or more cells and returns a new larger cell. But, sometimes we have to unmerge cells because some of the formulas do not work properly while the cells are merged. To illustrate the process of unmerging cells we will represent 7 easy methods in this article.

Download Practice Workbook

You can download the practice workbook from here.

7 Easy Methods to Unmerge Cells in Excel

While describing the methods of this article we will use the same dataset for all the methods. This will help you to differentiate one method from another which will give you a better insight.

1. Use Ribbon to Unmerge Cells in Excel

First and foremost, we will see how to unmerge cells in excel with the use of an excel ribbon. To demonstrate this method to you we have the following dataset of football players, their countries, and playing positions. If we take a look at the dataset we will see merged cells in the columns named “Country” and “Position”. So, let’s see how we can unmerge these cells by following some simple steps.

STEPS:

• Firstly, select all the merged cells. To select multiple merged cells just hold the Ctrl key and select the cells with the “left-click” of the mouse.

• Secondly, go to the Home Click the option “Merge & Center” from the ribbon. A new drop-down menu will be available.
• Thirdly, from the drop-down select the option “Unmerge Cells”.

• Lastly, the above action will unmerge all the selected merged cells from our data range.

2. Unmerge Cells from Entire Excel Worksheet

Suppose, we are working with a big dataset where we have too many merged cells. Now, we don’t want to unmerge cells one by one. Rather we want to unmerge all the merged cells from the entire worksheet at the same time. To perform this kind of action we just need to follow the simple steps given below.

STEPS:

• First, click on the triangle icon in the left corner of the worksheet to select the entire worksheet. We can also use Ctrl + A key to select the entire worksheet.

• Next, go to the Home Click the option “Merge & Center” from the ribbon. A new drop-down menu will be available.
• After that, from the drop-down select the option “Unmerge Cells”.

• Finally, the above action unmerges all the merged cells from our entire worksheet.

3. Insert the Unmerge Option in Quick Access Toolbar

If we need to merge and unmerge cells frequently while working, we can add a button for the option “Merge & Center” in Quick Access Toolbar. By doing this we can merge or unmerge cells just with one mouse click. Let’s see the steps to perform this action.

STEPS:

• In the beginning, go to the Home From the ribbon do a right-click on the option “Merge & Center”.
• Then, select the option “Add to Quick Access Toolbar”.

• Now a new button is visible in the Quick Access Toolbar.

• After that, select all the merged cells from our data range and click on the newly added button in the Quick Access Toolbar.

• Finally, the last action unmerges all the merged cells from our data range.

4. Use of Keyboard Shortcut to Unmerge Cells in Excel

In excel keyboard shortcut is used to do any task quickly. In this method, we will see the use of keyboard shortcuts to unmerge cells in excel. To demonstrate this method to you we will continue with our previous dataset. The keyboard shortcut to unmerge cells in excel is:

We have to press the keys one by one. Do not hit on all the keys at once. Let’s see in step by step what happens when we press the keys one by one.

STEPS:

• Firstly, select all the merged cells from the data range.

• Secondly, press the Alt
• This will give you the ribbon which looks like the following image.

• Thirdly, press the H It will active the Home tab.

• Fourthly, press the M It will open the drop-down values of the option “Merge & Center”.

• After that, press the U This command will select the option “Unmerge Cells” from the drop-down.

• Lastly, after all the steps we can see that all the merged cells are unmerged now.

Similar Readings

• How to Merge Cells in Excel with Data (3 Ways)
• Merge Text from Two Cells in Excel (7 Methods)
• How to Merge Text Cells in Excel (9 Simple Methods)
• Merge Cells in Excel Table (7 Ways)
• How to Merge Two Cells in Excel without Losing Any Data

5. Unmerge Cells after Finding All Merged Cells

In this method, we will learn how we can unmerge cells selectively. This method is applicable when we do not want to unmerge all the cells in a large dataset. On the other hand in a large dataset, it is hard to find exact cells which we want to unmerge. From this method, we will see how we can find all the merged cells from a dataset. After finding the merged cells we will unmerge only specific cells not all the cells in the data range. Let’s see the steps regarding this method.

STEPS:

• First, go to the “Find & Select” option in the ribbon and select the option Find.

• Next, a new dialogue box will appear. Click on the Format option from the dialogue box.

• Then, one more dialogue box will appear. From the box go to the “Alignment “ option and check the option “Merge cells”.
• Click on OK.

• Now, click on Find All. This command will return a list of all the merged cells.

• Here we want to unmerge the cells in the column named “Country”.

• After that, select those two merged cells from the list and click on the “Merge & Center” option on the ribbon.

• Finally, this action unmerges all the cells under the column “County”.

6. Copy the Actual Value in Every Unmerged Cell

Suppose, we are unmerging multiple cells in a worksheet. From the following figure, we can see that unmerged cells remain blank after unmerging. If we want to unmerge cells and auto-fill down the actual values simultaneously we can follow the below steps.

STEPS:

• In the beginning, select the data range where the unmerged cells lie.

• Next, go to the “Find & Select” option in the ribbon. Select the option “Go To Special” from the drop-down menu.

• Then, a new dialogue box will appear. Check the option “Blanks”.
• Click on OK.

• The above command will select all the blank cells.

• Now, in the active blank cell enter the following formula:

• Press Ctrl + Enter.

• Finally, The above command will input the original value of the merged cell in all the unmerged cells.

7. Use Excel VBA to Copy the Original Value in Unmerged Cells

We can also use VBA (Visual Basic for Applications) to copy the original value in unmerged cells. The output of this method is the same as the previous one. The difference is that we will use the VBA code in this method to copy the original value in unmerged cells. Let’s see the steps to perform this action.

STEPS:

• Firstly, from the Developer tab go to the “Visual Basic”.

• This will open the visual basic window.
• Secondly, do right-click on the active sheet and go to the option “View Code”.

• A new blank VBA MODULE will appear.

• Thirdly, insert the following code in that module:

Sub CopyOriginalValue()
Dim Xrng As Range, xCell As Range
Title = "Copy Original Value"
Set WorkXrng = Application.Selection
Set WorkXrng = Application.InputBox("Range", Title, WorkXrng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Xrng In WorkXrng
If Xrng.MergeCells Then
With Xrng.MergeArea
.UnMerge
.Formula = Xrng.Formula
End With
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• Now, click on the Run or press the F5 key to run the code.

• After that, a new dialogue box will appear. Insert our data range ($B$4:$D$10) in the Range input box.
• Then, press OK.

• Lastly, we can see that the above action copy all the original values in the blank cells.

Things to Remember

1. You have to select the specific area of merged cells before unmerging particular cells. Otherwise unmerging will not work.
2. While using “Sorting and Filtering” you should not use merging. Merged cells create unnecessary issues in the “Sorting and Filtering” of a data range.
3. When we merge cells the column and rows lose their coherence.

Conclusion

In the end, this article will give you a complete overview of how to unmerge cells in excel. To practice yourself download the practice workbook added with this article. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible. Stay tuned with us for more interesting solutions to Microsoft Excel problems.

Related Articles

• Macro to Populate a Mail Merge Document from Excel
• [Fix:] Excel Unable to Merge Cells in Table
• Merge Text from Two or More Cells into One Cell (Easiest 6 ways)
• How to Merge Cells in Excel with Data (3 Ways)
• Merge Cells in Excel Vertically Without Losing Data
• How to Merge Multiple Cells in Excel at Once (3 Quick Ways)