Displaying Excel Charts in SharePoint 2013

9/23/2015

There are several easy ways for the site owner to display data in a dashboard format on SharePoint 2013 sites without using installing SQL Server BI features. They include:

  • Using an Excel Web Part to display Excel data
  • Using a Page Viewer app to display Power View data created in Excel 2013

For this tutorial, we are using SharePoint 2013 online, but these features also work in the on-premises SharePoint Server 2013 environment. With either solution, Excel files are created prior to the creation of the SharePoint page.

Excel file containing Dashboard Data

The Excel file for our data is displayed below.

Excel Data

Excel Data

Both the table and chart are named in Excel so they can be referenced to display in SharePoint. The data table has been formatted using the Table feature found on the Insert tab. A Power View tab has been added to the Excel file and it will be displayed in the Web Page Viewer app.

Power View page created in Excel 2013

Power View page created in Excel 2013

Display Data in SharePoint 2013 using Excel Web Access app

This is the easiest method for displaying dashboard data in SharePoint.

  • Upload the Excel file with the metrics for display to a SharePoint Document Library. The Excel file can contain conditional formatting.
  • Use Site Contents to find Site Pages and create a new Wiki or Web Part Page. This example used a Wiki page called “Create Dashboard using Wiki Page”.
  • When the Wiki page is in edit mode, click the Insert tab on the contextual Wiki page ribbon and choose Web Part. In the Business Data category, add the Excel Web Access app to the page.
Excel Web Part Added to Page

Excel Web Part inserted into Page

  • The Excel Web Access app has options for selecting the file that has been previously added to the site’s Document Library to display in the app by customizing the web part properties in the tool pane.

 

Excel Web Part Tools Pane Settings

Excel Web Part Tools Pane Settings

  • Any table or chart in the Excel file can now be displayed in this Excel Web Part by changing the view menu above the chart. That menu displays the Excel Power View page but it does not display in the Web Part. We need to use the Page Viewer app for that.
View Named Charts and Tables

View Named Charts and Tables

Display Excel Power View in SharePoint 2013 using Page Viewer app

  • Upload the Excel file with the metrics for display to a SharePoint Document Library
  • Use Site Contents to find Site Pages and create a new Wiki or Web Part Page. This example used a Wiki page called “Use Excel Power View in Page Viewer App”.
  • When the Wiki page is in edit mode, click the Insert tab on the contextual Wiki page ribbon and choose Web Part. In the Media and Content category, add the Page Viewer app to the page.

 

Page Viewer Web Part

Page Viewer Web Part

  • The Page Viewer properties include the path of the Power View page. Navigate to the Document Library where the Excel file is stored. Open the file using Excel Online and view the Power View page. Copy that URL to paste in the Page Viewer app tool pane.
  • Edit the Wiki page containing the Page Viewer app. Update the Page Viewer tool pane to include these changes:
    • Keep the view at Web Page
    • Paste the Excel Online URL displaying the Power View Page in the Link
    • Add a Title for the app
    • Adjust Height to an appropriate height in pixels

 

Page Viewer Tool Pane Settings

Page Viewer Tool Pane Settings

  • Because the Power View feature is displayed using Excel Online, the filtering and pop-out features are fully functional when viewing it on the SharePoint page. This works best using HTML5 view rather than Silverlight.
Power View with Filters

Power View with Filters

Power BI provides more options for displaying data as a dashboard, but these two methods allow the site owner to display dashboard metrics using straight “out of the box” SharePoint 2013 team sites. Topics like this are covered in SharePoint 2013 End User and SharePoint 2013 Power User classes at LRS Education Services.