[Fix:] Excel Unable to Merge Cells in Table

In general, typical Excel datasets hold most of their entries in Excel Table. Excel unable to merge cells in Table is a common issue faced by users. As Excel Table doesn’t allow merging cells within it, we have to first convert an Excel Table to a simple range.

In this article, we describe Excel features as well as VBA macro to overcome the issue of Excel unable to merge cells in Table.

🔺 Reasons That Lead Cells Not to Merge in Excel Table

There are 3 probable reasons that lead to Excel unable to merge cells in Table. They are:

🔼 Cells within an Excel Table:

The most common reason for cells not to merge is that they are in an Excel Table. Excel Table doesn’t allow its cells to be merged. As a result, we have to convert the Table to a normal range to be able to apply cell merge.

🔼 Worksheet is Protected:

In case the user protects the working Excel worksheet, Excel doesn’t allow any users to merge the cells of a protected sheet. To unprotect the worksheet, Go to Review > Protect section > Unprotect Sheet. After unprotecting the worksheet, you can apply cell merge with ease.

🔼 Worksheet is Shared:

Shared worksheets also don’t support cell merge. To Unshare the worksheet, Go to Review > Protect section > Unshare Workbook. As we already unshare our workbook, Excel grayed out the option. Unless we apply Unshare to our workbook, we can always unshare a workbook using the Review tab.

How to Resolve Excel Unable to Merge Cells in Table: 3 Easy Ways

In the following section, we try to resolve Excel’s unable to merge cells in the Table phenomenon mainly considering our dataset is in Excel Table format. We first demonstrate ways that transform Excel Tables into the normal ranges. Then, it’ll be a piece of cake to merge desired cells.

Method 1: Using Convert to Range Feature to Enable Merging Cells in Table

Excel offers the Convert to Range option in the Table Design tab. If entries reside in an Excel Table, clicking on one of its cells triggers Excel to display the Table Design tab. In the Table Design tab, there are numerous options, and Convert to Range is one of them.

Step 1: Select the entire dataset. Excel displays the Table Design tab along with the other tabs. Go to Table Design > Select Convert to Range (from the Tools section).

Step 2: Excel displays a confirmation pop-up saying it’ll convert the Table to a normal range. Click on YES.

➤ Clicking YES transforms the Excel Table into a normal range. Now, Highlight the cells you want to merge, you see no Table Design tab along with other tabs. Excel not showing Table Design confirms the fact that you are working with a range.

Step 3: Select the desired cells, after that, Go to Home > Alignment section > Select Merge & Center.

➤ Selecting Merge & Center makes Excel show a warning that only the upper left value will remain after merging. Click on OK.

➤ Apply the Merge & Center operation to other cells and you see the final outcome as shown in the following picture.

From the above image, you can see it is able to merge cells after you convert the Excel Table to the normal range. Not offering to merge is one of the setbacks of Excel Tables.

Method 2: Able to Merge Cells in Table Using Context Menu

Similar to Method 1, we can execute Excel Table to normal range conversion using the Context Menu.

Step 1: Select the entire Table or click on any cell within the Table then Right-Click. The Context Menu appears. From the Context Menu, choose the Table option > Select Convert to Range.

Step 2: In a moment, Excel displays a confirmation window saying it’ll convert the Excel Table to the normal range. Click on YES.

Step 3: Repeat Step 3 of Method 1 and you get all the required cells merged as depicted in the below picture.

After executing Merge & Center, if you need to store entries in a Table, you can easily convert the restaurant range into an Excel Table.

Method 3: VBA Macro to Resolve Merging Cells Issue in Table

Excel VBA macros are very powerful considering their ability to achieve object-oriented results. A couple of lines of VBA macro can convert an Excel Table into a normal range and allow you to merge the cells. let’s follow the instructions below to use the VBA code to merge cells in Excel!

Step 1: Press ALT+F11 to open the Microsoft Visual Basic window. In the window, Go to the Insert tab (from the Toolbar) > Select Module (from the options).

Step 2: Paste the following macro in the Module.

Sub TableToRange()
Dim wrkSheet As Worksheet
Dim wrkList As ListObject
Set wrkSheet = ActiveWorkbook.ActiveSheet
For Each wrkList In wrkSheet.ListObjects
wrkList.Unlist
Next
End Sub

In the macro, we declare the wrkList variable as ListObject, as Table is considered as a ListObject. We assign active worksheet’s each ListObject to be Unlisted using Worksheet.Unlist command. The VBA FOR runs the loop.

Step 3: Use the F5 key to run the macro. After returning to the worksheet, you see no Table Design tab though you click on cells within the pre-existing Table range. This indicates the confirmation of the conversion of the Excel Table to a normal range.

➤ Follow Step 3 of Method 1 to execute the Merge & Center option for desired cells. The resultant image will be the same as symbolized in the below picture.

You can merge any number of cells after converting the Table into a normal range.

Download Excel Workbook

Conclusion

In this article, we demonstrate the Convert to Range feature and VBA macro to resolve Excel’s unable to merge cells in Table. Though merging cells within an Excel Table is not allowed, we can execute it after converting the Table into a normal range. Above-described methods convert Excel Table to normal range with ease afterward Excel’s Merge & Center feature does the job. Hope these methods fulfill your requirements. Comment if you have further inquiries or have anything to add.

<< Go Back to Excel Merge Cells | Merge in Excel | Learn Excel