How to unmerge cells in Excel
In this article, we’ll look learn how to unmerge cells in excel, how to find merged cells and much more.
Often times, you would merge small cells to create bigger cells for alignment purposes. But once you merge cells certain tasks become harder to do. Sorting data in column is not possible if the column has even one merged cell. Selecting ranges & filtering also become a problem. So let’s see how to split cells in excel i.e. in excel how to unmerge cells.
Unmerge Excel Cells
Unmerging cells is pretty easy.
- Select the cells or range of cells to unmerge.
- Go to Home tab, under alignment select Merge & Center.
- You can also click on the arrow to the right of Merge & Center to open drop-down and select Unmerge cells.
- Voila selected merged cells are now split in excel.
How to Split Cells in Excel of entire Worksheet
It might sound like a tedious process but it’ll just take a few clicks.
- Select the entire worksheet. You can either click the triangle in top left corner of worksheet or press CTRL + A to select all cells.
- Now go to Home tab and observe Merge and Center excel option.
- If option is highlighted then click it to unmerge excel cells.
- If it’s not highlighted that means there are no merged cells in the worksheet.
This is how one can split cells in excel.
How to unmerge excel cells and get original value in unmerged excel cells
Often times you’ll be merging cells having common values. Let’s say you merged some cells but now you want to unmerge them. The catch is you also want to get back the original value that was present in the cells.
- Select the cells or range of cells having merged cells and select Merge and Center excel option under Home tab. This will split cells in excel. But there will be unmerged cells which would be empty.
- Select the cells or range again. Go to Home tab, under Editing group select Find & Select then click Go To Special.
- In the dialog box, check the Blanks option and hit OK.
- All blank cells are now selected. Juts type equal sign = and press Up Arrow key. This will create a simple formula that will fill the blank cells. Now press CTRL + Enter to enter the formula in all selected blank cells.
- Voila now all blank unmerged cells have the original common value.
Do remember this can only be done for cells which had common values which were merged. Also, now the unmerged cells contain formulas, make sure you convert formulas to text.
How to find merged cells in excel
Let’s say you receive a spreadsheet which contains a lot of merged cells. Now you can’t manually check each sheet or scroll down to hundreds of rows to find merged cells. In this case we see how we can find merged cells in excel in one go and unmerge cells if you want.
- Go to Home tab, under Editing group select Find & Select and click Find. You can also just press CTRL + F.
- In the Find and Replace dialog box, select Options button and click Format…
- Go to Alignment tab and click Merge cells checkbox and hit OK.
- Now click:
- Find Next to go to next merged cell from the list
- Find All to see the list of all merged cells
- When you select any cells from the list within dialog box, excel will select the cell in the worksheet.
Now from here you can select Find All, get the entire list of cells. Select the cells from the list and go to Merge & Center to unmerge excel cells.
That was all about how do you unmerge cells in excel.
I hope this article was helpful to you and explained you what is a merged cell in excel and how to unmerge cells in excel.
Do also read about How to Split Cells in Excel to understand how you can split values in excel into multiple columns based on given criteria.
If you have any questions or tips, I’d love to hear them in the comment section below.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Doubt Resolution