Generate Reports by Extending Export to Excel Functionality

Summary: SharePoint libraries and lists store content yet there isn’t any built in SharePoint analysis tool to generate tables and graphs based on current data. But you can leverage the Export to Excel feature to transfer all data to excel. Once in excel, a data connection is automatically create. This connection can be updated to refresh every time the file is opened so the latest SharePoint data is displaying. Graphs, Charts and formulas can be added to display the relevant information in a visually appealing manner. Every time the file is opened, the latest trends will display.

Instructions:

1. On the library / list tab select Export to excel
2. A series of screens will display. Select open, then OK, then enable
3. You will now see all the SharePoint items in the excel file. Select the data tab in Excel
4. Select Connections
5. Select Properties for the connection
6. Tick the box to enable background refresh and to refresh data when opening the file
7. Now save the file to a SharePoint library and every time a user opens the file it will refresh the data. Users will need to select Enable content if it is not enabled by default so the content refreshes when they open the file.
8. You can now add additional tabs to display pivot tables, charts and calculations based on the SharePoint data.

Notes:

1. The Data connection is attached to the table, so when the connection is refreshed the table updates with as many rows as is needed for the latest data.
2. You can add multiple connections to an excel file. Export all views needed. Then in one spreadsheet with a connection, duplicate the tab. This will duplicate the existing connection  as well. In the Connection properties, on the definition tab, copy and paste the definition from the second exported view to the compiled spreadsheet and refresh the connections. Both connections will update to their source.
3. If you update the column ordering or add a column to the view, it will not update the ordering. New columns will display as the last column.

Samples:

A nomination system (a SharePoint List) is connected to an excel file. The leadership team can see which department/cities are being nominated. During the nomination period, they can check if a healthy amount of nominations are coming from each department and promote it if not.

An action tracker is used regularly by the team. The leadership team can see a summary of actions open, completed, overdue, extended and per category.