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 from 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 dropdown on the top right of the web part 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 none for the Type of Toolbar as well.
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.
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.
