I'd like to display the content of my Excel Workbook on a SharePoint site. I can do that in a Dashboard, which we'll talk about later in this section. But the easiest way to be able to display some of my data is in a SharePoint web part, on a web part page. Remembering that many of the pages that you use in SharePoint are web part pages. This is one that I created particularly for this purpose. So, it's a new web part page. I'll click Edit Page. And we have areas that I can place a web part in. Remember, that if you don't like these zones, it's really too late. You should throw them away and create a web part page with the zones you do want on it before you start.
But I'm going to click in the body. And in the Insert section, I'm going to choose Business Data, and there is my Excel web access. Click Add, I'll get an Excel web access web part. And I'm going to click to open up the Tool pane, which is also the Properties dialog. I'm going to click the ellipses to browse and go grab my Workbook, which is in the Documents Library, right here. And I'll insert it.
So, it's actually there. So, I'm going to choose my Workbook, it's in the Documents Library. There it is, and we will insert it. Now, what I'd like to do is choose a particular named item. And it helps for me to know what the names of those items were because when I click here, it's just asking me to type. So, I actually have a chart called Employee By Department chart. Remember, I can also have my Excel Workbook open and go in and look at it. So, here's my workbook.
And I'd like to show this pie chart here. So, I select it, click on Analyze, and it's the Staffing chart. You can always double-click its name and right-click to copy it if you wish. So, let's go back and insert Staffing chart. And then ,we have lots of options, just like every other web part does. Do you want to auto- generate a title for the web part? On the Toolbar, if I allow one, because I can choose not to, do I want to include the commands to open in Excel, download or download a snapshot? Commands to refresh selected connection. There aren't any connections here, I don't need that. And I also don't need to recalculate the workbook probably, although I might. Do I want a named item dropdown list for people to choose from? Not in this particular case.
In all of the other choices about Layout and Chrome in the title. So, the title here is not going to be Excel web access. This is actually Staffing Reports, and this is my Staffing chart. Im going to click OK. And there's my Staffing chart. So, what are the parts and pieces? Don't forget to name all of the items so that you can find theme easily. Don't forget to publish this in a place where a user who can get to this page has permission to get to the Document Library, in this case. And make sure that you feel comfortable working with web parts because you'll need to set the Properties here just as you would in any web part. So here, the choice is to open in Excel.
Remember the choice to recalculate the workbook, those were options that we had. File > Download, Download a Snapshot, Print. So, these were the settings that we provided for what kind of interactivity was going to be allowed when we change the properties of the Excel web access web part. I can now put other web parts on this page using the same workbook, or other workbooks. If I'm going to involve a number of different data sets and workbooks, it's time for me to move to a Business Intelligence page, something a little more specific. But I can create a nice looking dashboard here with a number of reports and charts that I already have.
Let's just do one more while we're here. Because once we start creating these, it's really pretty fast to continue doing so. I'm going to go ahead and click here in the Add a Web Part link. I'm going to choose Business Data, go right back to the same workbook. Click to open the Tool pane, click to find my workbook in the Documents Library > Employee Reports, Insert it. And I'd like to allow my users the opportunity to see the pivot table that sits underneath that chart.
So let's go back for just a moment, click on the Pivot Table in Excel. Go to Analyze. The Pivot Table is called Employee Count by Status, just copy it down. Let's go back. Paste our named item, employee count by status. Remember that I will want to change my title so that this is going to say, (SOUND) and I could say table in front of that if I want.
Whatever I'd like. Click OK. There's my employee count. There's my staffing by status. Now, I could make a few more changes and have this look even more splendid. I could tighten up the zones, I could change the settings for the heights of these web parts to tighten up the space here, but this looks pretty good. So, if a user wants to go in and download the workbook, they can do that.
They'll be asked, do they want to open it or save it. The entire workbook is availabe for them because all of these were based on the entire workbook. So, if someone has permissions to download, they have permissions to be able to have the entire workbook. Something to think of when you're deciding all the content that should be in this workbook. So, this is how we use Excel services to be able to deliver part or all of workbooks for our colleagues in Microsoft SharePoint.
AuthorGini von Courter
- What is SharePoint?
- Opening and saving Office documents
- Coauthoring Word documents in SharePoint
- Checking files in and out
- Working with SkyDrive
- Sharing and syncing document libraries
- Adding a list app to your site
- Using social networking features
- Creating site collections
- Editing pages
- Adding users to a security group
- Creating workflows with SharePoint Designer or Visio
- Creating content types and document sets
- Controlling site appearance
- Creating a Records Center
Skill Level Beginner
1. Introducing SharePoint
2. SharePoint Team Sites
3. Editing, Saving, and Sharing Documents
4. Working with Library and List Apps
5. Social Networking in SharePoint
6. SharePoint Sites and SharePoint Site Collections
7. Editing SharePoint Pages
8. Integration: SharePoint 2013 and Office 2013
9. SharePoint Permissions
10. SharePoint Workflows
11. SharePoint Content and Documents
12. Other SharePoint Server Site Templates
Controlling site appearance3m 39s
13. SharePoint Server Business Intelligence Features
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.