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”.

  1. 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.
  2. 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.
  3. On your new page, click the Edit Page button in the ribbon, and then click the Left column zone to Add a Web Part.
  4. Add your library-full-of-spreadsheets, mine is called Spreadsheets.
    236-image_1cbe880b-43a5-4b97-9dc0-3c48de74a43c_05623EC2.png
  5. 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.
    236-image_7bd9f779-df48-42fb-b1d0-faa0828f9880_05623EC2.png
  6. 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)
    236-image_fb83a64d-069a-4e87-8143-f4e146ec3692_05623EC2.png
  7. On the Configure Connection screen, choose Document URL, and click Finish.
    236-image_5ac94636-05e5-43c6-9d1b-5617193dffaf_05623EC2.png
  8. 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!

236-image_6_05623EC2.png

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.

Advertisements

6 comments

  • 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

    Like

    • 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.

      Like

  • I would like to freeze the headers on the spreadsheet displayed using this webpart. How do I achieve that. I have SP 2010

    Like

  • 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

    Like

  • 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

    Like

Leave a reply or question

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