Easy Ways You Can Unmerge Cells in Excel

Easy Ways You Can Unmerge Cells in Excel

There are times when you get information from a web server or a colleague with a merged cell for any reason like formatting.

Whenever this occurs, you may become annoyed as you do specific tasks on the excel sheets, like copying a cell that doesn’t match the show area.

You may not choose individual columns whenever they have a merged cell from an adjacent column.

It won’t allow it to filter, sort, etc. Because of that, you may need to unmerge cells.

There are so many valuable features and tools that Excel has.

Some of the features are used widely.

One of these features is the ability to have the cells merge.

Even if you are not using this option for yourself, you may still find yourself unmerging cells primarily in workbooks that other people share.

In our tutorial, we will show you how you can easily unmerge cells in Excel.

Although people may not stop merging cells anytime, I hope that you’ll consider doing these simple ways in unmerging cells so you can save time and avoid becoming frustrated.

Once you get related information in several cells, you may feel tempted to combine them into a single cell for comparison or alignment.

Because of that, merging some smaller cells into a larger one may it impossible for you to perform any simple task on your worksheet.

Whenever you have a merged cell, you may not sort the data in the columns.

Choosing or filtering a range may also become an issue.

How can you unmerge Excel cells if you want things to get back to normal?

Here are some of the simple techniques on how you can do this:

Ways You Can Use Excel to Unmerge Cells

Whenever there are merged cells that you want to unmerge, have the buttons merge and center used.

Use Merge and Center Buttons

Steps on how to unmerge cells

  • Choose the merged Cells
  • Check out the home tab and click on the “Merge & Center” that you can find in Alignment Group.
  • Once you do this, the cells will become unmerged.

You can do the same thing with the sequential shortcut ALT>H>M>C.

Just remember that the merged cell’s value will only be in the upper-left cell.

The other cell will be void, null, empty, blank, and a black hole, as you can see here:

Black Hole

It can lead us to our next mission: to Unmerge the cells and have each cell filled with the original value.

Unmerge Cells using a Keyboard Shortcut

The quickest way you can unmerge cells in a worksheet is by using the keyboard shortcut ALT + H + M + C.

How to have all merged cells split in a worksheet

This task may initially appear cumbersome, but, in reality, it only needs two clicks from the mouse.

Do these to unmerge every cell on the sheet:

  1. Choose the whole worksheet. You can either press the shortcut Ctrl + A or click on the little triangle found in the upper-left corner of the worksheet.

  1. Once you choose all of the cells in the sheet, check out the button Merge & Center:
    • If it’s not highlighted, the sheet does not have merged cells.
    • If it’s highlighted, click on it so you can have all of the worksheet’s merged cells unmerged.

Ways You Can Unmerge a Worksheet’s Merged Cells

These are the steps you need to follow if you want to unmerge every merged cell on an Excel sheet:

  • Press CTRL+A to select all.
  • Click on the Home tab menu’s Merge and Center button or use the keyboard shortcut ALT>H>M>C to unmerge.

It’s done as all of the cells are unmerged.

Keyboard Shortcut to Unmerge Cells

You may have a specific range of every cell chosen if you want to unmerge cells or select the whole worksheet and have all the merged cells unmerged from the entire worksheet.

If you want to unmerge cells in Excel, you can use this keyboard shortcut:

ALT + H + M + C

Press on every key in succession or one after another.

With this shortcut, you can instantly have the selected range of merged cells unmerged.

Make sure that you know these things whenever you unmerge Excel cells:

  • Whenever the selection does not have merged cells, Excel will have all the cells merged. Undo this using the keyboard shortcut ALT+H+M+C or Ctrl+Z.
  • If the merged cells have text, you can have these cells unmerged that all of the text will go to the group of merged cells’ top-left cells that are now unmerged.

Ways you can have the merged cell’s contents split across several cells.

There are situations wherein a merged cell has some data that you would want to have placed into separate cells.

