Use Excel 2010 to report on SharePoint lists

A quick win that’s often neglected is to utilise Excel to create reports on SharePoint, here’s how:

  1. Go to the list you’d like to report on and choose Export to Excel
  2. From the Data menu choose Connections then  Properties
  3. Check Refresh data when opening file
  4. Now create a pivot table/chart as you usually would in Excel
  5. Optionally create a named range of the area you would like to display in SharePoint
  6. Save the Excel spreadsheet to a document library within your site
  7. Add an Excel Web Access Web Part to a page
  8. Set the Workbook and Named Range

Note reports won’t update in real time -To update the report, its as easy as pressing the Open in Excel button then saving the spreadsheet.

Advertisements

One thought on “Use Excel 2010 to report on SharePoint lists

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s