Some cells can be merged as you fill in data that seem similar. However, this action can make it hard for some simple tasks to be performed in the worksheet. For example, data cannot be sorted in columns with at least one merged cell. It also becomes difficult to filter and select range in the worksheet. Therefore, there is a need to unmerge cells in Microsoft excel so that these tasks can be performed with ease. The following are steps to follow.
1. Select the cells that you want to unmerge in excel.
2. Go to the Home tab, choose the Alignment group, and select Merge and Center.
3. You can also select the drop-down arrow next to the next Merge and Center key and then click on Unmerge Cells.
This way, Excel unmerges all the merged cells you have selected.
To unmerge all cells in your worksheet, follow these easy steps
1. Highlight your entire worksheet. You can do this by pressing Ctrl + A or clicking the little triangle in the upper left corner side of your worksheet.
Select the Merge and Center button. Do this when;
If it has been selected, click on it to unmerge the merged cells
If it has not been selected, there are no cells that have been merged in the worksheet.
You can also unmerge cells and then copy the original value to each cell that has been unmerged. In this way, the dataset is improved. Follow these simple steps.
2. Select the columns that have the merged cells
3. Click on Merge &Center buttons in the Home Tab. The merged cells are split, and data is filled in the upper- left unmerged cells
4. Select your entire table. Click on the Home Tab, select the Editing group and click Find & Select. Finally, click Go To Special
5. Tick off the Blanks Option. Finally, click OK.
6. Type the equality sign and press the Up Arrow key on your keyboard. The formula is created that fills the first blank cell with a value
7. To fill the unmerged cells currently blank, press Ctrl +Enter, and then enter the formula in the selected cells.
In the event that you have been given a poorly structured worksheet, you may find it difficult to find the cells that have been merged in that worksheet. Merged cells are to be avoided in Excel Worksheets to make the tasks to be performed here easy. Follow these simple steps to find out how you can find merged cells in Excel.
8. Press on Ctrl +F and open a dialog box. Click on Find
9. You can go to the Home Tab. Click on Editing Group, then Find & Select. Click on Find
10. Click Option and choose Format
11. Go to the Alignment Tab, click Merge cells and click OK
12. You can either click on Find Next to get the following merged cell or Find All to get a list of all the merged cells.
Using Of Keyboard Shortcut to Unmerge Cells in Excel
Generally, the excel keyboard fastens any work to be done hence, you can use the method above by clicking;
Alt +H+M+U.
For you to apply the method, you need to tap all the keys simultaneously.
Steps:
1. When unmerging cells, you will have to highlight all the merged cells from the given source.
2. Then, tap the Alt key where a ribbon will appear.
3. Continue to tap the H key that will make the Home tab start functioning.
4. When the Home tab opens, tap the M key to activate the Merge and Center command.
5. Press the U key that picks the “Unmerge cells” from the values.
6. Finally, you will notice all merged cells are unmerged.
Unmerge Cells After Finding All Merged Cells
You will use the method to unmerge certain cells from a dataset that is difficult to select the cells to unmerge.
Steps:
1. Select the Find &Select option in the ribbon, then tap Find.
2. When a dialogue box emerges, select the Format option from the box.
3. Another dialogue box will also emerge, where you will click on the Alignment option and check for Merge cells.
4. Then, you press OK.
5. Tap Find All where all the merged cells in the dataset will appear.
6. Since you need to unmerge cells, go to the merge cells then select the Merge & Center on the ribbon.
7. By that all the cells are unmerged.
Copy The Actual Value in Every Unmerged Cell
You will find the method above more suitable to use when unmerging multiple cells in excel using the following steps:
1. Firstly, determine the areas where merged cells are and highlight them.
2. Then, go to the ribbon and press Find & Select. Next, check for Go To Special option in the details provided in the section.
3. A new dialogue box will emerge, then check Blanks and tap OK.
4. After that all the blank cells will be highlighted.
5. In the blank cell that will respond type the formula below:
=D5
6. Click the Ctrl + Enter keys altogether.
7. Lastly, the values of emerged cells will be placed in the unmerged cells.
Use Excel VBA to Copy the Original Value in Unmerged Cells
You can also use the VBA (Visual Basic for Applications) code to copy initial values in the merged cells. This helps you to unmerge cells using the following steps:
1. Click on the Developer tab and select Visual Basic, which will open the Visual Basic window.
2. Right-press on the sheet and check for View Code, where a new empty “VBA MODULE” emerges.
3. Copy and paste the codes provided:
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
.UnMer
.Formula = Xrng.Formula
End With
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
4. Alternatively, you can click Run option or click F5 to run the code.
5. Place the data you have in the input box found in the new dialogue box that emerges.
6. Tap OK.
7. Finally, you will note all the values placed in the blank cells by copying all the original values.