How to Merge cells in Microsoft Excel
Microsoft Excel, or Excel, is a powerful spreadsheet program developed by Microsoft as part of the Office suite. It enables users to record large amounts of data sets in multiple cells across different worksheets. Additionally, it allows users to adjust the formatting or structure of the recorded data to facilitate understanding of the data or enhance the overall presentation of the sheet. This is where Excel's Merge Cells feature comes into play.
This article discusses various step-by-step methods to merge cells in Microsoft Excel and manage the formatting of the associated data accordingly. Before discussing the methods to merge Excel cells, let us first introduce the Merge Cells feature with its definition.
What is the Merge Cells feature in Excel?
By default, content entered within Excel cells appears in the bottom area of the corresponding cells where text is left-aligned and numbers are right-aligned. However, we can change the alignment accordingly. Merge Cells is one of the inbuilt formatting features or tools in Excel.
By definition, "Merging cells (or merge cells) in Excel refers to joining or combining two or more adjacent cells within the sheet either horizontally, vertically, or in both ways, resulting in one larger cell."
Since the Merge Cells feature helps to create one larger cell by combining multiple selected cells, the choice of resizing the individual cells for the corresponding columns is removed. The larger cell created by merging cells is shown across several rows or/and columns. Also, the Merge Cells tool can only merge the selected cells but not their data. When we merge multiple cells as one, only the data/value from the upper-leftmost cell is preserved.
The following image displays a single merged cell after joining or combining a group of cells in the range B2:D5.
When merging cells in an Excel worksheet (or accessing the Merge tool), we typically get the following options:
Why do we need to merge cells in Excel?
We may need to merge cells in Excel primarily because of formatting and centering headings. When we need to insert the same title or label for the group of cells, rows, or columns, we can take advantage of Excel Merge Cells. The primary purpose of this feature/ tool in Excel is to make the data presentable and readable for the users.
For example purposes, we can see the following image that displays two merged cells in A1:B1 and C1:E1 used to represent the category of information for the respective columns.
The columns containing First and Last names are organized under the merged cell 'Name' in the above image. Similarly, another merged cell is created with the name 'Address' containing columns C, D, and E.
How to merge cells in Excel?
One of the great advantages of using Excel is that it offers various ways to perform any specific task within our sheet. Likewise, when merging cells in Excel, we get different methods. We can perform any of the following methods to learn how to merge or combine cells in an Excel worksheet:
Merge Cells using the Keyboard Shortcut
Another method to immediately merge the desired cells involves using keyboard combination keys. Although Excel assigns no specific shortcut keys to Merge Cells, the Alt key shortcut method works perfectly. Therefore, we can press the key combinations as per the desired actions after selecting the cells to be merged.
To use the above shortcut, we must press one key at a time in a series.
Merge Cells using the Ribbon
The Excel Ribbon is the most powerful area at the top of the Excel window that holds almost all the built-in tools or commands of Excel. It also includes a tool to help us merge the desired cells in the Excel sheet. This is the most common way to merge cells and involves the following steps:
Merge Cells using the Quick Access Toolbar
As the name suggests, Excel's quick access toolbar (QAT) helps users access any specific tool with a click. It is located on the top of the Excel window. Therefore, if we often need to use the Merge Cells tool in Excel, it is better to use it from the quick access toolbar. However, we must first add the 'Merge Cells' tool on the toolbar. We only need to add the tool or shortcut once, and it will be accessible every time we launch Excel in the future.
We must perform the following steps to add the 'Merge Cells' shortcut on the QAT:
Merge Cells using the Format Cells Window
In Excel, the Format Cells window (or dialog box) contains all the essential options required to adjust formatting in the sheet, including merging multiple cells into one larger cell. The window contains several tabs for different formatting use-cases. We can access the 'Merge Cells' toll under the 'Alignment' tab.
To merge cells in Microsoft Excel using the Format Cells window, we must perform the following steps:
Merge Cells using the Copy-Paste
If we have already merged cells in our worksheet and want the same merged cell (a bigger cell) in another area on the same sheet or others, we can copy it accordingly to create more merged cells. Copying the merged cells to another location creates merged cells of the same dimensions. However, we must ensure that the destination area (or paste area) does not contain existing merged cells.
We can use the keyboard shortcut 'Ctrl + C' to copy a merged cell. We can use the keyboard shortcut 'Ctrl + V' to paste the copied cell. We must ensure that the paste area does not contain data; otherwise, it will be replaced by the copied cells.
Merge Cells using the VBA
We can perform any task using the VBA in Excel. It is quite easy to merge cells using the basic commands in the VBA editor and execute them accordingly. We only need to input the range of cells we want to merge or make a larger cell.
Below are the steps to merge cells using the Excel's VBA:
In the above code, we have given the range A1:D1 to merge. However, we can change the range as per our requirements.
Unmerge Cells in Excel
Like merging cells, unmerging cells in Excel is also quick and easy. There are several ways we can unmerge cells similar to what we used to merge the cells. However, the best way to unmerge cells is to use the keyboard shortcut 'Alt + H + M + C',
We must first select the merged cell and then press each key from the shortcut key combination in a sequence (one by one). Besides, we can also unmerge cells using the Ribbon, Quick Access Toolbar, Format Cells window, Copy-pasting, VBA, etc.
Apart from these methods, there is one ultimate method to unmerge cells in Excel. It is mainly used when we have several merged cells to unmerge in different areas of the sheet. The method includes the following steps:
However, this method also removes other formatting features from the selected sheet.
Limitations of Merge Cells in Excel
Since there are certain limitations, the Merge Cells command does not work in specific scenarios. In such cases, we often see that the 'Merge Cells' button is not accessible through the drop-down list associated with the 'Merge & Center' command. It is greyed out, and one cannot click on it to use the respective feature.
Generally speaking, there are two common reasons why the Merge Cells command doesn't work in Excel or becomes unavailable (or greyed out). They are as follows:
After the sheet is unprotected, we can use any above-discussed methods to merge the desired cells into a larger cell. If the sheet is protected with a password and we don't know the associated password, it is nearly impossible to unprotect it. We cannot Merge Cells or use other formatting features in the corresponding Excel sheet in such a case.
Important Points to Remember