How to unmerge cells in excel - A DataSherpas Quick Tip

A DataSherpas Quick Tip.

Merging cells in Excel gives us great flexibility when it comes to formatting data, providing descriptive headings and titles to a group of cells or providing useful help information for other users of our Excel Spreadsheet.

But what if we want to unmerge those merged cells to change the layout or formatting of the spreadhseet? How do we unmerge the cells and how do we unmerge the cells without losing any data contained in the merged cells?

We cover all of these points and more in this quick tip article.

Unmerging cells in an Excel Spreasheet

We start on the premise you have some merged cells in your Excel Spreadsheet.

Now you want to unmerge those cells.

Simply click your mouse in the merged cell.

Then go to the “Merge & Centre” shortcut – click the arrow to the right to see more options.

Then choose unmerge.

The cells that were merged are now unmerged and all of the text in the merged cell is now in the left most cell of the unmerged cells.

Unmerging cells using the above process works in exactly the same way for cells that are merged vertically, horizontally or both.

Alternative ways to unmerge cells.

You can also unmerge cells in Excel by clicking your right mouse button over the merged cell, choose “Format Cells” from the options:

then from the dialogue box choose the “Alignment” tab and then uncheck the “Merge Cells” tick box.

How to unmerge cells in Excel without losing data.

You may have seen in the previous examples that if you unmerge cells in Excel the data in the merged cell is retained in the upper left most cell of the unmerged cells.

Example: The image below shows an excel worksheet with multiple cells merged both horizontally and vertically.

When the cell is umerged all of the text is retained in the top, left most cell of the unmerged cells.

You will see in the example above that even if the text in the merged cells is split over multiple lines, the line breaks are still retained. Just expand the height of the row and you will see the multiple lines in one cell.

Summary

Unmerging cells in excel is a very easy process and all data in the merged cells is retained after unmerging so you won’t lose your data.

We hope you found this quick tip article useful. Please use the comments below or our contact form if you have any questions about this article or need any more information to help you.

Other resources

Notes

The screen shots in this quick tip article and the screen recording in the video were produced using Microsoft Excel for Mac – version 16.56. We expect there to be small differences between Excel versions but the overall process should be almost identical.