Create a Pivot Table from a SharePoint List and Display it in SharePoint
Pivot Tables are a powerful capability of Excel, however MOSS now provides the capability to create a pivot table from data in a SharePoint list. This example will show you how to use a SharePoint list to create a pivot table and then display it back in SharePoint using the Excel Web Access web part.
- The first step is to make sure that you’ve specified that you have a trusted file location specified in your SSP that allows remote connections. If you haven’t already setup a trusted file location take a look at this post for more information.
- You’ll need to verify that your trusted file location is configured to allow external data connections. Open up the trusted file location you’ve created and edit the settings and confirm that the following value is set:
- The next step is to create a list in SharePoint that contains some data to be used for the pivot table. In this case, I’ve created a custom list with 3 site columns: City (text), Units Sold (Number), Profit (Currency). Then fill in the list with some sample data.
- From the actions button select to edit the list in datasheet as seen below:
- Once the list is shown as a datasheet you should see a small arrow at the right as seen the image below. Click on the arrow to expand the tool panel:
- From the tool panel, click the Create Excel Pivot Table Report link:
- You may be prompted with a warning that looks similar to this, click the Enable button:
- The pivot table will open in Excel, but you’ll need to select the fields at the right that you want to include in the pivot table. In this example we’ve chosen our 3 fields from the SharePoint list:
- It is also helpful to give the pivot table a meaningful name. We’ll be using this name later when we display the information with the Excel Web Access web part:
- The next step is to publish the pivot table into a document library in SharePoint. To do this, click on the Office icon in the upper left corner and select Publish > Excel Services:
- Choose a document library where you want to save your pivot table. You’ll need to browse to the appropriate location by clicking on My Network Places and then browsing to the server and then to the folder where you want to save the pivot table. In this case I’m saving it to a document library called Sales Figures. Be sure to pick an intuitive name for the file, but before clicking save press the Excel Services Options button:
- A new window will open, and for this example we’ve chose to show Items in the Workbook and have selected Q1Sales, which is the name for our pivot table. If you happened to have more than one pivot table this dialog would allow you to select only the items that you wanted to show:
- Now the table has been saved to SharePoint. The final stage of the process is to show the pivot table in a web part. For this step you could create a new web part page, or simply use any page that has a web part zone. Place the page into edit mode and add the Excel Web Access web part:
- After the web part has been added to the page, modify the web part and under the Workbook Display section, press the “…” button to browse for the file you just saved to SharePoint:
- Browse to the location where you saved the file and select it then press Ok:
- In the Named Item field for the web part specify the name you gave to the pivot table in step 9. In our case, it was Q1Sales. Once you’ve completed this, press the Ok button at the bottom of the web part tool pane.
- Publish the page if necessary and you should now see your pivot table:














It should be noted that this data will need to be refreshed manually which can be done by clicking on the update button in the image below.
Author: John Ross
John Ross is a trainer and consultant for SharePoint911. He is the coauthor of SPG301: SharePoint Planning and Governance with the Ted Pattison Group. John is also a coauthor on the SharePoint book MOSS Explained: An Information Worker’s Deep Dive into Microsoft Office SharePoint Server and was a contributing author to the book Professional SharePoint 2007 Design. John lives in Orlando, FL.
Be sure to check out our upcoming training for Business Users and Technical Leads June 1-4th in Cincinnati, OH.
- Getting Started with Excel Services: Part 1
- Getting Started with Excel Services: Part 2
- Create a Pivot Table from a SharePoint List and Display it in SharePoint
Thanks, this is a good idea.
We are getting Data refresh failed error when trrying to update.
Yes, data refresh does not seem to work against a sharepoint list. So if the only option is to have a static pivot table/chart that can’t be refreshed unless the owner opens it, refreshes it, and saves it, this doesn’t seem very useful.
Your options for refreshing from a SP List are very limited. However, there are other options for refreshing with other data connection types. That would make for a good followup post to this article.
-John
It is frustrating, because it is pretty straightforward to use an odc data connection file against sql server data and have it refresh every time the excel webpart is opened. Seems reasonable to expect this same functionality against a sharepoint list (which is essentially sql server data anyways…)
Great !
NOW PLEASE SHOW THE DIFFICULT PART;
a EDITABLE / UPDATABLE PIVOT TABLE !
It is wuite annoying that microsoft is reluctant to give this functionally, even killing the OWC11 PIVOT TABLE to be editable from the web.
Thanks any way,
Fabio
I still think this is a great idea and method.
Thanks John!
I have a question for you 17 step, the screenshot only show the pivot table, can you show the pivot table field list on excel service? I can’t access my pivot table field list on it.
Thanks!
It doesn’t work for me. Getting the following error message.
The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:
SharePoint lists
Contact the workbook author.
Does anyone know any workaround to this issue?
Does anyone know how to do this with Pivot tables created in Access? I don’t see the comparable options in Access for step 10, i.e. there are no Access Services.
btw, the reason I’m using Access is that Excel pivot tables display only numeric data, I am displaying text.
Thanks!
Hi,
I am trying to create a single list from a 5 other lists which have similar data content. I need to use this final combined list in order to create reports on the same sharepoint site. Does anyone know a quick way to do this?
Thank you,
Gargi
Hi
It didn’t work for me. I got the following error message.:
The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:
SharePoint lists
Contact the workbook author.
Can you please tell what can be the issue…?And i read in this MSDN blog…http://social.msdn.microsoft.com/Forums/en-US/sharepointexcel/thread/d3873d30-ef96-4bd2-857c-7dc518d3df5d …..that Excel Services do not support loading data from SharePoint List …then how is that yours is working…I am confused…can you plz clarify…?
-Shakti
Have you any procedures on adding a pivot chart to a sharepoint page?
I have a small question regarding , publishing the excel workbook ..
When i publish the excel workbook and see the data as web page can i do slicing and dicing on the data .. ?
I am using sharepoint 2010 .
Please provide the reply as soon as possible ..
Thanks in advance ..