How to unmerge cells in Excel

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.

How to unmerge cells in Excel

Unmerging cells in Excel is easy. Here's what you do:

  1. Select one or more cells you want to unmerge.
  2. On the Home tab, in the Alignment group, click Merge & Center.

The fastest way to unmerge cells in Excel

Or, click the drop-down arrow next to the Merge & Center button and select Unmerge Cells.
To split merged cells, click Merge & Center 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:
The contents of the merged cell is placed into the upper-left cell.

How to split all merged cells in a worksheet

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:

  1. Select the entire worksheet. For this, either click the little triangle in the upper-left corner of the worksheet or press the Ctrl + A shortcut.
    Select the whole Excel worksheet.
  2. With all the cells in the sheet selected, have a look at the Merge & Center button:
    • If it is highlighted, click it to unmerge all merged cells in the worksheet.
    • If it is not highlighted, there are no merged cells in the sheet.

    If the Merge & Center button is highlighted, click it to unmerge all cells in the sheet.

How to unmerge cells and copy the original value to each unmerged cell

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:
Unmerge cells and copy the original value to each unmerged cell.

To unmerge cells and fill down with duplicate values, please follow these steps:

  1. Select your table (or just the columns that have merged cells) and click the Merge & Center button on the Home tab. This will split all merged cells, but only the upper-left unmerged cells will be filled with data.
  2. Select the entire table again, go to the Home tab > Editing group, click Find & Select, and then click Go To Special…
    Open the Go To Special dialog box.
  3. In the Go To Special dialog window, tick off the Blanks option, and click OK:
    Select blank cells in Excel.
  4. With all the blank cells selected, type the equality sign (=) and press the Up Arrow key. This will create a simple formula that fills the first blank cell with the value from the above cell:
    A formula to fill blank cells with the value from the above cell.
  5. Since you want to fill all unmerged cells that are currently blank, press Ctrl + Enter to enter the formula in all of the selected cells.

As the result, each blank cell is filled with the value from the previously merged cell:
All blank cells are filled with the values from the previously merged cells.

Tip. If you wish to have only values in your dataset, replace formulas with their results by using Paste Special > Values. The detailed steps can be found in How to replace formulas with their values.

How to split the contents of the merged cell across several cells

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:

  • Text to Columns - allows splitting text strings by a specified delimiter such as comma, semicolon or space as well as separating substrings of a fixed length.
  • Flash Fill - a quick way to split relatively simple text strings of the same pattern.
  • Formulas to split text strings and numbers - best to be used when you need a custom solution for a specific dataset.
  • Split Text tool - the tool to try when all of the above methods have failed. It can split cells by any specified character or a few different characters, by string and mask (a pattern that you specify).

When the contents of the merged cells are split into individual cells, you are free to unmerge cells or delete the merged cells altogether.

How to find merged cells in Excel

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:

  1. Press Ctrl + F to open the Find dialog box. Or, go to the Home tab > Editing group, and click Find & Select > Find.
    Open the Find dialog box.
  2. In the Find and Replace dialog box, click the Options button, and then click Format…
    Find by format in Excel
  3. Switch to the Alignment tab, select the Merge cells check box under Text control, and click OK.
    Find merged cells in Excel.
  4. And now, click either:
    • Find Next to get to the next merged cell.
    • Find All to get a list of all merged cells.

When you click one of the found items, Excel will select the corresponding merged cell in your worksheet:
All merged cells in the worksheet are found.

Tip. In case you are just curious if there are any merged cells in a specific range, select that range and cast a glance at the Merge & Center button. If the button is highlighted, that means there is at least one merged cell in the selected range.

That's how you unmerge cells in Excel. I thank you for reading and hope to see you on our blog again next week!

You may also be interested in:

One Response to "How to unmerge cells in Excel"

  1. freekids says:

    Thank you so much Adriana :)

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard