Merging and unmerging cells in Excel is a common thing. Sometimes, according to your dataset, you may have to merge cells to narrow down data. In another instance, you might have to unmerge the same data to expand values individually.
Merging columns containing related data can save you time to filter and organize data. Meanwhile, these merged cells needed to be unmerged so that you can apply multiple other functions conveniently.
Before getting an answer to how to unmerge cells in Excel, let me clarify that we have 3 main methods to unmerge cells in Excel:
- Unmerge Cells with Excel Ribbon
- Unmerge Cells with Excel Shortcut
- Unmerge Cells and copy the Original Value to Each Unmerged Cell
Unmerge Cells in Excel with Ribbon
At first, you need to select single or multiple ranges of merged cells that needed to be unmerged.
- Open the Home tab and click on the “Merge & Center” option.
- Now, you will see all the selected merged cell ranges have become unmerged. This is it!
Unmerge Cells in Excel with Keyboard Shortcut
Well, from the beginning to now using the keyboard shortcut approach is the fastest and quickest one. You can unmerge cells in Excel easily by applying keyboard shortcuts.
First of all, choose a range of cells that needed to be unmerged, or else select the entire sheet to unmerge the merged cells. Following is the shortcut command to apply:
ALT + H + M + C
You need to press these keys in sequence and be careful while pressing these keys. Once the above shortcut keys are pressed correctly, your selected cells will be unmerged instantly.
Other than the process, you have to consider a few things while unmerging cells in Excel:
When the merged cells get unmerged, you will see the text in these merged cells goes to the upper left cell of that merged cells group.
In case, when your selection does not contain any merged cells, Excel will merge all the cells. To undo this function, just press CTRL + Z, or else you can use the keyboard shortcut once again.
How to Unmerge Cells and Copy the Original Value to Each Unmerged Cell
Sometimes, your dataset needs to be enhanced and for this, you have to unmerge cells while putting values in each unmerged cell from the original cell.
Below are some easy-to-follow steps to unmerge cells and enter the duplicate values:
- Choose the cells from the table or columns and click on the Merge & Center option given on the Home tab. you will see all the merged cells are split and the data will be added in the top-left unmerged cells.
- Select the whole table once again and open the Home tab.
- Click on the Editing option and choose Find & Select.
- Now, open the Go To Special option.
- Uncheck the Blanks option from the Go To Special dialog box and click OK.
- Put the equal sign = with all the blank cells selected and press the UP arrow key. You will see the formula fills up the first blank cell containing a value from the above cell:
- You currently want to put values in all the unmerged cells. Press CTRL + ENTER and the formula will be added by default in the cells you selected. Now, you will see the blank cells are filled with the value.
Things to Consider
You need to make sure the selected data has merged cells or not before unmerging the cells.
To know whether the sheet has merged cells or not, you have to choose the cells in the worksheet. Open the Home tab and click on the Alignment option. If the “Merge & Center” option is highlighted it clearly means that your selected data has merged cells.
Final Thoughts
Before getting to know how to unmerge cells in Excel, you must have an idea of how you can merge cells. Both functions run side by side because you may have to use them and vice versa. Keep going with an exploration of Excel’s new tricks and tips.