The possible ways you can handle this task would depend on your data structure:

  • Flash Fill – Here is a quick way to split strings of a similar pattern in a relatively simple way.
  • Text to Columns – It allows splitting text strings using a specified delimiter like space, semicolon, or comma, including a fixed length of separating substrings.
  • Split Text Tool – This is the tool that you should try if all of these methods fail. It can have cells split by any different characters or any specific character with a pattern that you need to specify, like a mask and a string.
  • Formulas to split numbers and text strings – It would be best to use this whenever you need a customized solution for a particular dataset.

Whenever the merged cells’ contents get split into individual cells, feel free to have all of the merged cells deleted or unmerged.

Use A Formula to Split the First Name and Last Name

Here is a bonus.

It is the formula you need to write in cell B2 so you can extract the last from cell A2 when it has a full name.

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

Have it copied in the cells below with the last name in column B. Write down this formula to get the first name:

=LEFT(A2,FIND(” “,A2)-1)

Have the Contents of the Merged Cell Split Across Several Cells

We were able to discuss a lot regarding unmerging cells.

However, we know that you have to split the data into various cells from one cell.

You may want to become aware of how you can split the merged cell’s text into a separate cell.

You can do this with the excel tool text to column.

Whenever you have information in a cell that you want to have split into columns using a specific character, this is the Excel feature you can use.

Here is an example wherein we split the full name into the first and the last name.

Since we know that space separates the first and last names, we can use it as a separator.

These are the steps that you need to follow so that data will split into adjacent columns.

    • Clear of add columns wherein you will write the split data
    • Sequentially press the ALT>A>E keyboard shortcut or check out the Data tab and then click on the text to column. It will open the Text To Column Wizard.
    • Choose the column you want to split.

    • Click on the Delimited option and select next.

    • Here, have your special Separator chosen. For this example, select space.

  • Tap on Finish.

Now, what we have in the original column is our first name with the last name in the column beside it. Rename the column’s full name to the first name so you can be good to go.

You can do the same thing with the use of a formula.

Please note that you need to be careful before using this to split the cells whenever an adjacent cell has data they want to overwrite.

Be aware of how many columns you’ll need for the split data.

Whenever you are unaware of how many cells you need, make sure to use a new sheet.

You may also use the sheet columns you used last not to lose any vital data. 

Use the Option in Ribbon to Unmerge Cells

Another great way you can unmerge Excel cells is by using the ribbon option Merge & Center.

Suppose that you have the dataset below.

You want to have Column B’s merged cells to become unmerged.

If you want the Excel cells to unmerge immediately, make sure to do these steps:

  1. Choose the range of cells from which you want to have the cells unmerge.
  2. Click on the Home tab.                                                                                                         
  3. Click on the icon ‘Merge & Center’ that you can find in the Alignment group.                               
  4. That is all you need to do! These are the steps that can help you immediately unmerge every merged cell in the selection.

Take note that the original value present in the merged cells will go to the top cell while the other cells are left empty.

With this toggle button, you can merge the cells by choosing the cells you want to merge and click on the icon.

Ways You Can Unmerge the Cells in Excel

It is easy to unmerge the cells in Excel.

These are the things that you need to do.

  1. Choose the cells that you want to become unmerged.
  2. Go to the Home tab, check the Alignment group, and click on Merge & Center.

You may also click on the drop-down arrow beside the Merge & Center button and choose Unmerge Cells.

Either of these can unmerge every selected merged cell. A

ll of the contents that every merged cell has will get placed in the upper-left cell while the other unmerged cells will become empty:

The Addition of the One-Click UnMerge Option in the QAT

There is another great way you can easily unmerge the cells in your worksheet.

Just include the Merge & Center button in your QAT or Quick Access Toolbar.

Through this, you can finish the work in just one click, as you can always see the quick access toolbar.

Here are the steps that you can do to include the Merge & Center button in the quick access toolbar:

  1. Click on the Home tab.
  2. Check out the Alignment group and right-click on the icon ‘Merge and Center.’
  3. Tap on the option ‘Add to Quick Access Toolbar.’

With these steps, you can have the icon ‘Merge & Center’ included in the quick access toolbar.

