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:

• Merge and Center: When we choose to use this particular tool, Excel merges all the selected cells (which must be adjacent) and arrange the data into the center. It only keeps the data (or text) from the upper-leftmost cell of the selected range. The data present in all other selected cells are deleted after merging them into a larger cell.
• Merge Across: Excel merges the selected cells based on their respective rows when we choose to use this tool. It only merges the cells of different rows individually, retaining the data from each left-most selected cell.
• Merge Cells: When we choose to use this tool, Excel merges the selected cells into one larger cell, whether vertical, horizontal, or both. Unlike 'Merge & Center', this option does not align the data into the center. However, it also preserves the data of the upper-leftmost cell only.
• Unmerge Cells: Excel unmerges the selected merged cell, splitting the respective cells back to their original or default structure when we use this tool.

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:

• First, we need to select or highlight all the desired cells to merge. We must select at least two adjacent cells to merge.
• After selecting the effective cells in the sheet, we must navigate the Home tab, click the drop-down arrow next to the 'Merge & Center' and select the 'Merge Cells' option from the list. This will instantly merge the selected cells into one larger cell.
  

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:

• First, we need to go to the Home tab and click the drop-down icon next to 'Merge & Center' under the section 'Alignment'.
  
• Next, we must press the right-click button via a mouse on the 'Merge Cells' option from the drop-down list.
  
• We must click on the 'Add to Quick Access Toolbar' option in the next step.
  
  This will immediately insert/ add a shortcut of the respective tool on the quick access toolbar.
  
  An advantage of adding the Merge Cells shortcut in the QAT is that we now get another keyboard shortcut (hotkey) based on the shortcut position on QAT. When we press the Alt key on the keyboard, Excel will display the associated key with the added shortcut or tool. In our case, it shows the key '5'.
  
  Thus, we can also access the 'Merge Cells' command by pressing the hotkey shortcut 'Alt + 5' in our case. However, we must have selected the cells to merge before using the 'Merge Cells' shortcut.

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:

• First, we need to select the desired cells to be merged. Like other methods, the Format Cells window is also used to merge contiguous cells only. Therefore, we must select adjacent cells accordingly.
• After selecting the effective cells, we need to open the 'Format Cells window'. There are multiple ways to open the Format Cells window. However, the quickest method to launch the Format Cells window is to press the keyboard shortcut 'Ctrl + 1'. But, it is essential to note that we must press the numeric key '1' only from the keyboard area. Pressing the key '1' from the number pad will not work.
• After the Format Cells window is displayed, we must navigate the Alignment We need to select the checkbox associated with the 'Merge Cells' option under the 'Text Control' section, as shown below:
  
• Lastly, we must click the OK button, and the selected cells will be merged immediately in the sheet.

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:

• First, we need to right-click on the Sheet name from the Sheet tab to which we need to merge desired cells. We must click the 'View Code' option from the list.
  
• In the Editor window, we must enter the following codes:

In the above code, we have given the range A1:D1 to merge. However, we can change the range as per our requirements.

• Once the code is entered, we must execute it by pressing the F5 function key on the keyboard. This will immediately merge the given range of cells into one larger cell. We can close the VBA editor after executing the VBA codes.
  

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:

• First, we need to select a desired merged cell to unmerge. We can select the entire sheet to unmerge several merged cells in the sheet.
• Next, we need to navigate the Home tab > Clear under Editing > Clear Formats.
  

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:

• When we try merging cells inside the Excel Table: Excel does not allow us to merge cells inside an Excel table, so there is no solution to this problem.
• When we try merging cells inside the Protected Sheet: If the sheet is protected, cells inside the respective sheet cannot be merged. In the protected mode, many other formatting options also do not work. However, there is a simple solution to this problem. We can usually navigate the Review tab and click the 'Unprotect Sheet' button.
  

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

• It is always recommended to limit the use of the Merge Cells command in a sheet. It should only be used for presentation purposes, such as to adjust the formatting of headings and cell titles.
• Once the cells are merged as one in an Excel sheet, it becomes difficult to sort the data in respective columns, be it ascending or descending.
• It is always recommended to have a backup of the original file before using the Merge Cells command. Since the data is not merged, we may accidentally lose data in some