Unmerge cells in Excel to revert previously combined cells back to their individual state, restoring flexibility and allowing for more granular data manipulation. Whether you’re restructuring a complex spreadsheet, standardizing data formats, or preparing for detailed analysis, understanding how to unmerge cells is crucial. This guide will provide you with a clear, step-by-step approach to unmerge cells in Excel, ensuring that your data is organized and presented in a way that best suits your analytical needs. By mastering this functionality, you’ll enhance your ability to adapt and refine your spreadsheets, making them more versatile and responsive to your data processing requirements.
This Tutorial Covers:
- Keyboard Shortcut for Cell Unmerging
- Unmerge Cells Using the Ribbon’s Option
- Implementing the QAT’s Unmerge Option (One-click Unmerge)
- Find each of the worksheet’s merged cells
- Unmerge the cells and enter the original value in the empty cells
1. Keyboard Shortcut for Cell Unmerging
Using a keyboard shortcut is the quickest approach to unmerge cells in a worksheet (at least for me).
Step 1: To unmerge all of the merged cells from a specific range of cells, you can either choose the entire worksheet or a specific range of cells.
Step 2: Enter the Excel keyboard shortcut for undoing cell merging is as follows:
ALT + H + M + C
In that order, press each of these keys (one after the other). The result looks like this:
The aforementioned shortcut would immediately undo the merging of all the cells in the selected range.
You should be aware of the following when unmerging cells in Excel:
- If the merged cells contain any text, unmerging them will cause all of the text to move to the top-left cell of the group of merged cells that was previously unmerged.
- Excel will merge all the cells if there are no merged cells in the selection. Control Z or simply pressing the keyboard shortcut again will reverse this.
2. Unmerge Cells Using the Ribbon’s Option
Using the Merge & Center option on the ribbon is a another, similarly quick method of unmerging cells in Excel.
Let’s say you want to undo the merging of the cells in Column B in the dataset depicted below.
Procedure of unmerging cells in excel is in below:
Step 1: Choose the cells or the range of cells you want to unmerge.
Step 2: On the “Home” tab, click. Select the “Merge & Center” symbol from the “Alignment” group.
That’s it! All of the merged cells in the selection would be promptly undone by the aforementioned processes.
Be aware that the top cell receives the original value from the merged cells, while the lower cells are left empty.
Cells can also be merged with this toggle button (where you can simply select the cells that you want to merge and then click on this icon).
3. Implementing the QAT’s Unmerge Option (One-click Unmerge)
By including the Merge & Center symbol to the Quick Access Toolbar, you may unmerge cells from your worksheet more rapidly (QAT).
This allows you to complete the task with a single click (as the quick access toolbar is always visible).
You can add the “Merge & Center” symbol to the quick access toolbar by following the instructions listed below:
Step 1: Activate the “Home” tab. Click the “Merge and Center” icon in the “Alignment” group using the right mouse button. Then select “Add to Quick Access Toolbar.”
The “Merge & Center” symbol would be added to the quick access toolbar by doing the aforementioned procedures. Simply select and choose this icon in the QAT when you need to unmerge cells in Excel.
Pro tip: You can also reach an icon you add to the “Quick Access Toolbar” using a keyboard shortcut. A number would appear beneath that emblem if you held down the ALT key. Holding the Alt key while pressing that number key will allow you to use this keyboard shortcut.
4. Find each of the worksheet’s merged cells
The merged cells in the selection would all be unmerged using any of the techniques we’ve discussed so far.
How would you handle it, though, if you wanted to manually unmerge some of these combined cells? (and not all).
Excel’s “Find and Replace” feature can be used to rapidly locate all combined cells, pick out the ones you wish to unmerge, and then unmerge them all at once.
The steps to do Excel cells unmerge are in below:
Step 1: Choose the range containing the cells you want to remove from the merging.
Step 2: Hold the Control key down and hit the F key (or Command + F on a Mac) with the cells selected. Then “Find and Replace” popup will then opened.
Step 3: Tap the “Format” button in the “Find and Replace” dialog box. If the “Format” button isn’t visible, click the “Options” button, and the “Format” button will appear.
Step 4: Select the “Alignment” tab in the “Find Format” dialog box. Select “Merge cells” from the menu. Select OK.
Step 5: In the “Find and Replace” popup, click the “Find All” button. This will locate all of the combined cells and display their cell references just beneath the dialog box. When manually selecting the cells, you want to unmerge, hold down the Control key.
Step 6: Unmerge all of these cells by selecting the ‘Merge and Center’ option in the ribbon (or use any of the methods covered above).
Using this technique, you can selectively undo cell fusions while maintaining part of the original merged cells.
5. Unmerge cells and enter the original value in the empty cells
The value in the merged cell is allocated to the top-left cell when cells are unmerged in Excel, which is a problem (after the cells have been unmerged).
As a result, if you have a block of three cells that is merged and contains a value, unmerge the block to reveal the value in the top cell and leave the other two cells empty.
As you can see in the sample below, I have unmerged the cells (which are highlighted in blue), and all save the top cell are now empty.
How would you go about getting this value in each of these three cells?
This is simple to accomplish with a small workaround.
The processes to unmerge cells and fill each one with its original value are as follows:
Step 1: Choose the cells or the range of cells you want to unmerge.
Step 2: On the “Home” tab, click. Select the “Merge & Center” symbol from the “Alignment” group.
The unmerged cells will be present at this time, and you will see that some cells are blank (which were earlier part of the merged cells).
Step 3: On the “Home” tab, click. Go to the “Editing” group and select “Find and Select.” Select “Go To Special” by clicking.
Step 4: Then “Go To Special” dialog box will appear as a result. Choose “Blanks” from the Go-To Special dialog box. Select OK.
The aforementioned procedures would only pick those blank cells that had previously been a component of the merged cells but had since become blank.
To duplicate the original value into these empty cells, proceed as follows:
Step 1: The active cell is the one that is highlighted in the list of selected blank cells.
Step 2: To equalize, press the equals (=) key. Consequently, the active cell will display an equal to sign. Use the up arrow to move up. This will take the reference to the cell that is immediately above the active cell.
Step 3: Press the Enter key while maintaining the CTRL key on your keyboard. All of these chosen blank cells will have the identical formula entered by this (in such a way that every blank cell would refer to the cell above it and pick the value from there).
You will now notice that every blank cell still contains the same value as it had earlier.
These are a few methods for unmerging cells in Excel.
For ready-to-use Dashboard Templates:
