How can I take advantage of sorting, filtering and generating charts from my data using "Format as table" option in Excel 2016 without losing its current table format?

Please note that when I convert my data using Excel automatic table formatting it loses its shape and I can no longer merge the cells.

My original data

original data

My table after formatted

Excel ruined my format

Kindly please help me because I need to report this data asap.

  • 1
    I don't think merging cells is compatible with Excel's tables, so I don't believe you can do this. – Blackwood Aug 11 '18 at 14:49
  • Then what can i do to take advantage of excel's sorting and chart generation options with keeping my current format? – Wayne Aug 11 '18 at 15:39
  • I don't know of a way. I think the closest you can get is probably to give up on merging cells that contain the same data, and duplicate the data in those cells instead. – Blackwood Aug 11 '18 at 15:42
  • You cannot use merge feature in a table in Excel, this is by design behavior in Excel. – Lee Aug 13 '18 at 8:34

Excel tables can't contain merged cells, therefore it unmerges them as you see

If the Merge and Center button is unavailable, the selected cells might be in Edit mode, or the cells might be inside an Excel table. To cancel Edit mode, press ENTER. You cannot merge cells that are inside an Excel table.

Merge and unmerge cells

Even if you format the table first and then merge it won't allow you to do that

It's actually reasonable, since what will happen if you filter the data out? How should matched rows that belongs to partially merged cells be shown?

But you can always filter the data by selecting the header line (row 2 in this case) then go to tab Data > Filter


After that just format the table manually if necessary

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.