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: 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: 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: 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: 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: 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 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
