How to Merge – Unmerge Cells in Excel

HomeLearn Excel OnlineHow to Merge – Unmerge Cells in Excel

Merge and Unmerge the cells while working in Excel is a common task.

Sometimes when you get the data that has many cells merged possibly due to having related data in many cells and due to which you cannot filter and sort data in columns accurately, that time to unmerge those cells, you use the unmerge feature of excel.

Merge the Cells through the Ribbon Option

  1. To merge the cells, First, select the cells you want to merge.
  2. After that, go to the home tab, and here you will find the “Merge & Center” option within the “Alignment” group on the ribbon.
  3. Now, just click on the “Merge & Center” option.
    1-home-tab-alignment-group
  4. The moment you click on the “Merge & Center” option, you will get the pop-up option and you just need to click “ok”
    2-merge-centre
  5. At this point, your selected range of cells is merged.
    3-merged-cell-range

Unmerge the Cells through Ribbon Option

  1. First, select single or multiple ranges of merged cells you want to unmerge.
  2. After that, go to the home tab, and here you will find the “Merge & Center” option is highlighted.
  3. Now, just click on this “Merge & Center” option.
    4-unmerge-cells-through-ribbon
  4. At this point, your selected merged cell ranges become unmerged.
    5-unmerged-cells

Merge and  Unmerge the Cells Using Keyboard Shortcut

Like other Excel functions and features, you can merge and unmerge the cells using the keyboard shortcut and save your time.

  1. To merge the cells, select the cell range you want to merge.
  2. After that press “Alt + H + M + C” or “Alt + H + M + M” one after the other and click “ok” on the pop-up option.
    6-merge-using-eyboard-shortcut
    7-ok-to-merge
  3. At this point, your selected range of cells is merged.
    8-merged-cells
  4. Now, to unmerge the merged cells, select the range of merged cells that you want to unmerge.
  5. After that press “Alt + H + M + C” one after the other.
    9-unmerge-using-keyboard-shortcut
  6. At this point, your selected range of merged cells becomes unmerged.
    10-unmerged-cells-range

Many times you may find the sheet with multiple merged cells within that sheet and to unmerge that all cells in one go, you just need to select the worksheet by pressing “Ctrl + A”.

Or by clicking on the triangle sign on the top left corner of the sheet and your sheet become selected and after that just click on the “Merge & Center” option from the ribbon and all the merged cells become unmerged within the sheet in one go.

multiple-merged-cells
unmerged-worksheet

Unmerge and Fill the Unmerged Cells with the Original Value

The challenge that always comes while unmerging the cells is to fill the original value in all the unmerged cells because when you merge any cell range, Excel allocates only the top cell value or the left cell value within that merged range based on the selection of your cells range.

So, when you unmerge that merged cells, you always find the value in the top unmerged cell and other unmerged cells of that selected range become blank.

  • First, unmerge the merged cells range.
    13-unmerge-merged-cells
  • After that, select the unmerged cells including the blank cells or the entire data set/table.
  • Now, go to the “Home Tab” and under the “editing” group, click on “Find & Select” and then click “Go To Special”.   
    14-go-to-special
    15-select-data-go-to-special
  • Once, you click on “Go To Special”, a pop-up option will open and select the “Blank” and click ok.
    16-select-blank-option
  • Now, you will find all blank cells selected and just enter (=) equal sign and then press the up arrow key from the keyboard and press Ctrl + Enter button and the formula gets copied to all the blank cells.
    17-all-blank-cells-selected
  • At this moment, your blank cells are filled with the original value that you had in the merged cells range.
    18-blank-cells-filled

How to Find the Merged Cells

Many times, you may get an Excel file with huge data set and you are not sure if this has some merged cells or not, so to find the merged cells in the data, you can find them all just by doing some below steps.

  • First, click anywhere within the data and go to the “Home” tab and under the “editing” group, click on “Find & Select” and then click on the “Find” option or just press Ctrl + F from your keyboard.
    19-find-merged-cells
  • Once you click on the “Find” option or press Ctrl+ F, you will get the “Find & Replace “ menu and just click on “Format”.
    20-find-replace-menu
  • Now, select the “Alignment” and tick mark the “Merge cells” and click ok.
    21-select-alignment-tab
  • In the end, select the “Find All” and you will get the list of all the merged cells within the data.
  • To see the merged cells one by one, just press “Find Next” and every time it takes you to the next merged cells and that merged cells become highlighted or you can also do it by clicking on the merged cells list.
    22-select-the-find-all