[Fixed!] Merge Cells Button Is Greyed Out in Excel

This tutorial will demonstrate how to fix the merge cells button that is greyed out in Excel. One of the most general uses utilized by beginner excel users is merging cells. Merging cells is a common scenario when you might need to combine multiple cells of data in one single cell. But, sometimes merging cells along with data could be a little difficult. The main problem happens when the merge cells button is greyed out and becomes unable to use. In this article, we will try to fix the problem when fixing the merge cells button that is greyed out in excel.


Download Practice Workbook

You can download the practice workbook from here.


Reasons Behind the Problem Merge Cells Button Is Greyed Out in Excel

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

  • Cells Within the Excel Table: If the cells are part of a table then most likely the merge cells option won’t work on them. In this case, at first you have to convert the table into normal data and after that the merge cells option will work.
  • The 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.

Unprotecting Workbook to the Problem Merge Cells Greyed Out in Excel

  • The 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.

Unshare Workbook to the Problem Merge Cells Greyed Out in Excel


3 Possible Solutions to the Problem Merge Cells Button Greyed Out in Excel

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have Category in Column B, Product in Column C, and Unit Price in Column D. If you follow the steps correctly, you should learn how to enable the merge cells button if it is greyed out in excel. The steps are

1. Using Convert to Range Feature to Enable Merging Cells in Table

In this case, our goal is to enable the merge cells button by using the Convert to Range option. 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. The steps of this method are.

Steps:

  • First, select the entire dataset. Excel displays the Table Design tab along with the other tabs. Then, go to Table Design > Select Convert to Range (from the Tools section).

Using Merge and Center option to the Problem Merge Cells Greyed Out in Excel

  • Next, Excel displays a confirmation pop-up saying it’ll convert the Table to a normal range. Click on YES to confirm the application.

Confirmation to the Problem Merge Cells Greyed Out in Excel

  • After that, select the desired cells, after that, Go to Home > Alignment section > Select Merge & Center.

Using Mergr and Center Option to the Problem Merge Cells Greyed Out in Excel

  • Furthermore, selecting Merge & Center makes Excel show a warning that only the upper left value will remain after merging. Click on OK to get the desired result.

  • Afterward, you will get the result for the particular group. It is very important to remember that the merged cell will contain the value of the first cell like the below image.

  • Finally, if you repeat the whole process for all the groups, you will get the desired result.

Read More: [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)


Similar Readings


2. Use of Context Menu to Merge Cells in Table

Now, we want to enable the merge cells button by the use of the Context Menu. After this, the method will be similar to the first method in the second portion. The steps of this method are.

Steps:

  • First, select the entire data table and right-click on the table.

  • Second, the Context Menu will appear on the screen. From the Context Menu, choose the Table option > Select Convert to Range.

Right-click to the Problem Merge Cells Greyed Out in Excel

  • Third, Excel displays a confirmation window saying it’ll convert Excel Table to the normal range. Click on the YES option to apply the application.

  • Last, if you repeat the last step of the first method(select the desired cells>go to Home > Alignment section > Select Merge & Center), then you will get the desired result for every group like the below image.

Read More: Excel Data Validation Greyed Out (4 Reasons with Solutions)


3. Applying VBA Code

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. In this case, our goal is to enable the merge cells button by using the VBA code. The steps of this method are.

Steps:

  • First, press ALT+F11 to open Microsoft Visual Basic In the window, Go to the Insert tab (from the Toolbar) > Select Module (from the options).

  • Secondly, insert the following macro code 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

Inserting VBA to the Problem Merge Cells Greyed Out in Excel

  • Thirdly, 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 This indicates the confirmation of the conversion of the Excel Table to a normal range.

  • Lastly, if you repeat the last step of the first method(select the desired cells>go to Home > Alignment section > Select Merge & Center), then you will get the desired result for every group like the below image.

Read More: [Solved]: If Macro Settings Is Greyed out in Excel


Things to Remember

  • In all cases, if you merge the cells, the merged cell will show the result of the first cell.
  • Moreover, the first method is the easiest among all methods. In the case of the second and third methods follow a similar method as the first one with the slightest change.
  • Lastly, we recommend downloading the excel file and having a look while reading the article to have a better understanding.

Conclusion

Henceforth, follow the above-described possible solutions. Hopefully, these possible solutions will help you to merge cells greyed out in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


Tags:

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo