Merging multiple excel cells allows your data to visualize as one large cell. The process is quite essential for presenting the data set to a business meeting and other purposes.
But one big drawback of merging data is you can’t implement any formula on the merged cells.
Even you will be unable to edit cell data if the cells are merged.
You can simply split the incorporated cell data into multiple unmerged cells to avoid such incompetence.
Don’t know how to do that? In this article, I’ll describe different methods of unmerging cells in MS Excel. You can use these techniques on any version of Microsoft Excel, including macOS.
So, let’s start.
How to Unmerge Cells in Excel (For Windows)
The quickest way to split merged cells in Excel is using keyboard shortcuts. Highlight the specific range of cells you want to unmerge > Press and hold the Alt key > Press H + M + C while holding the Alt key. This shortcut will definitely unmerge cells in your worksheet.
However, remember one thing, if the cells are not merged, and even then, if you use this shortcut, Excel will merge all the cells in that worksheet.
So, make sure you select the merged cell before pressing Alt + H + M +C altogether. Now, let’s get to the other unmerging methods I discussed.
Follow our guide on how to Mail Merge in Outlook with Excel & Word.
Here is the simplest way to unmerge Excel cells on Windows PC:
1. Use Unmerge Option in Excel Ribbon
If the keyboard shortcuts for unmerging don’t work, then probably you’re managing a large data set. Don’t worry; this method will surely unmerge cells in your worksheet. Just follow my lead.
Here are the steps to unmerge Excel cells using ribbon options:
- Highlight the range of cells that are merged.
- Go to the Home tab.
- Click on Merge & Center from the alignment group.
Note: The cell values of all unmerged cells will show up on the top merged cell. You’ll need to rewrite the values on every cell.
That’s how you unmerge cells using Excel’s in-built ribbon option.
Check out our separate post on how to Install Macro in Excel.
Adding the unmerge button in Excel’s quick access toolbar can be really helpful. As the toolbar is always visible, you can split the merged cells with just one click.
Here’s the procedure to include unmerge button in the toolbar:
- Open the Excel sheet and go to the Home tab.
- Right-click on the Merge and center icon from the alignment group.
- Select Add to Quick Access Toolbar from the dialogue box.
That simple task will add the Merge & Center icon in the toolbar. When you need to unmerge cells, just mark them and click on the shortcut icon from the top.
Now, if you’re a mac user, an unmerging option is also available for you. Check out the following section for unmerging excel cells in macOS.
Here’s a complete guide on how to Copy Values Without Formulas on Excel.
How to Unmerge Excel Cells in Mac
Unmerging Excel cells in Mac is quite similar to Windows. To unmerge combined cell data on Mac, you must follow the instructions given below.
Here are the steps to Split text from one cell into multiple cells in Mac:
- Select the cell you want to unmerge.
- Go to the Data tab and click on the text to columns. A new window named Convert Text to Columns Wizard will pop up.
- Tick on Comma and Space in the Delimiters menu. You’ll see the preview of selected data at the bottom.
- Click on Next once you’re done with this window.
- Select column data format in the following window. Text is preferable.
- Choose the destination cell where you want to unmerge.
- Click on Finish when you complete the above instructions.
Note: do not select more than one column while unmerging. And make sure there is sufficient space for a new column to the right of the merged cells.
Check out our separate post on Fix Microsoft Excel Freezing or Slow.
How to Find the Merged Cells in Excel
If your Excel sheet size is large, you might have trouble finding the merged cells. And, as I explained earlier, using the unmerging trick on an excel sheet that is not combined — will make the entire sheet merge.
So, it’s safe to find the merged cells first, then unmerge them.
Here are the steps to find merged cells in Microsoft Excel:
- Open the Excel sheet and press Ctrl + F. That will open the Find & Select option.
- Click on Options from the bottom right.
- Select the Format menu from the right side of that window.
- Go to the Alignment tab and tick on Merge cells > Click Ok.
- Click on Find All to view all merged cells in your Excel worksheet.
Now, select the cells you want to unmerge and press Alt+ H + M +C to unmerge them once and for all.
How to Unmerge Cells in Excel and Fill Cells
Sometimes, unmerging can take a lot of time if the spreadsheet contains a large amount of data. And, since Excel only unmerges data on the selected cell, you may need to fill up all the data manually by yourself.
Sounds like a hectic job, right?Don’t worry, as I’m here; I’ll share a simple trick that will not only unmerge your excel cells but also fill empty cells with duplicate data. Fair enough? Let’s see how to do that.
Here are the steps to Unmerge Cells And Fill them With Duplicate Data With One Click:
- Open the excel sheet and navigate to the Home tab > Merge & Center > Unmerge Cells.
- Highlight all the cells that you unmerged and click on Find & Select.
- Click on Go to Special from the list.
- Select Blanks from the menu and click Ok. All the blank cells will be selected.
- Type in = and press the up arrow on your keyboard.
- Press Ctrl + Enter. All the cells will be filled with original cell data.
This method may sound simple, but it’s not when you’re managing a large amount of data. Make sure you follow the steps as I explained.
How to Unmerge Excel cells Without Losing Data
As we know, unmerging can cause data loss from Excel sheets; asking about the procedure of unmerging without losing data is common.
Actually, it’s not difficult to split the combined data without any data loss. You just need to focus on some minor settings while unmerging.
Here are the steps to split merged cells without losing data:
- Click on any of the rows that are not merged in your spreadsheet.
- Click on Merge & Center. This entire row will be merged.
- Mark the other merged cells in Excel.
- Go to the Home tab > Merge & Center.
- Copy the unmerged data and paste it onto the cell you merged earlier.
Even if there was a chance of data loss during this process, copying unmerged data on a merged cell will resolve the issue.
Hopefully, you’ve understood all these techniques for unmerging an excel cell. If you have any further queries, continue reading to the end.
Follow our guide on how to Mail Merge in Outlook with Excel & Word.
Frequently Asked Questions
How do I Unmerge all merged cells?
Select all the merged excel cells > Press Alt + H + M +C on your keyboard to unmerge every cell.
How do you Unmerge cells in Excel and keep all data?
Unmerge the cells with a regular approach > Press Ctrl + F > Go to special > Select Blanks from the next window > change cell values.
Can you Unmerge multiple cells in Excel?
Select all cells in the sheet (click the arrow from the upper left corner of the cells) > Go to Home > Merge & Center.
Conclusion
If you’ve read until this point, you have probably become an expert in merging and unmerging excel cells.
First, try the keyboard shortcuts for unmerging. Then, try adding a button in the quick access toolbar. That will speed up the time of unmerging.
However, comment down if you have any concerns about other excel tricks. My support team will be happy to assist.
