Smarter ideas worth writing about.

Using a SharePoint List as a Data Set in Excel

There are different ways to manage projects and using SharePoint for project management is a very useful technique. I worked with a client whose project management methodology was something between agile and waterfall. They organized tasks and features into iterations, but the work itself was organized into something resembling a Work Breakdown Structure (WBS) of the type you would see in Microsoft Project. However, instead of using a traditional project management application to assist in managing requirements, tasks, and resources, they used SharePoint. 

Figure 1: Sample standard SharePoint List customized for task management

The WBS, requirements, and resource assignments exist in a customized SharePoint list where each item or row, if viewed in a table format, is one actionable task. All the information needed for that task is built in to the metadata for that item. There are fields for requirements, estimates, an assigned resource, feature alignment and many others. There are a lot of obvious benefits of using SharePoint for Project Management, including its inherent transparency and flexibility, but there's another way using SharePoint can give even more insight to your project- viewing the data in Excel. Here's how:

At the top of the SharePoint page, select the 'List' ribbon. The "Export to Excel" button should now be visible. Clicking that will cue Excel to open and import the SharePoint list as a data set and display it in a table format.

Figure 2: SharePoint Task List Exported to Excel

From here, you can do all your traditional Excel tasks. You can filter and sort, use formulas, copy & paste, or whatever you may need. It is important to note that any changes made to the data in the Excel sheet WILL NOTbe synced to SharePoint. The data connection is only one direction. SharePoint->Excel.


To take it a step farther, select the "Insert" ribbon in Excel and click "Pivot Table". By default, it will select the table you have imported and create the Pivot Table on a new sheet. From here, drag and drop the metadata fields (table columns) from the Field List on the top right and drop them into the areas below to create a dynamic table. 

For example, you can drag the field for "Project" into the Report Filter, Feature Alignment to Row Labels, Card Status for column labels, and Task Status to Values. This will display a list of all features within the project, and the status of all the tasks within that project by quantity. Essentially, this is a snapshot of the status of the project, organized by feature. 

Figure 3: Pivot Table showing tasks by status for each feature

You could do a similar structure, but with team members listed in the Row Labels. This can show you how many tasks are assigned to each person and what their status is.

Pivot Tables can be as simple or complex as you'd like. You can have the table provide sub and grand totals, filter by multiple attributes, and sort data. 

Figure 4: Pivot Table showing tasks by status for each developer

Pivot Tables can be as simple or complex as you'd like. You can have the table provide sub and grand totals, filter by multiple attributes, and sort data.

If a more graphical view of your data is desired, you can do that too. On your Pivot Table sheet, select the "Options" ribbon and click "PivotChart".  The standard Excel chart pop-up will appear and allow you to choose a chart type. Once you make your selection and click OK, Excel will create a chart based on your imported data. Don't worry if you select the wrong chart type- simply right click on the chart and click "Change Chart Type…"to choose another. You may also need to re-sort your table so that it is organized the way you would like it to appear in the chart.

Figure 5: Pivot Chart showing Figure 4 data visually

You can create multiple charts from the same dataset, which can be useful for creating dashboards for the data held in SharePoint. When you save the Excel sheet, it will retain the data that was already imported, but not reflect any changes made to the SharePoint list since that time unless you refresh the data. Refresh by selecting your sheet containing the SharePoint data, then choose the "Data" ribbon, and click "Refresh All". This will reach out to the SharePoint server and re-download the data. Your tables and charts will automatically update with the new data.

This technique has proven very useful when using SharePoint for Project Management, but could be equally as effective for any other data stored in SharePoint. 


About The Author

Senior Project Services Consultant
Tyler is a Senior Project Services consultant in Columbus, Ohio. His experience includes Business Analysis and Project Management with web development, Systems Management, and Enterprise Content Management.