How to eliminate merged and hidden Cells issue from Excel Export of a SSRS report

It is a known product issue (bug Id –469197) that when SSRS report is exported to excel, sometimes the columns get merged or hidden. As shown in Fig. below when a SSRS report is exported to excel ,columns in excel get merged and cause issues while printing and exporting to pdf. In some other cases exporting leaves some hidden columns in the excel sheet. This behavior is unpredictable and is encountered quite often. Though the issues are trivial, many a times it is difficult to explain and convince the customer about the erratic behavior of export as due to merged cells, customer might face issues while sorting columns.However, as per the SSRS team based on the way that the SSRS Excel renderer lays out a report when it is being created, that behavior is currently by design. Thus, here are few techniques/ workarounds that you can try to counter issues of merging and hiding columns on excel export of SSRS report.


To resolve the issues of merged and hidden columns, follow the steps below.

1.  Make sure that while designing the report in the report layout, all the report’s rows (header row as well data rows) should start at (0,0) location and there is no additional space on the left hand side of the report in the design layout. This can be
done by making the Left and Top values under the Location section in the properties window, 0

2. Ensure that the width of each column is a rounded figure.If you have a requirement  to have a decimal figure as width then ensure that decimal has at the most ONLY 1 or 2 decimal places ,

for eg : 2.5 , 2.75 and not decimals like 1.734 or 2.5234.

3. Ensure the header row and the report’s data rows are allof same width as shown in the Fig Below.


4. Once you have made widths of columns as rounded figures,ensure that the overall width (sum of all the widths of the individual columns)of the report data row including header row is a rounded figure like 6 , 9 etc



This would also ensure that the Report header, column header row and data rows all are of same width as well, which is also needed to avoid merging /hiding of cells.


5.  If the above steps don’t solve the merged and hidden cells issue, try making the width of all the data columns (Column header, in our example , shown  in Fig below – “Service Title “need not be of same width as other data columns –CostHeadingsName and Total) equal in the report  .The width can be set in the properties window under the size section.

Above workarounds should eliminate the merging and hidden columns issue on excel export of SSRS report.









Comments (25)

  1. Somewhere else was recommended to set the “Page Units” from “Report Properties” as centimeters. I have to say that this seemed to reduce the number of cases of inappropriate behavior, though I can’t argument it.

    Normally I’m trying to play with the Left and Width Properties of the various controls by adding/subtracting 0.00015-0.00025 in or cm. Sometimes a re-dimension of the size seems to work as well.

    There was also a recommendation to use an Excel-based reporting authoring tool for SSRS.

    I have to say that this issue freaks me out sometimes, especially when dealing with an important number of layout changes. Sometimes I “manage” to spend more time on matching the controls’ size than on building the actual query.

    Because of this I'm trying to simplify the layout of my reports, which is a good thing, even if kind of forced.

  2. Vinod R says:

    Thanks, by keeping the width of the header and the table  same solved the problem of  merged columns in excel……

  3. Sojan Yadav says:

    I had an issue of cells being merged because of the title. I followed the instructions and made sure the title was 6 inches long and started from the location was 0in, 0in and it worked like a charm. Thanks a lot.

  4. george says:

    Microsoft should have bought crystal reports.   ssrs just isn't of the same caliber of tool.

  5. Ratna says:

    Thanks much! This helped out a lot…it was driving me crazy trying to unmerge all these cells

  6. Mscheers says:

    Hi, to be sure to eliminate merged and hidden cells when exporting your report, use de "pt" units instead of using "inc" or "cm".

  7. nick says:

    Agree with Mscheers steps above don't eliminate all hidden cells (especialy when more than 1 table in report) and only way I've found is to use "pt" units.

  8. Manoj says:

    Hi All,

    Thanks Richa for your blog, I have sorted the cells being merged issue after read your blog, but I got another issue, I have done same thing what you have mentioned above, but I have added header and footer to my report, and I got logo's in the header, that causing the issue in merging the cells, can you please help me in sorting the merging cell when exporting to excel.

    Thank you in advance


  9. Manoj says:

    I have sorted it, I have aligned the logos to the column alignment, so, it works now.

    Thanks All.

  10. Adityan says:

    Thanks for your blog Richa. This content has helped me a lot in sorting the merge issue while exporting to excel.

  11. Rich says:

    This doesn't solve the problem with the large cell created at the beginning of an outer group, so I have a group "Customer" and within that I have another group with four lines, sales, sales total, adjustments, totals plus adjustments. This creates a column at the left of my page which spans four lines. Any ideas how to make these lines discrete?

  12. Mohan Kumar says:

    This one really worked and your steps are clear.

    Thanks alot.

  13. Dunc Smith says:

    I also removed the report header and footer which solved the merge/hidden cell issue.

    Report display was simplified right down to a single table, the size of which matched the overall width and height of the report.

  14. Sarunps says:

    I removed the header and that resolved this merge cell issue. Thanks

  15. Alin says:

    I have same issue, and finally found the reason, even for the matrix table which contains dynamic number of columns that we cannot predict how long the text controls outside of the table should be.

    The solution is, for the text controls outside of the table, its length must not in between any column of table, otherwise, it will use more than one columns when interpret to excel.

    This will cause merge columns at column 4 when export to excel

    | Header text control                     |

    |   Col1    |   Col2    |   Col3    |   Col4    |   Col5    |   Col6    |

    |   Col1    |   Col2    |   Col3    |   Col4    |   Col5    |   Col6    |

    |   Col1    |   Col2    |   Col3    |   Col4    |   Col5    |   Col6    |

    And following is to fix the issue, by increase the length of header text control to the ending of Col4

    | Header text control                               |

    |   Col1    |   Col2    |   Col3    |   Col4    |   Col5    |   Col6    |

    |   Col1    |   Col2    |   Col3    |   Col4    |   Col5    |   Col6    |

    |   Col1    |   Col2    |   Col3    |   Col4    |   Col5    |   Col6    |

  16. Joe says:

    For me, it was simply 2 text boxes in the header. Make sure that the edges line up with column edges, and no blank space between them, as this will introduce another column.

  17. Subbu says:

    ==========================Thanks all for your post =====================================

    Please follow the above five pts,,

    If all the above doesnt solve ur issue..Just try give some space between the two Tablixs..

  18. Jean-michel says:

    Thanks a lot . it helped me . i had titles and a text explanation at the beginning of the report that were too short compared with the size of the table. Now it is ok.

  19. kshah says:

    Another root cause of this issue may be images or text boxes, above the data matrix, that have edges not in alignment with column edges.  It appears that having an image or textbox edge ending in the middle of a column may cause the column to be split when exported to Excel.

  20. José G. Ramírez says:

    Thanks for the tips.

    I just had to relate to all controls mired DataGridView columns (Ex .: Instead of using 3 text controls, use 1 with markers linked positions between 1-3 columns but with no difference).

    Most of all the header and the LEFT and greater than 0 TOP location is what destroyed me rendering.

    Best regards.

  21. Saket Yadav says:

    Thanks, by keeping the width of the header and the table  same solved the problem of  merged columns in excel……

  22. Alex Gibb says:

    @Mscheers Great tip, when you've got multiple tables alignment and consistent sizing don't always seem to work, converting to PT worked first time! Thanks!

  23. Roberto says:

    This worked OK for me, set location to 0,0 , Thank you for posting

  24. John says:

    Changing all widths to pt unit fixes most of the problems. (in causes problems)

  25. Ana says:

    The article is very usefull. Thanks for sharing!