Dynamic Excel Web Part
In all versions of SharePoint and Office 365, the Excel Web Access web part has been around for years. It is part of the enterprise version of these products. Web part connections can be created on pages in SharePoint, to send information between web parts.
The requirement in this scenario is that a department has a bunch of spreadsheets in a document library. They are all similar, and the end users would like to be able to quickly flip between them while staying on the same page.
This can be done with an Excel Web Access web part and the library on a web part page, using web part connections! Here’s how:
What you already have: A document library with a bunch of spreadsheets in them. In my example, they are all exactly the same, just with different data in them. The tables, charts and named ranges are all exactly the same. My library is called “Spreadsheets”.
- Create a web part page for this new dashboard. A quick way to do this is to go to your “Site Pages” library, and in the Files tab, click the New Document drop-down, and choose Web Part Page.
- Give the web part page a name, double check that it’s being saved into the Site Pages library (or you can put it somewhere else, just pay attention to where it’s going), and click Create.
- On your new page, click the Edit Page button in the ribbon, and then click the Left column zone to Add a Web Part.
- Add your library-full-of-spreadsheets, mine is called Spreadsheets.
- On this same page, click Add a Web Part in the Right or middle column zone, and in the Business Data category, choose the Excel Web Access web part.
- With the page in Edit mode again, click the little drop-down box at the top right of the Excel Web Access Web Part, and choose Connections –> Get Workbook URL From –> Spreadsheets (or whatever the name of *your* library is)
- On the Configure Connection screen, choose Document URL, and click Finish.
- Now stop editing, or save the page. By default, the web part will show the first spreadsheet in the library, according to whatever order your data is sorted in that web part. As you click the little gray double-arrow next to each spreadsheet, the web part on the right changes to show that spreadsheet!
In mine, I even have multiple named ranges and tables, so I can let the end user use that little drop-down at the top right of the Excel Web Access web part (that says AllProducts) to switch over to different parts of the spreadsheet!
1/23 update: On SharePoint Power Hour this week, I did a demonstration of this solution. Click to watch the video.
Hi,
Thank you for this post. I almost have this working. I have the same spreadsheets with the same named chart added into a document library. I put the named item for the chart into the Excel Web Access Web Part and when the page loads, the chart is displayed for the first spreadsheet. If I click on the other workbooks in the document library, the entire spreadsheet appears in the Excel Web Access web part. Do I need to do something else? A web part filter maybe? Cannot figure this one out.
Thank you so much,
Joanna
Hi Joanna,
In the other spreadsheets, in “Browser options” in excel, I think you need to uncheck the box so that the whole sheet isn’t even an option in there, which should force it to just use the range(s) you specify.
I would like to freeze the headers on the spreadsheet displayed using this webpart. How do I achieve that. I have SP 2010
NBK, have you tried making the column headings into filters? I think that makes them stick. You do that in excel, on the data tab.
Hi Joanna,
You can put named ranges in Excel, and then in the Excel web access web part, type the name of a specific named range that you would like the web part to display. Also, on the “save as” screen in excel, there are “browser options” also where you can select what you want displayed in web parts. More info: https://support.office.com/en-us/article/Excel-Web-Access-Web-Part-custom-properties-F08198DE-C476-4E20-BEA7-64248D46304E
Hi,
Thanks for your information!! I created two web parts running SharePoint 2010. But I don’t see ‘connections’ from the drop-down box at the top right of the Excel Web Access Web Part. Is there any permission issue with it? Can I create a connection using SharePoint designer? What do you suggest?
Thanks,
GG
We have a 2010 site that uses the Excel Web Part with a connection to a document library of XLS files on several sub sites. Customers love it. They use an Excel template to show funding and charges each week. Now we are In the process of migrating to SP 2016 on prem. However it is not allowing the connection to complete between the parts. (never gets to step 7) It just spins. Tried other browsers, tagged as a Trusted site, tried it in other 2016 site collections, using OOS files open in browser np. Even checked John White’s “Adding Excel Services Capabilities to a SharePoint 2016 Farm”. Are there any other CA settings or PS to check? Have you set this up on a SP 2016 on prem with Office Online Server? Is there a different method for SP 2016?
Thank you, Laura, for your many great no code videos.