Excel – How to create a sortable table with merged cells in the header in Excel

microsoft excel

I want to create a table that has a somewhat complex header. The table should look like this:

+-----+-----------+-----------+
|     | Monthly   |Annually   |
|Item +-----+-----+-----+-----+
|     | RMB | USD | RMB | USD |
+-----+-----+-----+-----+-----+
|     |     |     |     |     |
+-----+-----+-----+-----+-----+
|     |     |     |     |     |
+-----+-----+-----+-----+-----+
|     |     |     |     |     |
+-----+-----+-----+-----+-----+
|     |     |     |     |     |
+-----+-----+-----+-----+-----+

There are a couple problems I'm encountering:

  1. I don't know how to create a header row that spans two columns or two rows. In the example above, the 'Monthly' and 'Annually' aren't really column headers; I just added a row to the spreadsheet above the the table, after creating the table.

  2. The table won't let me have sortable columns with the same name. It auto-renames the 'Annually::RMB' and 'Annually::USD' columns RMB2 and USD2 respectively.

Is there a way to create a table like I've demonstrated above that is sortable in Excel?

Best Answer

Beware of merging cells, both vertically and horizontally.

Determine what headers you want to sort by. You don't need to create a table to sort items by their headers. But if you choose to create an Excel table, then the headers must be unique.

Consider creating a structure starting in row 2 with header names like item, RMB, USD, RMB USD

Enter some data. Then turn on autofilter for that range. Now you have the sort/filter drop-down buttons in the header cells. Next, enter any text in row 1. You can even merge cells A1 and A2 if you really feel the need.

enter image description here

In short, you don't HAVE to merge cells. Use formatting instead. You don't HAVE to use a table to benefit from sorting and filtering functionality.

Related Question