New, Improved way of delivering Excel reports: Part 2

In this article we will learn the actual steps of delivering Excel reports: Create a report in Excel, Publish it in SharePoint and Share it with others.  Please read the previous article before reading this article.

Publishing Excel reports

Estimated reading time 10 min


Excel 2013, SharePoint 2013 (Enterprise edition) or Office 365 with SharePoint. Some of the functionality will work with SharePoint 2010 as well.

The process

Here is what we want to do … same diagram from the previous article.

Delivering Excel reports - the process

Get the data and Create the reports

I am not going to spend time on how to create reports. For the time being, let us assume that you have already created the summarized reports in the form of Power View Sheets, Pivot Tables and Charts. You may also have some manually created custom reports on specific sheets.

Decide what you want to display as a report

Ideally we don’t want to show all the contents. Most of the content will be raw data, intermediate calculation or supporting worksheets. Usually only the final report, which you usually copy paste as values into another file – is what we want to show.

This is done using File – Info – Browser View Options.

Delivering Excel reports - Browser view options

By default all the sheets are shown. Choose the required option and save to SharePoint site.

Delivering Excel reports - Choose Entire workbook

However, you can choose the sheets you want to display.

Delivering Excel reports - choose sheets to display

Showing entire sheets is a good idea for custom reports which are manually created. It is also the only option available for Power View reports.

Now only the selected sheets will be shown on the browser on SharePoint site.

This approach has one disadvantage. The  report components like pivots and charts may be scattered across the selected sheet. They may not be visible without scrolling. Therefore, it is a bit cumbersome to discover all components of a report – especially if these are lying on multiple sheets.

Therefore, the third option was provided. This is the most powerful option.

Items in the workbook

Every pivot table, table or chart can have a name. Default names are like Table33, PivotTable2, Chart19 and so on. But you can change the names.

Change Table name from Table tools – Design menu, Pivot table and Chart names from Analyze menu.

PivotTable name

Choose the items you want and then save the file to SharePoint or OneDrive for Business.

Delivering Excel reports - Items in the workbook

Save to SharePoint

With Office 2013, this is very easy because your OneDrive and SharePoint locations are available in File – Save As menu itself. In Office 2010, you have to go to File – Save and Send – SharePoint option.

Share the report

Once the file is in SharePoint you have to share it with the intended recipients of the report. Click on the option button (…) next to the file and choose Share – type names of people. Choose whether they can Edit or View and click Ok.

Delivering Excel reports - Share the report

A preview of the Excel content is also shown in this dialog for your convenience. It also shows the views on the file!

delivering Excel reports - Share on Sharepoint

How do the recipients get the report?

Once you send a sharing invitation, they will receive the link to your  report by mail. The mail will be sent by SharePoint.

They just have to click the link. The items you chose to display will now be shown on the browser – in a nice scrolling menu.

delivering Excel reports - view on Excel Online

On the right side – under the View area – small thumbnails of all selected items are shown. Click on the item to view it on the main area of the page.

If you have used Slicers – which is basically a filter, you can see the slicer on browser as well.

Delivering Excel reports - View on Browser

New way to publish reports

This is, of course, a new approach. It will need time for you and your organization to implement it. But why don’t you publish a few reports right away and try it out?

Over the next few articles we will cover more practical topics like difference between view and edit permissions, multiple slicers, versioning, notifications, combining multiple reports into one dashboard and auditing.

Related Articles

Part 1 covers the concept.
Part 2  (this article) shows the step by step process.
Part 3 covers Frequently Asked Questions.


Queries | Comments | Suggestions | Wish list