How to Create a Table with Merged Cells in Excel (4 Easy Methods)

One of the major drawbacks of an Excel table is that you can not create a table with merged cells. Let’s assume you have a data set that consists of multiple merged cells like below.

How to Create a Table with Merged Cells in Excel

D3, E3, and F3, G3 are merged. If you want to create a table with these cells, you will get the following result.

How to Create a Table with Merged Cells in Excel

Excel has unmerged the merged cells and created additional column headers. We have to follow a technique that will allow us to create a table consisting of merged cells. Note that this will not be an actual excel table, but the data formatting will be like an excel table.

Method 1 – Use of Center Across Selection Option to Merge Cell While Creating a Table

In this method, we need to transform the Table into a normal range. Then we will apply a special alignment option.

Steps:

Select any cell of the Table and go to the Table Design
In the Tools group, click on Convert to Range.

Use of Center Across Selection Option to Merge Cell While Creating a Table

In the dialog box, click on Yes. This will convert the Table into a normal range.

Use of Center Across Selection Option to Merge Cell While Creating a Table

Erase the texts of cells E3 and E4.

Use of Center Across Selection Option to Merge Cell While Creating a Table

Select D3 and E3 cells, in the Alignment group, click on the arrow icon to open all the commands of the alignment group.

The Format Cells dialog box will pop up. From the drop-down menu under the Horizontal option, select the Center Across Selection option and click OK.

Use of Center Across Selection Option to Merge Cell While Creating a Table

You will see that the “Mid Term” text has come in the middle of D3 and E3 cells and makes it look like they are merged. However, the text is in D3 while E3 is empty.

Follow the same steps for cells F3 and G3.

Use of Center Across Selection Option to Merge Cell While Creating a Table

This Table looks exactly like an excel table but lacks features of excel tables such as Sorting and Filtering.
If your merged cells are not in the header columns (on the top row), you can convert it into an excel table.
A limitation of this method is that, it is only applicable when the cells need to be horizontally merged. For vertically merged cells, we have to follow other methods.

Method 2 – Application of Convert to Range Feature to Generate a Table with Merged Cells

This method is applicable for both vertically and horizontally merged cells. We are using another sample data set with multiple vertically merged cells for illustration.

In the above data set, B4–B5, B6–B7, B8–B9, B10–B11, C4–C5, C6–C7, C8–C9, and C10–C11 are merged. We want to create a table from this dataset. To do that, follow the steps below.

Steps:

Select all the dataset cells and convert them to a table.

Application of Convert to Range Feature to Generate a Table with Merged Cells

We can see that Excel has automatically unmerged the merged cells with the Merge & Center option being unavailable. To merge those cells again, we have to convert the Table into a range.
Select any cell of the Table and go to the Table Design
In the Tools group, click on Convert to Range.

Application of Convert to Range Feature to Generate a Table with Merged Cells

In the dialog box, select Yes.

Application of Convert to Range Feature to Generate a Table with Merged Cells

The Table will be converted into a range.

Application of Convert to Range Feature to Generate a Table with Merged Cells To merge those unmerged cells, select the cells B4 and B5 and click on the Merge & Center option.

The cells will be merged.

Application of Convert to Range Feature to Generate a Table with Merged Cells

Follow the same steps for the remaining cells to get the final result as shown below.

Application of Convert to Range Feature to Generate a Table with Merged Cells

Method 3 – Using Context Menu Option to Create a Table with Merged Cells

There is an alternative method to convert a table to a range. After converting the Table to a range, the rest of the procedure is the same as method 2.

Steps:

Convert the dataset into a table by following the same steps of method 2.

Select any cells of the Table and right-click on the Mouse. From the Context menu, select Table > Convert to Range.

Using Context Menu Option to Create a Table with Merged Cells

In the pop-up box, select Yes.

Using Context Menu Option to Create a Table with Merged Cells

On clicking Yes, the Table will be converted into a range.

Using Context Menu Option to Create a Table with Merged Cells

Follow the same steps of Method 2 to merge the cells.

Using Context Menu Option to Create a Table with Merged Cells

Method 4 – Run a VBA Code to Create a Table with Merged Cells

We can also run VBA code to convert an excel table to a range and merge the unmerged cells by using the same procedures mentioned in the previous methods.

Steps:

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

Run a VBA Code to Create a Table with Merged Cells

Enter the following code into 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

Run a VBA Code to Create a Table with Merged Cells

Press the F5 key to run the macro. After running the code, you will see that the Table has been converted to a range.
Use the same procedure to merge the required cells to get the final result.

Run a VBA Code to Create a Table with Merged Cells