How to Unmerge Cells in Excel

Unmerged cells

If you have ever created a table in Excel, you may know that sometimes you need more space to add, for example, a proper title. That’s where cell merging comes into play.

This feature allows you to join multiple worksheet cells into one cell. It may come in handy when you need to use a bigger text block as a header for the content spread across several columns or rows.

However, sometimes merging cells in Excel can be rather troublesome. This step-by-step guide will teach you how to easily unmerge cells in Excel.

Why You Should Be Careful with Merging Cells in Excel

Let’s say you’ve made a table containing some merged cells, and now you need to move it to a different place on a worksheet. If you drag and drop its contents, you might come across this message:

Excel error 1

Alternatively, you may receive this warning message:

Excel error 2

The thing is that merging cells in Excel has an adverse effect on its functionality and causes a lot of problems in performing even the simplest actions.

Here is an illustrative list of the other functions that can be affected by merging cells:

  • Sorting and filtering data
  • Creating PivotTables
  • Applying formulas
  • Running macros
  • Using a screen reader

If you have already recognized the need to get rid of all your merged cells, one of the methods below can help.

How to Unmerge Excel Cells Using the Toolbar

This way of unmerging cells in Excel is as easy as ABC. Here’s how you do it:

1. Select the cells you want to unmerge.

  • If you want to select one cell, just click it.
select one cell
  • If you want to select a cell range, click the first cell, press the left mouse button, and drag the cursor over the other cells you need. Alternatively, simply press and hold down the Shift key while using the arrow keys to extend the selection.
Select a cell range
  • If you want to select non-adjacent cells, click the first cell, press and hold down the Ctrl key, and select the additional cells.
Select non-adjacent cells
  • If you want to select the entire worksheet, press the Ctrl + A combination. We’ve used this option to unmerge all merged cells in our table.
Select the entire worksheet

2. Click the Home tab.

3. In the “Alignment” section of the toolbar, click the drop-down arrow next to the “Merge & Center” button.

4. In the drop-down menu that appears, click “Unmerge cells.”

Click "Unmerge Cells"

Note: you can click the “Merge & Center” button directly—the result will be the same.

Voilà! Now all the cells we selected are unmerged. Here’s what the final result looks like:

Unmerged cells

How to Unmerge Excel Cells Using a Keyboard Shortcut

Shortcuts always serve as real time-savers, and this one will provide you with the quickest way to unmerge your cells.

1. Select one or more cells you want to unmerge.

Select the cells you want to unmerge

2. Press the Alt + H + M + C combination quickly in a row.

Hit the keyboard shortcut

You’re done! See how quick that was?

How to Add the Unmerge Option to the Quick Access Toolbar

Do you often face the necessity of unmerging your cells and want the “Merge & Center” button to be on hand all the time? If so, there’s a helpful lifehack for you!

You can add the “Merge & Center” button to the Quick Access Toolbar and have access to it no matter what tab you’re on at the moment. Just follow the steps below:

1. Click the Home tab.

2. In the “Alignment” section of the toolbar, right-click on the “Merge & Center” button.

3. In the drop-down list, click “Add to Quick Access Toolbar.”

Click "Add" to quick access

This will add the “Merge & Center” button to the command line above the main ribbon tabs, and you will be able to click it whenever you want to unmerge your cells.

Add Merge Center button

How to Find All Merged Cells Within a Range

If you work with a huge amount of data, it could be really tedious to do away with all merged cells in the worksheet. The task becomes twice as difficult when you receive an Excel file created by somebody else and realize that merged cells are totally indistinguishable from unmerged ones.

No worries—it’s an easy-to-solve problem. Using the “Find and Replace” feature, you can quickly find all merged cells within a selected range.

1. Select the range where you want to look for the merged cells.

2. With the cells selected, click the Home tab.

3. In the “Editing” group, click the “Find & Select” button.

4. Click “Replace” to open the “Find and Replace” dialog box.

Select "Find and Replace"

Note: you can also do this by pressing the Ctrl + F keyboard shortcut.

5. In the “Find and Replace” dialog box, click the “Option” button. This will expand the dialog and show you more options.

Click "Options" 1

6. Next to the “Find what” field, click the “Format…” button.

Select "Format"

7. In the “Find Format” dialog box that appears, switch to the Alignment tab.

8. Check the “Merge cells” box under “Text control.”

9. Click “OK.”

Check the merge cells box

10. You will be brought back to the “Find and Replace” dialog box. Click “Find All.”

Click "Find All"

All is set! Now you can see the list of all your merged cells and easily decide their fate.

How to Copy the Original Value to the Empty Unmerged Cells

Up to this point, you may have already noticed that when you unmerge cells, all the data go to the top-left cell while the other cells are left empty. It doesn’t look very nice, does it?

Copy the original value to the empty unmerged cells

Fortunately, with just a few simple steps, you can fill in all the blanks with very little effort.

Step 1. Find All Empty Cells

First, let’s detect all cells that don’t contain any data.

1. Select the range where there are some empty cells.

2. Click the Home tab.

3. In the “Editing” group, click the “Find & Select” button.

4. Click “Go To Special…”

Select "Go to Special"

5. In the “Go To Special” dialog box, choose “Blanks.”

6. Click “OK.”

Choose blanks

Awesome! Now all empty cells within a chosen range are selected.

How to select empty cells

Step 2. Fill Each Empty Cell with the Original Value

It’s high time we put our table in order and set all the data straight.

7. When all the empty cells are selected, type the equal sign (=) and press the Up Arrow key (↑). This formula will copy the data from the cell above to the active cell.

Fill each empty cell with the original value

9. Hold down the Ctrl key and press Enter. Now all empty cells will be filled with the data from the cells above.

Fill each empty cell with the original value 2

Long story short, merging cells in Excel is a double-edged sword that can be useful and irritating at the same time.

However, now you have all the knowledge necessary to keep it under control and quickly unmerge cells whenever you want.