This short tutorial shows how to quickly unmerge cells in Excel, how to find all merged cells in a worksheet, and how to fill each unmerged cell with the original value from the merged cell.
When you have related data in several cells, you may be tempted to combine them into a single cell for alignment or comparison purposes. So, you merge a few smaller cells into a bigger one only to realize that merged cells have made it impossible to perform the simplest tasks on your worksheet. For example, you cannot sort data in columns that have at least one merged cell. Filtering or even selecting a range might also be a problem. Well, how do you unmerge cells in Excel to get things back to normal? Below, you will find a few simple techniques.
Unmerging cells in Excel is easy. Here's what you do:
Or, click the drop-down arrow next to the Merge & Center button and select Unmerge Cells.
Either way, Excel will unmerge all the merged cells in the selection. The contents of each merged cell will be placed into the upper-left cell, other unmerged cells will be empty:
At first sight, the task may seem cumbersome, but in fact it takes only a couple of mouse clicks.
To unmerge all cells on the sheet, you do the following:
To improve the structure of your dataset, you may often need not only to unmerge cells but also fill each unmerged cell with the value from the original cell, as shown in the screenshot below:
To unmerge cells and fill down with duplicate values, please follow these steps:
As the result, each blank cell is filled with the value from the previously merged cell:
In situations when a merged cell contains a few pieces of information, you may want to put those pieces into separate cells. Depending on your data structure, there are a few possible ways to handle this task:
When the contents of the merged cells are split into individual cells, you are free to unmerge cells or delete the merged cells altogether.
You already know that merged cells are something you should avoid in your Excel worksheets. But what if you have been given a poorly structured spreadsheet and you are trying to convert it into something useful. The problem is the sheet contains a fairly big amount of merged cells you don't know about.
So, how do you find merged cells in your worksheet? Just remember that merging cells relates to alignment, and alignment is part of formatting, and Excel Find can search by format :) Here's how:
When you click one of the found items, Excel will select the corresponding merged cell in your worksheet:
That's how you unmerge cells in Excel. I thank you for reading and hope to see you on our blog again next week!
10 responses to "How to unmerge cells in Excel"
Thank you so much Adriana :)
can you tell us i am not able to write 4-8 this kind of value in excel. because it has been automatic date format. but i don't want date. please help me out.
Hi Mohammad,
Excel assumes you are entering a date and converts the input to the Date format. To prevent this from happening, apply the Text format to the cell, and then type a value like 4-8.
Thanks, for the tips. I run into the issue of not being able to sort a worksheet that had merged cells. You gave me a simple but elegant solution!
Best 2 tips EVER for Excel data management!! Saved hours and hours. Thank you so much.
Hi Svetlana Cheusheva, after I'm going to type = (equality sign) every selected blank cell getting unselect. Have you missed any step to tell us?
Hi Saddam,
Be sure to perform no other actions between selecting blanks and typing the equality sign. That is, you should not click in the first blank cell. With all the blank cells selected, press the = key, and then press the up arrow key. The result should look like in the screenshot in step 4.
great tip. Saved a lot of time
thank you so much:)
VERY USEFUL AND VERY CLEAR> THANK YOU VERY MUCH> SOME TIMES UNMERGE CELLS COMMAND DOES NOT WORK CAN YOU HELP ME TO SOLVE THIS PROBLEM>