How to unmerge cells in Excel

This step-by-step tutorial shows how to unmerge cells in Excel, how to find all merged cells from a worksheet, and how to fill unmerged cells with the original content of the merged cells.

Merged cells provide flexibility if you want to create a nice and structured presentation, but when it comes to usability, in most cases it will break your file rather than improve it. For example, if there are merged cells in your columns you cannot sort data. Also, using merged cells in formulas can be a pain because Excel actually sees it as a range of cells rather than a single cell.

But before we dive into merging and unmerging cells, I strongly recommend that you download the exercise file. It allows you to practice and follow along with the exact file I have used when writing this guide.

How to unmerge cells in Excel

Unmerging cells in Excel is very easy and in most cases straightforward. Here are the easy steps:

1. Select the cell (or multiple cells) that you want to unmerge;

2. Navigate to the Home tab and look for the Alignment group;

Unmerge cells in Excel

3. Click on the Merge & Center drop down menu and select Unmerge Cells. Alternatively, you can click directly on Merge & Center button.

To split the current cell into multiple cells click Merge & Center and select Unmerge Cells.

Regardless of the method you choose the result will be the same: the merged cells will be split into multiple cells and the original value will always be positioned in the upper left corner of the initially merged range. The rest of the cells will be blank.

Original content of merged cells is positioned in the upper left corner

If you prefer video, this short tutorial from Microsoft shows how to merge and unmerge cells.

If you are looking to unmerge cells in Excel for iOS or Excel for macOS please read this article from Microsoft.

Unmerge cells using Format Cells dialog box

Since Merged cells are mostly used for styling your spreadsheet, Microsoft included the option to Merge and Unmerge cells directly from the Format Cells menu. To unmerge cells using this method follow these steps:

1. Select the cell you want to unmerge;

2. Navigate to the Home tab, look for the Alignment group and click on the small arrow on the bottom right corner. Alternatively, you can Right-click in the cell and select Format Cells

Select the Alignment menu by clicking the ower right arrow

3. In the Alignment tab unckeck the Merge cells option and click OK.

Uncheck the Merge cell option and click OK to unmerge the selected cells.
Uncheck the Merge cell option

Unmerge cells in Excel shortcut

Unfortunately there is no pre-built shortcut to Merge and Unmerge cells. However, it is possible to access the Merge cell menu using the Alt key along with 3 other keys. In order to Unmerge cells press Alt, followed by H, M and U.

Pressing Alt will trigger the keyboard menu selection. By pressing H you will navigate to the Home tab. Pressing the letter M will open the Merge cells menu, while U is used to select Unmerge Cells.

So, the shortcut to unmerge cells is Alt -> H -> M -> U.

Unmerge cells using Alt, H, M, U.

Alternatively, you can use Alt -> H -> M -> C to merge any selected cells.

How to unmerge all merged cells in a Worksheet

When you have a large spreadsheet the idea of splitting all merged cells may seem intimidating, but this can be achieved easily in just a few clicks.

First, you need to select the entire sheet. You can do this by pressing the triangle in the upper left corner of your worksheet. Alternatively, you can press CTRL + A to select the entire sheet.

Select entire Excel worksheet.

Once you have the entire sheet selected, check the Merge & Center button from your Alignment tab. If it’s highlighted this means there are merged cells within your selection. Then, simply press the Merge & Center or select Unmerge Cells from the drop down menu as shown before.

If Merge & Center is highlighted you can press it to unmerge all cells.

How to unmerge cells in Excel and fill cells with original values

Often times, when doing data cleansing you will need to unmerge cells and fill the unmerged cells with the same value as the original cell. The image below shows what we are after:

Unmerge cells and fill all unmerged cells with original value.

This is very useful when you are working with files structured as databases, because it will allow you to properly use a lot of functions like VLOOKUP, SUMIFS or SUMPRODUCT.

As you remember, I mentioned that when you unmerge cells the content will be moved to the upper left cell of the merged range of cells. This means that the blank cells will always be underneath our original content, which makes it easy for us to copy data using a simple formula.

To unmerge cells and fill down with the same values, just follow these step by step instructions:

1. Select the column with the merged cells. You can also select the entire table if there are merged cells in multiple columns. Click the Merge & Center button to unmerge all cells.

2. Select your data again, navigate to the Home tab > Editing group, Find & Select, and click on Go To Special…

Navigate to Home tab / Editing group / Find & Select / Go To Special

3. Select Blanks from the Go To Special dialog box and click OK.

Select blank cells in Excel using Go To Special dialog box

4. While the blank cells are still selected, we will enter the formula by typing the equal sign (=), followed by the up arrow key (↑). This simple formula will take the value from the cell above and copy it in the blank cell.

A simple formula to fill blank cells with values from unmerged cells.

5. Press CTRL + Enter to fill all unmerged blank cells. Since you want to apply the same formula to all your blank cells you need to finish the formula with CTRL + Enter rather then Enter.

Blank cells are filled with the values from the originally merged cells.

And there you have it. Now each blank cell contains the the value from the originally merged cell.

How to find merged cells in Excel

Whenever you are preparing and Excel file it is best to avoid using unnecessary merged cells. However, when working in a large company there is a high chance that you will receive files which contain merged cells from other colleagues. Moreover, these cells may not be easy to spot, especially in large files with multiple sheets.

So, how do you find merged cells which are not easy to spot? There is a quick method using the Find dialog box and I will guide you through the entire process.

1. Open the Find dialog box. You can do this using the CTRL + F shortcut or by navigating to Home -> Editing -> Find & Select -> Find.

Open Find dialog box in Excel.

2. In the Find and Replace dialog box, click the Options button, and then on Format.

Open Format dialog.

3. Go to the Alignment tab, select the Merge cells check box under Text control, and click OK.

Select Merge cells and click OK.

4. Now you can click on either Find Next or Find All.

All merged cells in the active worksheet are displayed.
  • Find Next will select the next merged cell it founds and navigates through each cell corresponding to your find criteria.
  • Find All will display a list of all merged cells which can then be selected individually. Excel will automatically select the corresponding cell.
Select each merged cell from the worksheet.

Extra tip: You can adjust your search criteria to find all merged cells in the entire Workbook, not just the current active sheet. To do so, simply change the Within criteria from Sheet to Workbook.

And that covers all the essentials of merging and unmerging cells in Excel. If you want to continue to learn Excel make sure to read my articles on Excel formulas. It’s a great resource to understand how to use Excel on a daily basis to solve real life tasks.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.