Many, if not most, of the SSRS reports I write end up being exported to Excel. But unfortunately, many reports don’t export cleanly. More often than not, you wind up with merged cells and blank spaces you don’t want.

In this blog post, I’ll show you how to build your report correctly for a cleaner export to Excel.

Exporting to Excel

I’ve used the AdventureWorks database for all my examples.

1. Eliminating Merged Cells from SSRS Export to Excel

Merged cells are one of the great annoyances of exporting to Excel. Here’s a simple example of how to resolve this issue using sales data:

Export to Excel 1

When we export this to Excel, the sales order ID column is a merged column of Columns C and D.

Export to Excel 2

This is a really simple report. So, why do we have this merged column issue?

If we look at the report layout, we can see that the text box from the header aligns to the middle of the column for sales order ID:

Export to Excel 3

This is what’s causing the issue.

While it’s theoretically possible to make sure that all your text boxes align exactly with your Tablix columns, I haven’t tried it as my columns change way to often.

The easier way to fix the problem is to simply hide the text box. To do this, we click on the text box and set visibility to Hide:

Export to Excel 4

When I rerun the report and export to Excel, the sales order column is no longer merged. Instead, it’s only covering Column C:

Export to Excel 5

Now, of course, we do want the header information to print sometimes. For that, we create a parameter called Format for Excel:

Export to Excel 6

And we assign it two values – Yes and No:

Export to Excel 7

Then we hide the text box when FormatForExcel = Yes:

Export to Excel 8

And voila, our columns no longer merge when we export to Excel.

2. Removing Extra Columns and Rows from SSRS Export To Excel

We may have solved the merged column issue, but we may still have the problem of extra columns and rows. This isn’t the end of the world, but it’s not great either. Because, as I’ve said before, you won’t get credit for what’s hard. But you may get credit for what’s pretty.

Going back to Excel, say we have an extra column to the left:

Export to Excel 9

To get rid of extra columns, simply make sure that your Tablix is aligned to the right border of the report.

In this case, we highlighted the table and find location in the properties box:

Export to Excel 10

We set the location to 0in, 0in:


Export to Excel 11

If we export again, we have no extra column on the left:

Export to Excel 12

As for extra rows at the top, the news isn’t as good. Even if you hide the headings, as I did before, you’ll still get an extra line. If you’re always going to export to Excel, you can delete the heading. Otherwise, if you really can’t have that extra line, you’ll have to eliminate it manually.

3. Adding Information to the Tab Name in Your Export

The only problem with hiding headers is that your parameter information can go bye-bye.  So what to do instead? You can control data in the tab name.

Again, highlight your Tablix and find the PageName Property:

Export to Excel 13

In this case, I’m going to create a simple expression including the beginning and ending dates:

Export to Excel 14

When we export, we see the information in the tab:

Export to Excel 15

I hope these few examples help you create pretty exports to Excel.


Leave a comment


    • tejaa

    when im exporting the ssrs report to the excel the cells are expanding and the gaps are created.
    report name columns *it should be like this- but it is showing as below*

    report name columns *it is showing like this*


      • Adam Jacobson

      Sorry I can’t help without more info

    • Michael Harrison

    Nice summary. I have been able to suppress the blank initial row by putting all the header elements inside a rectangle with its own Hidden property set the same way.

      • Adam Jacobson

      Glad you liked it.
      SSRS rewards tinkering.
      Wondering how all this works as paginated reports in PowerBI

    • Lyz

    from “FormatForExcel” Yes and No box, i lost you on how you got to the next step. do you create another parameter called Hidden? the formula shown doesn’t strip the text box, so how does one do that? i did not get to the VOILA stage :). was this gap by design to get business or am i that lost?

    • Lyz

    thank you!

      • Adam Jacobson

      Seems you figured it out.
      Basically, the formula goes in the text box properties above.

    • Shawn

    Add the position and height of the previous text box to find out the position of the next textbox. If you match it exactly to the decimals then you will not get any row merge issue.

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284

Request a Consult