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