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.
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.
- 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.
Merge Cells Button Is Greyed Out in Excel: 3 Possible Solutions
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).
- Next, Excel displays a confirmation pop-up saying it’ll convert the Table to a normal range. Click on YES to confirm the application.
- After that, select the desired cells, after that, Go to Home > Alignment section > Select Merge & Center.
- 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.
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. Let’s follow the instructions below to merge cells in the Excel table!
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.
- 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.
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 are given below to merge cells in Excel using VBA code.
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
- 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.
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.
Download Practice Workbook
You can download the practice workbook from here.
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. 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.
