How to Unmerge Cells in Excel? 3 Best Methods
Note: This tutorial on how to unmerge cells in Excel is suitable for all Excel versions including Office 365.
The merge cells feature in Excel is a very handy tool in presenting the data in a visually appealing way. It helps declutter and align your spreadsheet elements. But, if you rely too heavily on using it, you will soon find yourselves in a mess.
The merge cells feature comes with its own disadvantages and will prevent you from doing even simple tasks like sorting, filtering and selecting data.
Hence, it is advisable to use the merge feature with care. Nonetheless, there is a way to roll back the changes made by the merge cell feature. In this guide, I am going to show you how to use each one of these simple techniques to unmerge cells in Excel.
Related:
How to Extract an Excel Substring? – 6 Best Methods
How to Superscript in Excel? (9 Best Methods)
How To Find Duplicates in Excel? (3 Easy Methods)
Find All Merged Cells in a Worksheet
The first order of business, when it comes to unmerging cells is to identify and locate the merged cells. You may think, what is so difficult about this. But, in reality, visually locating each one of these merged cells will prove to be challenging and time-consuming.
You can quickly find all the merged cells in a sheet using the following steps:
- Click on the Find option under the Find & Select drop-down menu in the Home Tab. The shortcut is Ctrl+F.
- In the next window click on Options followed by Format.
- In the Find Format window, go to the Alignment tab and click the tick box next to Merge Cells and Click OK.
- Next, choose either Find All or Find Next to highlight all merged cells or highlight them one by one respectively.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
How to Unmerge Cells in Excel?
The textbook method to unmerge cells is pretty straightforward. Follow these steps:
- Select the cells you want to unmerge. Use Ctrl+A if you want to unmerge all merged cells in the worksheet.
- Click on the drop-down button next to Merge & Centre in the Alignment group under the Home tab.
- From the drop-down menu, select Unmerge Cells.
That’s all. The selected cells will be unmerged immediately and the cell contents will be moved to the upper-left cell of each unmerged cell.
Note that this will leave the remaining unmerged cells blank.
How to Unmerge Cells in Excel, but Retain the Original Value in Each Unmerged Cell?
In most cases, the empty or blank unmerged cells are not good for the data integrity of your worksheet. You need to fill them with their original data as soon as possible to avoid errors or confusion.
To do this, follow these steps:
- Unmerge cells in the worksheet as discussed before, using the Unmerge Cells option from the Merge & Centre drop-down menu.
- Select the range of cells you just unmerged and go to Find & Select and click on GoTo Special. You’ll find it in the Editing group of the Home tab.
The keyboard shortcut is F5 followed by Alt+S or Ctrl+G followed by Alt+S.
- In the GoTo Special menu, choose Blanks and click OK. This will select all the blank unmerged cells in your data range.
- Now type, “=” followed by an Up arrow key to indicate that you want to copy the upper value to each unmerged cell. Note, that the up arrow key applies only if you have merged the cells vertically. If you have merged them horizontally, use the left arrow key.
- Use Ctrl+Enter to apply the formula to all the blank unmerged cells.
Other Methods to Split Merged Cells in Excel.
On some occasions, you may need to split the contents of these merged cells into multiple cells. Now, there are many ways to accomplish this in Excel. Find their detailed guides here:
- Use Text Formulas when you need a dynamic split. That is if you want the output to change continuously along with the changes in the input.
- Use Text to Columns if your merged cells have a specific delimiter that you can use to split the cells.
- Use Flash Fill if you want to quickly split simple merges. Note that this may not always work, especially if the split pattern is complex.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
That’s all about how to unmerge cells in Excel. Test these methods in a practice worksheet and feel free to ask any questions or doubts you may have in the comments section.
If you like liked this guide and want more high-quality tutorials on Excel check out our free Excel resources centre.
Ready to take the next step and hone your skills in Excel?
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Adam Lacey
Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.
