0

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.

4
  • 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, 2018 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, 2018 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, 2018 at 15:42
  • You cannot use merge feature in a table in Excel, this is by design behavior in Excel.
    – Lee
    Aug 13, 2018 at 8:34

1 Answer 1

0

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 (or press Ctrl+Shift+L)

Filter

After that just format the table manually if necessary

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .