Microsoft Excel is a handy tool for data visualization, manipulation and analysis. Often, we merge some cells in an Excel spreadsheet to make it look more structured or for a bit of convenience.
But merged cells have some drawbacks too. For example, you cannot sort a column’s data if it contains even one merged cell.
Are you someone who is searching to get to know how to unmerge cells in Excel? In this article, I have explained how to unmerge cells in Excel for various circumstances and how to find them in a large spreadsheet. So, let’s dive in!
How to Find Merged Cells in Excel
To find all the merged cells in a spreadsheet, you need to search by the formatting category of cells since merging cells is related to alignment, and aligning is part of formatting.
Suppose you were given a poorly organized spreadsheet and asked to turn it into something useful. But you’ve noticed that the sheet has a lot of cells that have been merged, which is hard to find manually. How would you find all the merged cells in your spreadsheet?
Go and check our other article on change row height in Excel.
Here is an easy way to find all of the merged cells in a worksheet:
- Press Ctrl+F to bring up the Find box. Alternatively, go to Find & Select > Click on Find.
- Click on the Options button in the Find and Replace box > Click on Format.
- Go to the Alignment tab > Check the box next to Merge cells in the Text control section > Click OK.
- Click on either Find Next to go to the next merged cell or Find All to see a list of all the merged cells.
When you click on one of the items found, Excel will select the merged cell in your worksheet. 
If you want to see if there are any merged cells in a specific range, you can select that range and look at the Merge & Center button. If the button is highlighted, at least one merged cell is in the chosen range.
Here’s a complete guide on how to sort by date in Excel.
How Can I Unmerge Cells That Are Merged in Excel?
In Microsoft Excel, there are different ways to separate cells that have been merged. You can use the Excel Ribbon, the Merge Cells menu, or keyboard shortcuts to separate the cells.
Remember that when you unmerge cells, the content of the merged cell will go to the upper-left cell of the range of cells that were merged. So, different methods must be used to separate the data of the merged ones from the data of the unmerged ones.
Here is how to unmerge cells in Excel for different purposes:
1. Unmerge Certain Cells
Unmerging specific cells in Excel is pretty straightforward. All the options are available on the ribbon, and unmerging can be done with a few clicks right away!
Here are the steps to unmerge specific cells in Excel:
- Select the cell or cells you want to unmerge.
- Navigate to Merge & Center in the Alignment group on the Home tab.
- Click on the dropdown arrow next to the Merge & Center button > Choose Unmerge Cells from the menu that appears.
Excel will separate all the cells that were joined in the selection. The contents of each merged cell will be put in the upper-left cell, and the other cells that haven’t been merged will be left empty.
Check out the easiest way to do a subscript in Excel.
2. Unmerge Cells in Excel and Keep all data
If you have to unmerge cells to perform some operations on your dataset, sometimes an empty cell could cause a problem. To counter that, you’ll need to insert the original cell’s value into each unmerged cell.
To unmerge cells and ensure that each cell contains the same information, follow these steps:
- Select the columns that include the merged cells > Click on the arrow beside Merge & Center on the Home Every merged cell will be unmerged, and the data will be placed in the group’s upper left cell.
- Select the entire table once more > Navigate to the Home tab > Go to the Editing section.
- Click on Find & Select > Select the Go To Special button.
- Select the Blanks option in the dialog box > Click on OK.
- Type the equal sign (=) with all of the empty cells selected > Hit the Up Arrow key on your keyboard. This will result in a straightforward formula that copies the cell’s content above into the selected cells.
- Press Ctrl + Enter to insert the formula into all of the cells that have been selected.
As a consequence of this, the content of the most recently merged cell is copied into each of the cells that are now empty. If you wish your dataset to only contain values, you can replace formulas with their results by using the Paste Special > Values command.
Follow our guide step-by-step to remove page break in Excel.
3. Split The Content of The Merged Cell Across Several Cells
In certain circumstances, such as when a merged cell contains a few different bits of information, you might want to divide those pieces of information into their cells.
When you need to split a single cell into two or more cells, the Text to Columns feature is beneficial. It can split text strings using a delimiter like a comma, semicolon, or space and split substrings of a fixed length apart.
Here is how to split the data across all the cells that were merged:
- Start by adding a duplicate column so you don’t overwrite your existing data.
- Select the cell/cells you want to split > Go to the Data tab.
- Navigate to the Data Tools section > Click on the Text to Columns button.
- Choose how to split cells by selecting delimiter or by width. In the example, there are commas between the contents of each cell, so I chose Delimited > Click on Next.
- Choose the delimiters such as comma (,).or space.
Select two delimiters together when your data may have two or more delimiters in a row. For example, when there are a few spaces between words or when a comma and a space separate the data.
Check out our recent article on add months to date in Excel.
What Is The Shortcut Used for Unmerging Cells in Excel?
You are in luck in Excel if you like to use keyboard shortcuts since you can merge and unmerge cells using a combination of keyboard shortcuts.
Here is how to unmerge cells in Excel using keyboard shortcuts:
- Select the merged cell/cells.
- View the available shortcuts by pressing the Alt key on your keyboard.
- Go to the Home tab by pressing H on your keyboard.
- Access the option for merging cells by tapping on the M key.
- Unmerge the cells using the U key on your keyboard.
If you follow this procedure, you will only need a few keystrokes to unmerge the data cells in your spreadsheet.
Can You Unmerge Multiple Cells in Excel?
You can unmerge multiple merged cells inside a worksheet simultaneously. To achieve that, first, you must find all the merged cells in your spreadsheet by following the instructions discussed earlier.
Moreover, you can also select all the cells of a spreadsheet and follow some steps to achieve the same result. The task could appear difficult, but in reality, it can be completed with just a few clicks of the mouse.
Here is how to unmerge multiple cells in Excel at once:
- Choose the complete worksheet by either clicking on the small triangle in the upper-left corner of the worksheet or hitting the shortcut Control + A on your keyboard.
- Make sure all the cells in the sheet are selected before navigating to the Merge & Center button. If the button is highlighted, clicking it will cause all the merged cells in the worksheet to be unmerged. Or, if it is not highlighted, it means that the sheet does not contain any merged cells.
- Click on the dropdown arrow beside Merge & Center > Choose the Unmerge cells option.
Doing so would unmerge all the selected merged cells, and the values of the cells would set themselves in the first cell of the merged cell range.
Conclusion
Sometimes, unmerging cells is needed to structure the spreadsheet better, especially if the amount of data is huge. You will need to use Excel’s functionalities to sort and make sense of data instead of manually inspecting them.
I hope this article has helped you to learn how to unmerge cells in Excel for different circumstances. Test these methods on a practice worksheet, and don’t be afraid to ask questions or voice any concerns you might have.
