Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using Excel Services

From: SharePoint 2010 Essential Training

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.

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.

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.

Show transcript

This video is part of

Image for SharePoint 2010 Essential Training
SharePoint 2010 Essential Training

70 video lessons · 47794 viewers

Simon Allardice
Author

 
Expand all | Collapse all
  1. 1m 16s
    1. Welcome
      1m 16s
  2. 16m 34s
    1. What is SharePoint?
      8m 9s
    2. SharePoint roles
      2m 5s
    3. Accessing SharePoint
      4m 48s
    4. The SharePoint product line
      1m 32s
  3. 44m 55s
    1. What is a team site?
      2m 43s
    2. Navigating a team site
      9m 41s
    3. Using team site lists and libraries
      11m 38s
    4. Editing the home page
      9m 31s
    5. Adding a Web Part
      6m 19s
    6. Deleting a Web Part
      5m 3s
  4. 10m 53s
    1. What is a Document Workspace?
      4m 2s
    2. Creating a Document Workspace
      4m 3s
    3. Deleting a Document Workspace
      2m 48s
  5. 6m 3s
    1. What is a Meeting Workspace?
      2m 7s
    2. Creating a Meeting Workspace
      2m 40s
    3. Deleting a Meeting Workspace
      1m 16s
  6. 36m 3s
    1. Exploring the available lists
      5m 30s
    2. Creating a custom list
      8m 44s
    3. Creating a custom view
      6m 43s
    4. Working with libraries
      6m 18s
    5. Using versioning and Check In/Check Out
      8m 48s
  7. 45m 55s
    1. SharePoint and Word
      6m 6s
    2. SharePoint and Outlook
      7m 38s
    3. SharePoint and Excel
      3m 54s
    4. SharePoint and Access
      2m 58s
    5. SharePoint and InfoPath
      11m 42s
    6. SharePoint and PowerPoint
      3m 46s
    7. SharePoint and Visio
      6m 20s
    8. Using SharePoint Workspace
      3m 31s
  8. 32m 8s
    1. What is a site collection?
      3m 56s
    2. Creating a site collection
      6m 35s
    3. Creating a new site
      6m 29s
    4. Customizing a site
      7m 47s
    5. Creating a site template
      7m 21s
  9. 13m 53s
    1. Understanding permissions
      3m 33s
    2. Adding a user to a site
      5m 14s
    3. Deleting a user from a site
      1m 39s
    4. Creating a new security group
      3m 27s
  10. 31m 54s
    1. Using out-of-the-box workflows
      11m 1s
    2. Creating your own workflows with SharePoint Designer
      15m 20s
    3. Creating your own workflows with Visio
      5m 33s
  11. 40m 36s
    1. Using site templates
      5m 49s
    2. Using the web content management features
      10m 40s
    3. Using master pages
      3m 37s
    4. Creating an Enterprise Wiki
      7m 14s
    5. Sharing an Access database with Access Services
      7m 19s
    6. Working with rich media
      5m 57s
  12. 53m 9s
    1. Managing documents and records
      3m 0s
    2. What are content types?
      4m 22s
    3. Creating a content type
      11m 30s
    4. What are document sets?
      2m 12s
    5. Creating document sets
      7m 49s
    6. Creating a Document Center
      4m 37s
    7. Creating a Record Center
      8m 25s
    8. Defining information management policy
      11m 14s
  13. 15m 42s
    1. Using personal and social features
      7m 28s
    2. Creating a SharePoint blog
      2m 48s
    3. Personalizing SharePoint with tags and notes
      5m 26s
  14. 21m 22s
    1. Searching in SharePoint
      4m 26s
    2. Creating a Search Center
      8m 4s
    3. Customizing Search with keywords
      3m 30s
    4. Customizing Search with scopes
      5m 22s
  15. 47m 18s
    1. Using Excel Services
      10m 12s
    2. Creating a Business Intelligence Center
      3m 5s
    3. Using PerformancePoint Services
      12m 3s
    4. Using status indicators
      8m 10s
    5. Using the Chart Web Parts
      6m 33s
    6. Using Business Connectivity Services (BCS)
      7m 15s
  16. 1m 3s
    1. Goodbye
      1m 3s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

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.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

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.


Mark all as unwatched Cancel

Congratulations

You have completed SharePoint 2010 Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

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

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

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:

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:

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

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.