Whenever you need to have the Excel cells unmerged, select and have this icon in the QAT clicked.

Professional Tip: In the Quick Access Toolbar, you may also access it with a keyboard shortcut whenever you include an icon.

Hold on to the ALT key so you can see the number underneath the icon.

Whenever you want to use a keyboard shortcut for this, hold on to the Alt key and press the number key. 

Look for All of the Workbook’s Merged Cells

So far, in all the methods we covered, they can help unmerge every selected merged cell.

Yet, what can you do whenever you want to get through the merged cells and have some of those (and not everything) unmerged manually?

You may use the Excel feature ‘Find and Replace’ whenever you want to know all of the merged cells immediately.

Just choose the ones you want to unmerge and have these unmerged quickly.

Here are the steps on how you can do this:

  1. Choose the range with the cells that you want to get unmerged.
  2. With the chosen cells, press Command + F (for Mac) or hold the CTRL + F (for Windows). It will open the dialog box ‘Find and Replace.’
  3. In the dialog box of Find and Replace, tap on the Format button. Whenever you can’t see the Format button, make the Format button appear by clicking on the Options button. 
  4. Look for the dialog box Fin Format and click on the tab’ alignment.                                                                                                                             
  5. Click on the option ‘Merge cells.’                                                                                                                                                       
  6. Tap on OK.
  7. Click on the button Find All that is in the dialog box Find and Replace. It will look for all of the merged cells. Right below the dialog box, it will show all of these cells’ cell references.       
  8. Hold on to the Control key. Then, manually choose the cells you want to unmerge.                   
  9. In the ribbon, click on the option ‘Merge & Center’ so you can have all these cells unmerged. You may also use any of the mentioned methods.

With this method, you can unmerge cells selectively as you keep some of the merged cells intact.

Ways you can search for Excel’s merged cells.

You may now be aware that you need to avoid having merged cells in your Excel worksheets.

However, the spreadsheet that you have created was poorly structured, that you want to try to have it converted into something useful for you.

Yet, you’re having problems as the sheet has so many merged cells that you may not know.

Now, how can you look for the merged cells that your worksheet has?

Always remember that the merging of cells is related to alignment that is a part of formatting.

With Excel Find, you can search according to Format.

Here is the way you can do this:

  1. Open the Find dialog box by pressing Ctrl + F. You may also check out the Home tab > Editing group and click on Find & Select > Find.
  2. In the dialog box Find and Replace, click on the Options button, and then tap on Format.                     
  3. Switch to the tab Alignment tab. Under the Text control, choose the check box Merge cells, and click on OK.                                       
  4. By this time, you can click on either of the following:
    • Find All so you can get the list of every merged cell.
    • Find Next so you can to the next merged cell.

Once you click on one of the items that you find, Excel will choose the worksheet’s corresponding merged cell:

Tip. Whenever you feel curious if there are merged cells in a specified range, choose that range and check out the button Merge & Center.

Whenever the button gets highlighted, it only means that the selected field has a minimum of one merged cell.

Ways You Can Look for Excel’s Merged Cells

Most of the time, you get the information on the sheet with many merged cells.

Whenever there are specific tasks that you need to do, you need to become aware of where every merged cell is located.

Make sure to follow these steps whenever you want to look for all of the excel sheet’s merged cells:

    • Open the dialogue box by pressing CTRL+F. 
    • Tap on Option

    • Choose Format

    • Check out the Alignment tab.
    • Choose the merged cell.

    • Hit on Find All so you can have the list of addresses of every merged cell.

  • Tap on Find Next so you can horizontally move from a merged cell to another.

Here is where you can see all of the merged cells.

Unmerge the Cells So That the Blank Cells will become full of the Original Value

In Excel, when it comes to unmerging cells, the value of the merged cell can get allocated to the cell in the top left after the cells get unmerged.

Because of that, whenever there is value in a block of three merged cells, and you unmerge it, the value will be at the top cell, and the other two cells will remain empty.

Here is an example where we had the cells unmerged.

Here, you can see the rest as empty, and the value is retained at the only one at the top cell.

