[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.

Dataset-Excel Unable to Merge Cells in Table

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


Download Excel Workbook


🔺 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 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 unprotect-Excel Unable to Merge Cells in Table


🔼 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.

Unshared worksheet-Excel Unable to Merge Cells in Table


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

In the following section, we try to resolve Excel unable to merge cells in Table phenomenon mainly considering our dataset is in Excel Table format. And 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).

Convert to Range-Excel Unable to Merge Cells in Table

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

Confirmation

➤ Clicking YES transforms Excel Table into a normal range. Now, Highlight 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.

no table design tab

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

Merge & Center

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

confirmation

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

Final result-Excel Unable to Merge Cells in Table

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

Read More: How to Merge and Center Cells in Excel (3 Easy Methods)


Similar Readings


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.

context menu-Excel Unable to Merge Cells in Table

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

Confirmation

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

Final Result

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

Read More: How to Merge Cells in Excel with Data (3 Ways)


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.

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

Module

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

macro

In the macro, we declare 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 conversion of Excel Table to a normal range.

No table design tab

➤ 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.

Final result

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

Read More: VBA to Merge Cells in Excel (9 Methods)


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo