Posted on August 11, 2016|by: Ricardo Palhano| No Comments

Dynamic SharePoint List View Dashboard with Excel Viewer

This post will cover how to create an Excel Dashboard inside of a SharePoint list or library view, using the Excel Viewer web part and an Excel sheet linked to your list or library.  The Excel sheet will use pivot charts and tables to display the data we want from the list in a graphical manner.  We will get Business Intelligence Visualizations without the need for complex SQL.


1. First we need to export our list to Excel via List>Export to Excel


2. Open the iqy file in excel.  This creates the necessary data connection in Excel which we will examine next.


3. Because this is an external data source we need to Enable the connection.


4. Now we can see all of the data that is pulling from the SharePoint list

5. We want to create a table from all this data, so select the INSERT tab on the ribbon and click on PivotTable

6. Now we can select Use an external data source and click on Choose Connection… so that the new PivotTable and Chart can pull directly form the SharePoint list.


7. SharePoint created a new connection called owssvr and we will select that for our connection.


8. At this point we can delete the owssvr worksheet SharePoint created if we don’t need to use it anymore.


9. With the new PivotTable created, let’s go ahead and pick the fields we want to use for both this table and the PivotChart we will be adding.  Select NextFY Value ($K), Op Status, and Total Value ($K)


10. We want to see totals for our value fields, so let’s move those into the Columns and make sure they are counts.  We can leave the Op Status field in the Rows section since we are organizing our table by that.


11. Here we can see our new table layout showing labels and


12. Now we can add the PivotChart by selecting INSERT > PivotChart


13. Select a Clustered Column Chart


14. You can rename the chart by clicking on it and selecting the name in the dropdown at the top left of the Excel ribbon.


15. We want to use the same configuration as our table but for both, let’s go ahead and add the ability to filter by Business Unit.  Do this by dragging Business Unit into the Filters section.


16. Now we can see a new filter dropdown on our table.


17. We see the same for the chart


18. At this point we can create new sheets or tables and graphs for the different views we want by selecting the filters we may need for that particular view.  Click the filter dropdown and select Defense then click OK


19. Do the same for the chart and we can see that the funnel icon appears.


20. Now we have a filtered version of both the chart and table.  We can create additional ones for each view if we need.


21. If we are going to be including multiple charts and tables in the excel for use in the Excel Viewer web part, we will want to rename them to something a bit more descriptive.  This is done by selecting the chart or table you want then clicking on the Name text box in the top left of the ribbon.

22. Go ahead and save the document as an Excel Workbook


23. We will upload the Excel to a document library

24. Now on the view you want to add the Excel chart and table to select Edit Page


25. This will open up the view page, which has web part zones available to add web parts to.


26. Click Add Web Part the select Business Data>Excel Web Access


27. Now on the dropdon on the top right of the webpart select Edit Web Part


28. Click on the points of ellipses to select the workbook we just uploaded


29. Select the named item All Opportunities Table or whatever you happened to name that particular item, then uncheck the Autogenerate options and select non for the Type of Toolbar as well

30. We can now adjust the appearance of the web part.  Rename the webpart, and set a height and width that will display all of the table without producing scroll bars.  Also set the Chrome Type to None


31. Add another for the chart and select the same options except for the Named Item


32. Again set the appearance options for the chart web part
33. You can now do the same for the other view(s).


Our views now have some great tables and charts providing additional insight that refresh with the view loading.

Filed in: Collaboration, Sharepoint, Technology, Tips & Tricks

Your email is kept private. Required fields are marked *