When using worksheets, you may wish to visually merge many cells into one cell. For instance, while formatting the header region you may wish a single title to be centered across your whole table, or you may merge rows in a Gantt chart to display time across multiple cells, or you might merge cells where there is duplicate data.
Topics in this Article
- How to Merge Cells
- How to Undo Lost Data from Merge
- How to Unmerge Cells
- Formulas, Comments, and Data Combination Functions
How to Merge Cells
There are multiple ways to merge cells, for instance, by using the merge button in the toolbar, the Merge cells feature in the Format menu of the toolbar, or the merge keyboard shortcut (Ctrl + M).
For instance, you can select the two or more cells you want to merge and click on the merge button in the toolbar.
By default, clicking merge cells will merge the top-leftmost value across all selected cells. Also, if you merge cells with values in the cells, only the top-leftmost value will remain.
You can also choose to merge horizontally, which will merge the leftmost value in each row across all selected rows. Or you can merge vertically, merging the topmost value in each column down across all selected columns. For instance, you can merge from this:
|Merge all||Merge horizontally||Merge vertically|
When you try to merge cells where data will be removed, you will see a warning like the one below.
Below we merge two cells to allow for our workbook's title to be fully displayed. We could also adjust the cell wrapping but demonstrate how to merge cells.
How to Undo Lost Data from Merge
Merging cells may delete data from your worksheet. To return the data, you will have to undo the merge and then move the data before merging the cells again. To undo an action, either use the keyboard shortcut Ctrl + Z, select the Undo button in the Toolbar, or choose the Undo option in the Edit menu.
How to Unmerge Cells
Cells that have been merged can also be unmerged. However, unmerging cells does not restore the data previously in those cells.
There are multiple ways to unmerge cells. The easiest may be to select the cells you wish to unmerge and click the merge cell button in the toolbar. Though this button can be used to merge cells, it also recognizes already merged cells and unmerges them on click. Otherwise, you can use navigate to the Format menu and select Unmerge cells. Or click on the drop-down arrow next to the merge cells button and select Unmerge cells.
Unlike the undo animation, in this animation, when we unmerge two cells, the previous data in the merged cell is not restored. Use undo to restore data, and unmerge to pull apart cells you no longer wish to be merged.
Also, it is not possible to unmerge only a part of a merged range. However, you can unmerge all the cells and then remerge those cells you want to stay merged.
Formulas, Comments, and Data Combination Functions
Because merging cells only preserves the top-leftmost cell (or leftmost or topmost if merging horizontally or vertically), formulas that use values in merged cells may break. Any formulas impacted by the merge action will be pointed at new cells, or the values in the merged cells will need to be restored (using Undo) and cut and pasted into a different cell. See our Formula readjustment article for more details.
Similarly, when you merge cells, only the cell comments of the top-leftmost cell appear in the merged cell. The cell messages of all other merged cells can still be seen in the row channel and will restore if you unmerge the cells.
Data Combination Functions
Merging cells is a great way to format your data, particularly the data in the header region. For more advanced formatting techniques, you may also be interested in our articles on assigning formatting with formulas.