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.

  1. Select the cells or range of cells to unmerge.
  2. Go to Home tab, under alignment select Merge & Center.
    Unmerge cells in Excel
  3. You can also click on the arrow to the right of Merge & Center to open drop-down and select Unmerge cells.
    Unmerge cells in Excel
  4. Voila selected merged cells are now split in excel.
    Unmerge cells 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.

  1. Select the entire worksheet. You can either click the triangle in top left corner of worksheet or press CTRL + A to select all cells.
    Unmerge cells in Excel
  2. Now go to Home tab and observe Merge and Center excel option.
    1. If option is highlighted then click it to unmerge excel cells.
    2. If it’s not highlighted that means there are no merged cells in the worksheet.
      Unmerge cells in Excel
      Unmerge cells in Excel

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.

  1. 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.
    Unmerge cells in Excel
  2. Select the cells or range again. Go to Home tab, under Editing group select Find & Select then click Go To Special.
    Unmerge cells in Excel
  3. In the dialog box, check the Blanks option and hit OK.
    Unmerge cells in Excel
  4. 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.
    Unmerge cells in Excel
  5. Voila now all blank unmerged cells have the original common value.
    Unmerge cells in Excel

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.

  1. Go to Home tab, under Editing group select Find & Select and click Find. You can also just press CTRL + F.
    Unmerge cells in Excel
  2. In the Find and Replace dialog box, select Options button and click Format…
    Unmerge cells in Excel
  3. Go to Alignment tab and click Merge cells checkbox and hit OK.
    Unmerge cells in Excel
  4. Now click:
    1. Find Next to go to next merged cell from the list
    2. Find All to see the list of all merged cells
  5. 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.

Conclusion

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.

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Ask a Question or Leave a Reply

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Learn Army Logo
Learnarmy.com Copyright © 2020 All Rights Reserved