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

Representing data clearly and concisely is an essential skill in this data-driven world. As a result, representing data in a table has become crucial as it is one of the greatest ways of expressing data. In this article, we will learn how to create a table with merged cells in Excel.

How to Create a Table with Merged Cells in Excel: 4 Useful Methods

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

Here D3, E3, and F3, G3 are merged. Now, if you want to create a table with these cells, you will get results like this.

Here, notice that Excel has unmerged the merged cells by itself and created additional column headers. To solve the problem, we have to follow a technique that will allow us to create a table consisting of merged cells. Please note that this will not be precisely an excel table, but the data formatting will be like an excel table. Let’s explore our first method.

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

In this method, we first need to transform the Table into a normal range. Then we will apply a special alignment option. To do this follow the steps below.

Steps:

First, select any cell of the Table, and then go to the Table Design option.
Then in the Tools group, click on Convert to Range.

Now, in the dialogue box, click on Yes. This will convert the Table into a normal range.

Then we have to erase the texts of cells E3 and E4.

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

A dialogue box titled Format Cells will pop up. Now, from the drop-down menu under the Horizontal option, select the Center Across Selection option and click OK.

Now, you will see that the “Mid Term” text has come in the middle of D3 and E3 cells that look like they are merged. In fact, the text is only in D3 and E3 is empty.

Follow the same steps for cells F3 and G3. You will have the same result.

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.

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 have taken another data set with multiple vertically merged cells to know how this method works.

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

Steps:

First, select all the dataset cells and convert them to a table with the formatting you like.

Here, we can see that Excel has automatically unmerged the previously merged cells. Also, notice that the Merge & Center option is unavailable. Hence, to merge those cells again, we have to convert the Table into a range.
First, select any cell of the Table, and then go to the Table Design option.
Then in the Tools group, click on Convert to Range.

In the popped-up dialogue box, select Yes.

Consequently, the Table has again converted into a range.

Now, we can merge those unmerged cells again. So select the cells B4 and B5 first, then click on the Merge & Center option.

The cells will be merged again.

Now, do the same thing for other cells. You will get the final result like this.

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

There is an alternative way (and perhaps quicker) to convert a table to a range. Once you convert the Table to a range, the rest of the procedure is the same as method 2. To apply this method, follow the steps below.

Steps:

Convert the dataset into a table using the same process mentioned in method 2.

Now select any cells of the Table and right-click on the Mouse. A context menu will appear. From the menu, select Table > Convert to Range.

Now, a popup will appear. Select Yes.

After clicking Yes, the Table will be converted into a range like this below.

Now, follow the same steps to merge the cells like method 2. You will get the same result as this.

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 then merge the unmerged cells by using the same procedures mentioned in the previous methods. To run VBA code, follow the steps below.

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

Now paste 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

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

Things to Remember

Excel Tables do not allow to merge cells within a table
Following the methods mentioned above, we can represent our data in a table format but they will not offer the functions that an excel table posses.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Table with Merged Cells.xlsx

Conclusion

That is the end of this article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

<< Go Back to Make a Table | Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

How to Create a Table with Merged Cells in Excel: 4 Useful Methods

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

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

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

4. Run a VBA Code to Create a Table with Merged Cells

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply