From SharePoint List thru Excel to Web Part

How do you take information from your SharePoint list and display it in a pivot table on a SharePoint page? This can be achieved by using Excel’s Data Connections and Excel Services. This article will walk through the steps of going from a SharePoint list to an Excel Pivot Table and then to displaying the pivot table on a SharePoint page.

 

Export the list to a pivot table

 

Change the view of the list to “Edit in Datasheet”.
  

On the right side of the list is a subtle arrow. This is the hidden task pane. Click the arrow to open the task pane.

 

Select “Create Excel Pivot Table Report”

  

Choose the fields to put on the pivot table.

 

For more information on creating Pivot Tables with Excel 2007 see the Excel 2007 Courses site.

  
Change the name of your Pivot Table to something meaningful. This is how you’ll identify the table to the web part.
Publish to Excel Services

 

When you’re ready to publish your table, select the windows button > Publish > Excel Services.
  

Select the SharePoint document library you’d like to publish to.

 

If you need to add a network place, use the “new folder” button to launch the wizard.

  
Open “Excel Services Options…”
  
Select “Items in the Workbook” from the Show selection dropdown.
  

Select the pivot table created and click “OK”.

 

After naming the file, click “Save”. This will publish the workbook to SharePoint and Excel Services.

  
Excel Services will open with a preview of the report created.

 

Close the tab when you’ve finished reviewing the output.

 

 

Create the Web Part Page

 

Back on the SharePoint site, create a new page.
  

Enter in a Title, the URL Name (without spaces, using CamelCase), and “Blank Web Part Page” for the layout.

 

Create.

  
Add a web part
  
Add an Excel Web Access web part.
  

In the web part, you’ll see “Click here to open the tool pane.”

 

Inside the tool pane, the ellipsis next to the Workbook will open a browse menu to libraries/lists in the site. Locate the document library the workbook was published to.

 

You may need to use the paging option at the top to retrieve the next set of options.

  

Select the workbook that was published earlier.

 

Alternatively, if you have the URL, you can just enter that into the Workbook field.

  

In the Named Item, enter the name of the pivot table (exactly as it was named earlier in the workbook.)

 

Click “Apply”.

 

Click “Save and Stop Editing” to view the page normally.

  

The page will be saved into the “Pages” document library, a library that is common to all SharePoint sites.

 

Copy the URL to add to a link list.

  
This link can then be added to another page more accessible to others.
Refreshing the list

 

When the originating SharePoint list has changed, for example new items have been added to the list, the excel pivot table will need to be opened and refreshed. (Instructions on having the data refresh automatically will be added in another post.)

 

Open the document library housing the spreadsheet, and “Edit in Microsoft Office Excel.”
  
On the “Data” tab, select “Connection” and “Refresh”, then “Close”.

 

You should see the new totals reflected on the pivot table in the workbook.

 

Click “Save”. Since the workbook was opened from SharePoint it will save back to SharePoint. You do not need to republish it.

 

Close the workbook and refresh the page with the web part to view new data.

Advertisements

29 Responses to From SharePoint List thru Excel to Web Part

  1. Philip Coupar says:

    I would be very interested to know if anyone can get the automatic refresh working against a sharepoint list in excel services. I have followed various sets of instruction on how to do this but so far have been unsuccessful.

    Looking forward to seeing a post on this.

  2. Yes, I need to get back to that. I haven’t figured it out yet, since I got put onto other things, but I’m sure the need will come up again soon.

  3. Khalid Al-Kooheji says:

    Hi,
    we urgently require to have automatic refresh working for workbooks connected to SharePoint lists, we’re using Excel Web Services to display these workbooks.

    Appreciate any comment on the issue,
    Thanks

  4. Mayrun says:

    Hey there,
    I tried getting automatic refresh working (edit the ODC connectivity file) and it still does not work.

    Waiting for your reply, SharePoint Diva 🙂

    • sharepointdiva says:

      Unfortunately working this out isn’t on my priority list right now. But if you figure it out, please oh please do blog about it! I encourage everyone to start a blog over at the SharePoint Users Group. http://www.thesug.org

  5. Jenkins says:

    Hi
    Use UDF(User defined functions)
    achive this

    Regards
    Jenkins

  6. Nimi Kaul says:

    Hi,

    We have a slightly different problem. Our sharepoint site has multiple urls: An internal url and a different external url. When i implement this method with the internal url, i am able to see the dashboard but not with the internet url.

    What could the issue be and how csn i solve this probelm?

    Awaiting your response eagerly.

  7. Nimi Kaul says:

    To add to the above issue, when i follow the above steps and publish from the internet, the report is not displayed. it say “An error has occurred.

    Please contact your system administrator if this problem persists.”

    Any help will be appreciated.

    • Nimi,

      I’m sorry but I can’t help you with that issue. My company only has internal facing sharepoint sites so I’ve not had to deal with internet issues.

  8. Deborah says:

    Hi SharePoint Diva,

    Thankyou for your work thus far – I too am really keen to enable the SharePoint List to be refreshed – have you made any progress on how this is done? I followed the previous comments by M Naeem but that did not seem to resolve this particur issue of automatically refreshing data from sharepoint lists

    Regards
    Deborah

  9. Andy C says:

    I repeat deborah’s centements. If you can offer help on how you can refresh Excel webparts automatically, I will arrange for some kind of praise circle of monks to pray for you and ensure you are favorably looked apon by god(s).

  10. Tom S says:

    Me too. Setting the data connection in the excel file to refresh on open results in “Data Refresh Failed”

  11. Tom S says:

    http://msdn.microsoft.com/en-us/library/ms496823.aspx

    Seems that SharePoint lists are not currently supported in Excel Services 😦

    Excerpt …

    Unsupported Features in Excel Services

    Microsoft Office Excel 2007 is feature-rich. It is not possible to support every Excel 2007 feature in the first version of Excel Services. In deciding which feature to support, priorities are given to features that are needed in key Excel Services scenarios, and to making sure Excel Services is a server-grade service that will meet customers’ expectations in terms of reliability, scalability, and security.

    Future versions of Excel Services will support more features of Excel 2007.

    Features that Prevent Excel Files from Loading
    * Query tables, SharePoint lists, Web queries, and text queries.

  12. Tony W says:

    PROBLEM STATEMENT
    I have a manager request to have a sharepoint list or excel workbook incorporate an automatic capability descripted in this article, e.g., a change/update in a list launches excel services to republish existing charts, tables, pivots, areas, etc.

    Other articles seem to state that this task is unsupported.

    – through SOAP code –> Adding a Reference to Microsoft.Office.Excel.Server.WebServices.dll MSDN website = http://msdn.microsoft.com/en-us/library/ms565591.aspx

    – direct link –> MSDN-BLOGsite = http://blogs.msdn.com/cumgranosalis/archive/2007/01/25/msdn-documentation-correction-direct-linking-the-excel-web-services-assembly-does-not-work-in-sharepoint-workflow.aspx

    – via workflow –> msdn site = http://social.msdn.microsoft.com/Forums/en/sharepointexcel/thread/f2ab7f27-1278-41e8-b5a0-31ec04bbe570

    FINDINGS:

    so today, it seems that this must be done manually. as described by SharePointDiva (above), the various MSDN websites, and this Wrox article. Wrox site = http://www.wrox.com/WileyCDA/Section/SharePoint-Application-Services-for-Reporting-and-Dashboards.id-305860.html

    NEXT STEPS:

    continue to research and utilize the manual approach.

    Best Regards,
    Tony Willeto

  13. Marc Devillard says:

    Hello,
    It looks as though the great synchronization feature between Excel and SharePoint lists that was present in Office 2003 disappeared in versions 2007 and 2010.
    The good news is: a young company has developped and released a piece of software that restores the missing functionality.
    All details are on : http://www.softfluent.com/wsslists.htm
    The piece of software is free for non-commercial use.
    Please have a look and leave them a kind comment if you are happy with the result, as I was.

  14. Pratik says:

    very good post, but I am not getting updated data on EWA web part, i can see it in the source Excel file but its not published in EWA web part.
    Please advice

  15. Pratik says:

    found one more thing when click on Update > Reload Workbook, it shows all updated data.it isn’t possible without making any UDF to create Auto refresh string ?

  16. jaraguenc says:

    Did somebody could get the automatic refresh working?

  17. Anonymous says:

    It doesn’t work — perdiod. There must be a reason that they took this out, but I don’t know why. UDF solution is clunky because you have to declare the range which means you need to know data size before hand — and any blank cell will be filled with and error statement. If you can live with that the UDF solution will work I got that to work.

  18. Anonymous says:

    What if you add “Microsoft.Office.List.OLEDB.2.0” as a Trusted Data Provider in sharepoint administration?

  19. Thank you! The screenshots are fantastic. Thank you very much for the detailed post.

  20. msbuzzz says:

    Thank you for your great help, how ever a quick doubt, I am using an Excel web access service to show an excel sheet in the browser, how ever I am little confused that, I cannot add new rows from web part. Is that possible, or am I missing some thing ? Many Thanks !!

    • sharepointdiva says:

      The web part is to display the information to others without requiring them to open the spreadsheed in Excel. They don’t even need to have Excel to see the data through the web part. However, you still use Excel to edit the information.

      • msbuzzz says:

        Thank you for spending, time on this!! So I replaced Excel web access web part with a data sheet custom list web part. So that we can easily edit and import to Excel quickly. Thank you!!

  21. Chong says:

    This really is the third blog, of your site I actually read.
    However , I like this one, “From SharePoint
    List thru Excel to Web Part | SharePoint Diva” the best.
    Thanks -Jamal

  22. I have an article that shows you how to load real-time SharePoint list data into Excel Services including permissions, etc

    https://learn.sharepoint.com/Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: