Loading...

Dynamic SharePoint List Dashboard Using 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.

SharePoint add new item

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

Open or save notification

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

Excel security notice

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

External Data Source

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

Excel Pivot Table

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

Excel Pivot Table Choose Connection

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 from the SharePoint list.

Opening Existing Connections in Excel

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

Excel Delete

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

Excel PivotTable Fields

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).

Excel PivotChart

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.

Excel Total Values

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

Excel Ribbon

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

Excel Charts

13. Select a Clustered Column Chart

Excel Chart 1

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.

PivotChart Business Unit

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.

Excel Business Unit (All)

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

Excel Table

17. We see the same for the chart.

Excel select defense ok

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.

Business Unit Filter

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

Excel Funnel Icon

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

Excel Pivot Table tools

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.

Excel Upload Document

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

SharePoint Edit Page

23. We will upload the Excel to a document library.

Add New Item in SharePoint

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

SharePoint Business Data Folder

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

SharePoint Excel Workbook Display

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

SharePoint Excel Webpart

27. Now on the dropdown on the top right of the web part select Edit Web Part.

SharePoint Excel Workbook Display

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

SharePoint Workbook Display

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

SharePoint Appearance Settings

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

SharePoint All Opportunities Chart

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

SharePoint Title Chart

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

Excel Tables and Charts

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

Share
Tweet
Share
+1