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:
Workbook data can be presented in a trusted location that is set up for your organization.
You can specify the parts of the workbook that you want other people to see — no need to hide workbook data that you don't want them to see.
You can check a workbook for compatibility with Excel Services so that you can avoid using features that are not supported in Excel Services.
Workbooks that are published to Excel Services can be accessed in a browser by using Microsoft Office Excel Web Access and calculated with full Excel calculation fidelity.
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.
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.
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.
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.
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.
1. The top section contains the toolbar that has several menus, commands, drop-down lists, and navigation buttons for scrolling by page.
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.
3. The bottom section displays alerts and prompts, such as refresh data messages.
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.
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.
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.
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.
3. The workbook author and other users can create reports, Web Part Pages, and Business Intelligence dashboards that use the workbook.
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.
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.
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.
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.
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 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.
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.