How to Export to Excel Without Making a Mess

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.

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

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:

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

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:

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:

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

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

And we assign it two values – Yes and No:

Then we hide the text box when FormatForExcel = Yes:

And voila, our columns no longer merge when we 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:

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:

We set the location to 0in, 0in:

 

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

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.

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:

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

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

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