Using Excel Services to collaborate

To make collaborating on workbook data easier than ever, Microsoft Office Excel 2007 can be used with Excel Services — a Microsoft Office SharePoint Server 2007 server that is capable of running Excel Calculation Services.

Instead of just saving a workbook to a SharePoint document library, you can publish a workbook to Excel Services to take advantage of the following benefits:

 Important   The ability to publish an Excel workbook to Excel Services is available only in Microsoft Office Ultimate 2007, Microsoft Office Professional Plus 2007, Microsoft Office Enterprise 2007, and Microsoft Office Excel 2007.

Don’t have the 2007 Office release? You can download a free 60-day trial or buy it now.

In this article


What is Excel Services?

There are three basic components to Excel Services that interact with each other and together form the overall structural design of Excel Services.


Overview of Excel Services

Callout 1 Excel Calculation Services (ECS) is the "engine" of Excel Services that loads the workbook, calculates in full fidelity with Excel Services, refreshes external data, and maintains sessions.
Callout 2 Excel Web Access (EWA) is a Web Part that displays and enables interaction with a Microsoft Office Excel workbook in a browser by using Dynamic Hierarchical Tag Markup Language (DHTML) and JavaScript without the need for downloading ActiveX controls on your client computer, and can be connected to other Web Parts on dashboards and other Web Part Pages.
Callout 3 Excel Web Services (EWS) is a Web service hosted in Microsoft Office SharePoint Services that provides several methods that a developer can use as an application programming interface (API) to build custom applications based on the Excel workbook.

Because Excel Services is a component of Microsoft Office SharePoint Server 2007, you can also take advantage of many SharePoint technology features, such as, controlling, securing, and managing access to workbooks, server-based performance, and the ability to scale well when users are added.


Top of Page Top of Page

What is Excel Web Access?

Excel Web Access is a Web Part that displays data and charts from an Excel Workbook, has a "look and feel" that is similar to Excel (for example, both have sheet tabs, outline buttons, and drop-down arrows), and Excel Web Access provides a number of ways to customize the Web Part.


Using Excel Web Access to share Excel workbooks

Callout 1 The top section contains the toolbar that has several menus, commands, drop-down lists, and navigation buttons for scrolling by page.
Callout 2 The main window displays one or more worksheets in Worksheet view, a named item, such as a chart or an Excel table in Named Item view, and optionally an outline area.
Callout 3 The bottom section displays alerts and prompts, such as refresh data messages.
Callout 4 The Parameter Task Pane has parameter labels, text boxes for data entry, and optional tool tips that provide more information about each parameter. Microsoft Office Excel Web Access

For more information on using Excel Web Access, see Help.


Top of Page Top of Page

How do Excel 2007 and Excel Services work together?

To collaborate by using Excel Services, you must first create a workbook in Excel 2007, and then publish that workbook to Excel Services. In essence, Excel 2007 is the authoring tool and Excel Services is the reporting tool.


How  Excel Services and Excel 2007 work together

Callout 1 A workbook author, often a business analyst, uses Excel 2007 to create the Excel workbook, and optionally to specify named items for viewing, and to define parameters.
Callout 2 The workbook author publishes the workbook to a document library (or to a network or Web folder) in Excel Services, where it is managed and secured by a SharePoint administrator.
Callout 3 The workbook author and other users can create reports, Web Part Pages, and Business Intelligence dashboards that use the workbook.
Callout 4 Many business users can access the workbook by viewing it in a browser, and even refresh the data if the workbook is connected to an external data source.
Callout 5 With appropriate permission, business users can copy the current state of the workbook and any interactions that are made during the current session, such as sorting and filtering to a client computer for further analysis either as an Excel workbook or a workbook snapshot.

Top of Page Top of Page

Excel Services and Information Rights Management

Information Rights Management (IRM) is a way to provide privacy protection for a Microsoft Office document and to ensure that sensitive information is only viewed by appropriate people. For example, you may want to report quarterly financial data only to select members of an executive committee one month before the data becomes publicly available in a financial statement, so that the executive committee has time to prepare public relation responses and make appropriate business decisions.

