One issue that continually vexes users of the SQL Server Reporting Services Excel rendering extension is that of merged cells in the Excel output. Merged cells can be a problem because Excel's sort functionality is very particular about the way cells have to be merged in order to work properly. Excel requires that ranges of merged cells be identically sized in order to be sorted.
So, why does the renderer merge cells? It's important to keep in mind that the Excel renderer is primarily a layout renderer. Its goal in life is to replicate your defined report layout as closely as possible as an Excel workbook. At that task, it does a really good job. A consequence of this design goal is that in striving to preserve the layout, cells need to be merged on the worksheet surface.
Consider this example. This is a very common case that I see a lot of users running into. Say you have a report with a single Table and a Textbox above it acting as a header that is laid out like this:
When rendering to Excel, in order to preserve the layout defined above (in particular, the dimensions of each Textbox relative to one another), the worksheet cell grid is configured like this:
If you want to sort the Table data in Excel you cannnot because the first Table column consists of merged columns A and B and the second is column C. Excel's sort function cannot handle this case.
If exporting to Excel with the ability to sort is important to you, here are some tips to help you reduce the amount of merged cells in your workbooks:
I hope this helps clear up some of the questions surrounding this issue. Let me know if you have any feedback.