Question: When I use Excel "Format as Table" to format a range, the Merge & Center button is greyed out and becomes inactive and I can no longer merge cells.
Answer: This is because the cells are within an Excel Table format, and we are unable to merge cells within a Table format.
However, there is an alignment format you can use to give Table formatted cells the look of a merged cell.
Why Can't I Merge Cells in an Excel Table?
In the example below I’m wanting to merge the text ‘Status TBC’ across two cells. However, the Merge and Centre button is greyed out and no longer available.
In this blog post I’m going to give you a work-around for when the Merge & Center button is greyed out, and cover steps that you can take to give your Table cells the look of a merged cell.
1. Convert the Table to a Range
The first thing I need to do is remove the Table functionality from my data.
Warning: the following steps will leave the formatting on your Table but will remove all Table functionality so please proceed with care.
1. To do this select any cell within the Table area and then click the Table Design tab.
2. Click the Convert to Range button.
3. Excel will ask ‘Do you want to convert the table to a normal range. Click Yes.
4. Your Table will now be converted back to a normal range. The formatting will remain, but all other Table features will be removed.
2. Apply Center Across Selection
Now that the Table has been removed you will now be able to use an alignment setting called Center Across Selection. This format allows us to give the cells a merged look.
Note: the next steps will work for data inside the Table, but not for Table headings. Please see Merging Table headings doesn’t work below.
1. First select over the cells that you would like to merge. In the example below I have selected over cells G9 and H9.
2. Click the small arrow in the bottom right corner of the Alignment group.
3. This will open the Format Cells dialogue box with the Alignment tab settings displayed.
4. From the Text alignment, Horizontal options box select Center Across Selection.
5. Click OK.
6. It will now appear that the cells have been merged.
7. Even though the cells look to be merged they still are separate. The text ‘Status TBC’ still resided in cell G9. The Center Across Selection option gives the look of a merged cell, without you needing to merge the cells.
3. Select the data and use Format as Table again
8. Now I will insert a Table over the data once more. To do this I will select the data and then from the Insert tab click Table.
9. Excel will ask if I wish to create a Table. Click OK.
10. A Table will be applied over the data once more and the cells formatted with Center Across Selection will still have the look of a merged cell.
Please note: if you will be using the Filter or Sort features within the Table format, content that has been formatted using Center Across Selection will be included in the column it was originally entered into.
Merging Table Headings doesn’t work
In my example below I have used the Centre Across Selection format to merge two column headings. The cell containing the text ‘Approval Status’ now has a merged look.
With the cell formatted using the Centre Across Selection I can insert a Table over the data.
However, as soon as I insert a Table over the data, Excel will give me a new column heading, ‘Column1’.
The merged cells with ‘Status TBC’ inside the Table remain with the merged look, but the ‘Approval Status’ is reset.
This is because each column within a Table requires a unique heading, therefore Centre Across Selection will not work for cells in the heading row.
To Sum up
Using this quick work around in Excel to give your formatted Table cells the look of merged cells.
Was this blog helpful? We'd love to know. If you're a beginner or if you use Excel every day please let us know if we helped in some way by leaving a Comment below.
If you enjoyed this and want to learn more about how to use Excel, have a look at our posts How to copy and paste visible cells only in Excel (excluding hidden rows and columns) and How to Sum only visible cells in Excel when using Filter.