Microsoft Office

Today’s author, Diego Oppenheimer, a Program Manager on the Excel team, talks about connecting PivotTables to data stored in SharePoint Lists:

Many of us on the Excel team have been approached by customers asking us how to create a connection to a SharePoint list. SharePoint lists can be exported easily by creating a Microsoft Office Excel Web Query. The connection can then be tied directly to lists or PivotTables in the workbook like any other Data Connection.

I have a SharePoint List that we use to upload and keep track of all project management reports. The list contains information like the project manager that uploaded it, the date, and the name of the reports as well as other automated fields created by SharePoint to make my life easier. Using the filters provided by SharePoint gives me a great way navigate my list quickly and easily but I am more interested in creating a macro view of the team reports so an easy solution would be to connect my SharePoint List to a PivotTable. The fact that my SharePoint List has more than 250 rows makes the use of a PivotTable even more appealing.

Creating the Connection and the PivotTable:

Once we have located the SharePoint list we want to export we go to “Export to Spreadsheet” in the “Actions” menu button.

When the message prompting us if we would like to open or save the Microsoft Office Excel Web Query click “Open” (or you can save for use later). If you haven’t started Excel yet this should start it up.

Click “Enable” to unblock the data connection.

If you had Excel already open you will get the the “Import Data” menu. In the case that you did not have Excel open already opening the connection will create a a resfreshable query table in the workbook.

From this menu we can choose to directly create a “PivotTable Report” a report and a “PivotChart” or just a query “Table”. Today I am interested in creating a PivotTable so I select “PivotTable Report” and hit “Ok”.

Now I can see all my uploaded reports in the PivotTable. To get a better view of what is going on we add the Project Manager field to Rows, Date to the Report Filter (so we can filter by date) and the Name field (the name of each one of our uploaded reports on the SharePoint List) to Values. By adding Name to Values we are essentially creating a count of how many name items we have in the data source.

We replace “Count of Name” with “# Reports” by editing in the formula tab like any other cell.

Now I can easily view the numbers of reports per project manager and filter by date as well as viewing the “Grand Total” number of reports. This is great but the team actually splits up in sub teams by area of expertise so I am going to group them to make this clearer.

We select all the members of the each team in the PivotTable and right click. Select “Group”. Now I can rename the Group label to better represent the teams. I name my groups “Finance”. “Technology” & “Operations”.

Combining the use of our Date filter and the groups we created we can easily see a macro view of all the project management reports.

As soon as new reports are populated into my SharePoint List I will be able to update the PivotTable (right click on the PivotTable then click “Refresh”).

Where does the connection live?

Like all other data connections in our work book we can access it by selecting the “Existing Connections” button under the “Data” tab.

We can see that our SharePoint List connection is shown under “Connections in this Workbook” and can now be used to create new PivotTables.

Multiple Microsoft Office Excel Web Queries can be used to monitor more than one SharePoint list at a time as well as to easily analyze the data in them using PivotTables and PivotCharts. The same as with all Office Data Connections you can now use this connection with multiple workbooks and update any connection changes in a single place.