Windows SharePoint Services Version 3.0 or later supports IRM on a document library and all of the documents in that library (whether those individual documents are enabled with IRM). After the document is uploaded to a document library enabled with IRM, the document, in effect, becomes IRM-enabled.

Excel Services does not support Excel workbooks that have been enabled with IRM, and it cannot open an Excel workbook if it is enabled with IRM or if it comes from a document library that is enabled with IRM. However, if you want to take advantage of IRM, you can publish an Excel workbook without IRM to Excel Services, open the workbook as a snapshot in Excel Services, and then save the snapshot to a document library that is enabled with IRM.

For more information, see the Office SharePoint Server Central Administration Help system.

Top of Page Top of Page

Connecting to data in a secure way

For most Excel workbooks that are published to Excel Services, the data is stored in the workbook. To update Excel Services with changes that you make to this data, you can simply save the Excel workbook. For other workbooks, there are one or more connections to external data sources, such as a database or OLAP cube. These connections contain information about how to locate, log in, query, and access the external data source. Although this connection information can be stored in the workbook, often it is stored in an Office Data Connection (ODC) file (.odc), especially when the data is shared by many users and the connection information needs to be updated. The workbook author or an administrator can create the connection information by using Excel 2007 to author the connection, and then to export the connection information to an ODC file (.odc).

A Data Connection Library (DCL) is a special SharePoint document library that can be defined as a trusted location library and that makes it easy to store, secure, share, and manage .odc files. For example, an administrator may need to move a database from a test server to a production server or update a query that accesses the data. By using one .odc file that is saved in a DCL, administration of this connection information is much easier and the user's access to data is more convenient because all workbooks use the same connection file and refresh operation, whether they are on the client or server computer, and they get up-to-date changes to that connection file. You can even set up Office SharePoint Server and a user's client computer to automatically detect changes to the connection file and use the most up-to-date version of that connection file.

For more information, see Create, edit, and manage connections to external data and the Office SharePoint Server Central Administration Help system.

Top of Page Top of Page

Publishing a workbook to Excel Services

If you have access to Excel Services, you can publish a workbook to that server so that other users can access all or parts of the data that it contains in a browser (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Windows Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.) by using Microsoft Office Excel Web Access.

Defining what you want other users to see

When you publish a workbook to Excel Services, the entire workbook is published on the server, but you can define the parts of the workbook, such as individual worksheets, named ranges, or charts, that you want Excel Services to display in Microsoft Office Excel Web Access. By displaying only specific parts of the workbook and by using Office SharePoint Server 2007 permissions to help protect the workbook from unauthorized access, you can keep data in the workbook confidential while enabling authorized users to refresh, recalculate, and interact with the viewable data.

Setting parameters to allow interactive analysis

You can also define parameters. Parameters are single cells that can have their values defined by Microsoft Office Excel Web Access users. You can use parameters to expose cells that can drive workbook calculation, such as a what-if analysis that is using the values that users enter in cells that are specified as parameters.

For more information, see Publish a workbook to Excel Services and Define workbook parameters for use in Office Excel Web Access.

Top of Page Top of Page

Interacting with an Excel workbook in Excel Services

Although you cannot edit workbook cells in Excel Services, you can interact with the data in a number of ways. To answer specific, unanticipated questions you might have about the data, you can often find and display information by using the following interactive features:

  • View the latest formula results by recalculating data in the workbook.
  • Refresh live data from an external data source, such as a database or an Online Analytical Processing (OLAP) cube.
  • Navigate to different worksheets, parts of worksheets, or selected named items in the workbook, such as a chart or an Excel table.
  • Sort and filter data.
  • Expand or collapse levels of data in a PivotTable report.
  • Temporarily change the values of cells by entering parameters to update the results of a formula or do simple what-if analysis.
  • Obtain different results or views by selecting data from another connected Web Part, such as a Filters Web Part or List View Web Part, on a dashboard or other Web Part Page.

 Note   You can also copy the Excel workbook, open it in Excel 2007 on your computer, and then use all of the features of Excel, including what-if analysis and well-formatted printing.

For more information on using Excel Web Access, see Microsoft Office Excel Web Access Help.

Top of Page Top of Page

 
 
Applies to:
Excel 2007