If you want to get the value of all of these cells, you can easily do this with some workarounds.

Here are the steps that you can use so that the cells will unmerge and become filled with their original value:

  1. Choose the cells with merged cells.
  2. Click on the Home Tab
  3. In the group Alignment, choose ‘Merge and Center.’                                                                               
  4. By this time, you get the unmerged cells and see that some of the previously merged cells are now blank.                         
  5. Click on the Home tab.                                                                                                                                                           
  6. Check out the group Editing and tap on ‘Find and Select.’                                                                                                                                                                 
  7. Click on the option Go To Special that will open the dialog box Go To Special.                                                                                                       
  8. Choose ‘Blanks’ in the dialog box Go-To Special.                                                                                                                                                 
  9. Click on OK

With these steps, you can only select the blanks cells that initially were included in the merged cells. However, after they get unmerged, these cells become blank.

Follow these steps so you can have the original value copied into the blank cells:

  1. In your chosen blank cells, highlight the active cell.                                                                                                           
  2. Have the = or equal to sign pressed to enter the sign similar to in the active cell.
  3. Press the arrow key to pick up the cell reference of the cell right on top of the active cell.                                       
  4. Hold on to the CTRL-key. Next, press Enter so that the same formula will get entered in the blank cells that you chose so that each empty cell would refer to the cell above it. From there, it will select the value.

Here, you will see that every blank cell has a value similar to the one it previously had.

By this time, you can get rid of any formulas when converting the formulas into static values.

Unmerge the cells to fill every cell with the original value

Before, we were able to unmerge column A cells that have a region.

After they get unmerged, we only get the data that is in the uppermost cells.

You want to unmerge it and make every unmerged cell get the original value. However, excel is not doing it for you.

Make sure to follow these steps if you want the cells to become filled with the original values:

    • In excel, choose all of the unmerged cells that you want to become full of the original data. https://www.exceltip.com/wp-content/uploads/2019/08/338.png
    • Press on Ctrl+G, and the option Go To will appear. Click on the Special button after. https://www.exceltip.com/wp-content/uploads/2019/08/424.png
      • You can do the same thing when you go to the home button Find & Select. https://www.exceltip.com/wp-content/uploads/2019/08/517.png
    • Click on special afterward.
    • Click on the “blank” option, then choose the OK button.
    • By this time, all of the blank cells are selected. Type = and then click on the arrow up button. In this example, it will choose A2, and the formula bar will have = A2
  • Click on CTRL+Enter to make every cell full of the original value.                                                                                                                                   

Ways you can unmerge cells and copy the original value to every unmerged cell

Improve your dataset’s structure by unmerging the cells and filling every unmerged cell with the original cell’s value, as seen in this screenshot:

Follow these steps so you can unmerge the cells and have them filled with duplicate values.

  1. Choose your table or the columns with the merged cells. On the Home tab, click on the button Merge & Center. Doing this will make all the merged cells split with the unmerged cells in the upper-left full of data.
  2. Next, choose the whole table and go to the Home tab > Editing group, click on Find & Select and tap on Go To Special. 
  3. In the dialog window Go To Special, have the Blanks option ticked off and choose OK:                                                             
  4. Once you select all of the blank cells, tap the equal sign (=) and then have the Up Arrow key pressed. Doing this will make a simple formula that can fill the first blank cell with the value from the cell above: 
  5. Suppose you want to fill every unmerged cell that is blank, press Ctrl + Enter. With this, you can have the formula entered in the chosen cells.

With this, every blank cell will become full of the value that came from the cell that got previously merged:

Tip. If you want your dataset to only have values, use Paste Special > Values so that the formulas will replace the results.

These are just some of the ways people can use if they want to unmerge any cell in Excel.

We do hope that our tutorial has helped you in unmerging Excel cells. Thanks for reading this, and we hope you’ll check out our next blog!

Become Aware of How To Unmerge Cells in Excel with ExcelMaster

Make yourself an Excel expert with us! Allow yourself to become sufficiently knowledgeable on how to unmerge cells in Excel with ExcelMaster NOW!

Leave a Comment