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.
