Video: Using Excel Services

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.

In SharePoint 2010 Essential Training, author Simon Allardice demonstrates the full feature set in SharePoint 2010 and the necessary skills to be a SharePoint site administrator. The course shows how to use SharePoint, create sites and site collections, and plan and design sites and portals. It also covers Office integration, security and permissions, and advanced features such as document management and business intelligence.

Using Excel Services

Excel Services is a feature of SharePoint Enterprise and it's all about gettingworkbooks 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 adocument library and being able to select a document like an Excel workbook, andclick View in Browser or Edit in Browser.Well, here what we've been using is the Microsoft Excel Web application, partof 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 ofinformation 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 fulloff 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 workin your C-level positions.the people who work in working with the numbers often have workbooks that theyconsider to be one version of the truth.These are the authoritative documents that describe how exactly is the companydoing at any particular point in time. But they can't just make this availablefor everybody to see. Things like salary information are very muchconfidential pieces.

We don't want to share that.So oftentimes, you'll hear about people having to work with this spreadsheetand then save off multiple versions of it and trying to extract out all theconfidential 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 haveseveral sheets to the workbook.I have some named charts, I have got Chart 1 and Chart 2, I have got somenamed tables, and in fact in this section too I actually have the totalsalary 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 thatworkbook 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 someparts 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 clickBrowse 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 publisheverything, 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 theConfidential library.Again if I hadn't clicked the option on the previous page, I can also clickPublish 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 Webapplication, 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 theExcel Web application are Chart 1, Chart 2, Salary Total, Table 2, which has alot of stuff, and Table 3.So, I've already put a lot of control over this because I am not sharing thatconfidential salary information.

However because I have the right permissions, I can still use this documentlibrary 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 tosee certain parts of it.Now one of the most common ways that you're going to allow people to see partsof 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 goingto go over to Insert and insert a new Web Part.

The place that you're looking for is under Business Data because there's anExcel Web Access Web Part.So instead of having to look at the entire thing and take over the screen withjust 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'smeant 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 whatworkbook are you try to show.

I have got a little ellipsis button, where I can now go over to my Confidentialdocument library and select that sample workbook, click OK, and now it's going tosay do you want to show the whole thing, do you want to show a named item, and Icould 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 sourceslike 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 mightexpect 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 actuallytrying to take up a little more space than it needs to, but I have that piece ofinformation 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 goto the document library.I could go back and edit this Web Part, change some of the settings again, forexample in the Appearance Settings, I could say this does not needs to have afixed 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'tbelieve it's necessary.I don't need to autogenerate Web Part titles and Web Part URLs andcalculate 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 isone of the more complex Web Parts there is. I could perhaps change the titlehere 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 bitmore 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, andmake it really worthwhile inside SharePoint, without sharing all thatconfidential 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 toother external spreadsheets, things like data validation, embedded pictures andclip 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, yourworkbook might need a little customizing before publishing to Excel Services.Now the example that I showed which was very straightforward, was using this ona team site and although, you can use Excel Services on a team site, the moretypical use for Excel Services is on larger scale websites and in fact there arewebsites specifically designed for using Excel Services on such as the BusinessIntelligence Center but there is nothing to stop you using it on any site thatyou see fit and being able to use Excel workbooks as real first-class datasources within SharePoint as a feature that many over your say C-levelexecutives and financial people are going to find very useful indeed.

Q: In the "Adding a user to a site" movie, the instructor shows how to add a user to SharePoint and demonstrates by adding a user named “gini.” But gini is already set up and recognized by SharePoint. What if I have no users set yet? How can I add someone?

A: SharePoint doesn't store a separate user database; it wants to be pointed to an existing source of users, like Active Directory. If you don't have that, you need to first add your new users as local accounts on the Windows box you installed SharePoint on. Only then will you be able to give them permission on a SharePoint site.

Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Already a member ?

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships.
Learn more

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.