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.
How to unmerge cells in Excel
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.
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;
3. Click on the Merge & Center drop down menu and select Unmerge Cells. Alternatively, you can click directly on Merge & Center button.
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.
If you prefer video, this short tutorial from Microsoft shows how to merge and unmerge cells in Excel.
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 separate 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 in the bottom right corner. Alternatively, you can right-click in the cell and select Format Cells…
3. In the Alignment tab unckeck the Merge cells option and click OK.
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 selecting Unmerge Cells.
So, the shortcut to unmerge cells is 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.
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.
How to unmerge cells in Excel and fill cells with original values
Oftentimes, 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:
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, SUMIF, 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 separate cells.
2. Select your data again, navigate to the Home tab > Editing group, Find & Select, and click on Go To Special…
3. Select Blanks from the Go To Special dialog box and click OK.
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 into the blank cell.
5. Press CTRL + Enter to fill all unmerged blank cells. Since you want to apply the same formula to all these cells, you need to finish the formula with CTRL + Enter rather than Enter.
And there you have it. Now each blank cell contains the value from the originally merged cell.
How to find merged cells in Excel
Whenever you are preparing an 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 that 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.
2. In the Find and Replace dialog box, click the Options button, and then on Format.
3. Go to the Alignment tab, select the Merge cells check box under Text control, and click OK.
4. Now you can click on either Find Next or Find All.
- 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.
Extra tip: You can adjust your search criteria to find all merged cells in the entire Workbook, not just the currently 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.
