Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel Services is a feature of SharePoint Enterprise and it's all about getting workbooks to be viewed in a browser. Now you might be thinking, "well, hang on a second, haven't we done this already?" I mean after all, very early on, we talked about the idea of going into a document library and being able to select a document like an Excel workbook, and click View in Browser or Edit in Browser. Well, here what we've been using is the Microsoft Excel Web application, part of the Office Web Apps, which is an add-on for SharePoint.
This is as much as possible trying to replicate Excel in the browser. So we can even edit an Excel document, see the whole thing, make a change to it, save it back into that document library. That is the Excel Web application, and that's very, very different from Excel Services. Excel Services is more of a business intelligence feature. It's not about having multiple people save and edit a workbook in a document library. We can already do that.
We don't need SharePoint Server Enterprise Edition for that. Well, what is it then? Well, it is about taking complex large workbooks with massive amounts of information and choosing to make parts of them available for other people to see. Excel Services is about publishing your content, your Excel workbooks. Not about collaborating on it. For example, I'm looking at a sample workbook. It's got a lot of sale information. It has got some charts in it.
It has got some item information, historic prices, there is even sheets full off salary information, which you certainly don't want to be sharing across the entire company. Now these kinds of workbooks are very, very common, they are the people who work in your C-level positions. the people who work in working with the numbers often have workbooks that they consider to be one version of the truth. These are the authoritative documents that describe how exactly is the company doing at any particular point in time. But they can't just make this available for everybody to see. Things like salary information are very much confidential pieces.
We don't want to share that. So oftentimes, you'll hear about people having to work with this spreadsheet and then save off multiple versions of it and trying to extract out all the confidential information every week, so that doesn't get seen by the wrong people. Well, this is what Excel Services is designed to take care of. So right now, this workbook is actually just sitting on my desktop and I have several sheets to the workbook. I have some named charts, I have got Chart 1 and Chart 2, I have got some named tables, and in fact in this section too I actually have the total salary is named at $609,000 called Salary Total, but I don't want to share the other information.
So what can I do? Well over here in my team site, though it doesn't have to be in the team site, I created a document library called Confidential and I'm hoping to put that workbook in this library. I could even put separate permissions on this library, so very few people get to read it, but I still want to be able to share some parts of that workbook and here is how I do it. I'm going to take the address of that library, because I haven't yet used it, go over to Excel, and from my File menu, I'm going to go to Save and Send and Save into SharePoint.
It isn't one of my existing locations right now, so I am going to click Browse for a location. But before I save it, I'm going to select this button up here that says Publish Options. Select which workbook items to publish to the web. What it allows me to do is select either the entire workbook and publish everything, all sheets, perhaps select only certain sheets, don't do all sheets, but I will share sales information, and item information. I could do it that way or I could go for items in the workbook. I could say that yes, I want to share all charts.
That's okay, because that's calculated information. I'm going to leave the PivotTables behind. I'm going to share Table 2 and 3, and then I also want to share Salary Total. Click OK. I'm going to click Save. Right now, I am just trying to save it to my Shared Documents library. I am not going to put it there, so I am going to type in the address of the Confidential library. Again if I hadn't clicked the option on the previous page, I can also click Publish Options here to select the same information, and click Save.
Now, what it's going to do by default is actually just jump to the Excel Web application, but notice how it's not jumping to a full view of that. In fact what it's showing me is just one of the things I've said it's allowed to show me. Over here, I've got a View now, saying the only things that I can view in the Excel Web application are Chart 1, Chart 2, Salary Total, Table 2, which has a lot of stuff, and Table 3. So, I've already put a lot of control over this because I am not sharing that confidential salary information.
However because I have the right permissions, I can still use this document library as the location to store that actual Excel workbook and I could edit that in Excel. So I have one version of the truth, if you will. This is my master document. I keep it this way, but I can allow other people to see certain parts of it. Now one of the most common ways that you're going to allow people to see parts of it is you can add Web Parts to your pages that display just sections of that workbook. So for example, I'm going to switch into the Edit mode on this page. I am going to go over to Insert and insert a new Web Part.
The place that you're looking for is under Business Data because there's an Excel Web Access Web Part. So instead of having to look at the entire thing and take over the screen with just Excel, we can inject a little piece of it just into our pages. I am going to click Add, and right now of course it has no idea what it's meant to be showing. So, it's says well to display a workbook, you must first select the workbook, and the way that you do that is click here to open the Tool pane. The Tool pane is essentially a Settings panel on the right-hand side of the page, which is going to ask for a few things, and most importantly what workbook are you try to show.
I have got a little ellipsis button, where I can now go over to my Confidential document library and select that sample workbook, click OK, and now it's going to say do you want to show the whole thing, do you want to show a named item, and I could even do something. I could give it say the name of a chart, such as Chart 1. I could give it the name of a named region, such as Salary Total. There is a series of options over here. Do you want to generate Web Part title? Do I want to give people the ability to open in Excel or download a copy? I am going to say No.
If this Excel workbook was drawing information from background data sources like SQL Server Analysis Services, you would enough have an option to refresh a connection. I am going to say I don't need that either. This option here, the Named Item In Drop- Down List, would give me as you might expect a drop-down list of all the named items, I'm allowed to see. I am just actually going to leave that because I don't want to do that. I am going to just leave the rest as default and click OK. Now, it's a little boring at this particular point here.
I am just going to save my changes, because the Web Part itself is actually trying to take up a little more space than it needs to, but I have that piece of information showing up and do notice this does not allow data entry. Again, Excel Services is not about collaborating on this content. It's about publishing this content. If I want to just collaborate on this content, I can already do that. I just go to the document library. I could go back and edit this Web Part, change some of the settings again, for example in the Appearance Settings, I could say this does not needs to have a fixed height. Click OK.
If I want to edit the settings of these, I can go back into editing this Web Part. I could say, for example, that I don't want a toolbar there because I don't believe it's necessary. I don't need to autogenerate Web Part titles and Web Part URLs and calculate workbook commands. In fact I can uncheck a lot of this stuff. You will find a lot of settings for the Excel Web Access Web Part, and it is one of the more complex Web Parts there is. I could perhaps change the title here to Total Salary.
I'm going to give the Web Part a fixed height of 60 pixels and click OK. As you see it's quite easy to start changing this information, make it a bit more useful, but what you're really able to do with Excel Services is take that data, and a lot of companies have massive amounts of data stored in Excel, and make it really worthwhile inside SharePoint, without sharing all that confidential information with everybody. Now, there are certain things that will not work completely if you move it into SharePoint.
If your workbook for example, has things like ActiveX controls, references to other external spreadsheets, things like data validation, embedded pictures and clip art, and things like the old-school VBA macros. You will find that some of that content will not work. So oftentimes if you been working with a workbook for several years, your workbook might need a little customizing before publishing to Excel Services. Now the example that I showed which was very straightforward, was using this on a team site and although, you can use Excel Services on a team site, the more typical use for Excel Services is on larger scale websites and in fact there are websites specifically designed for using Excel Services on such as the Business Intelligence Center but there is nothing to stop you using it on any site that you see fit and being able to use Excel workbooks as real first-class data sources within SharePoint as a feature that many over your say C-level executives and financial people are going to find very useful indeed.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64732 Viewers
80 Video lessons · 124333 Viewers
52 Video lessons · 60262 Viewers
59 Video lessons · 46099 Viewers