Using Excel Services

In every organization, knowledge workers need to analyze increasingly more data more frequently. This version of Excel supports vastly larger worksheets than the prior version to meet these increased demands. The worksheets and workbooks you can create in Excel 2007 are very large. A single workbook can easily contain millions of values-a challenge for a standard desktop or laptop. Although Excel data stores are increasing in size, the number of controls placed on company data is also increasing, particularly for publicly traded companies. Excel Services, new in Office SharePoint Server 2007, is a special server version of the calculation capabilities found in Excel to support and secure Excel workbooks in a multiuser environment by letting other users access all or part of the workbook in their browsers using Office Excel Web Access.

In many organizations, it's difficult to keep track of which version of an Excel workbook represents "the truth." Even when you share a workbook, users create and modify their own copies and then e-mail them to other users. When you save a workbook to Excel Services, the entire workbook is secured on the server. Users cannot open this workbook using Excel, so the workbook stored on the server becomes the "one source of truth." Some workbooks should not be shared, however, because they contain proprietary information or are subject to regulatory constraints such as the Sarbanes-Oxley Act in the United States. By publishing the workbook to Excel Services, you can distribute appropriate information from the workbook and still be able to guarantee compliance with governmental regulations or your company's policies.

Excel Web Access is a visually rich browser client that requires no additional installation. It supports most of the newer formatting options from Excel 2007, including conditional formatting and data visualizations. In addition, Excel Web Access is interactive. Users can filter, apply outlining, and use PivotTables for data analysis. With some planning on your part, Excel Web Access supports what-if analysis using familiar Excel tools such as the goal-seeking feature and the Solver add-in. Users do not need to have Excel 2007 to use Excel Web Access.

You determine which parts of the workbook are displayed in Excel Web Access: specific worksheets, ranges, or charts. You can also use parameters to let users type data in specific cells. (Parameters are single cells where users can enter data. ) Each parameter must be named.

For information on naming cells, see "Naming Cells and Cell Ranges" on page 441.

Excel Services is a calculation engine that supports most, but not all, of the functionality of Excel. For example, Excel Services does not support XML maps, data validation, or comments. For a complete list of unsupported features, click the Excel Help button, and search for help on Excel Services differences.

Publishing a Workbook to Excel Services

Before publishing the workbook to Excel Services, make sure you have named any cells where you want users to enter data (for example, a starting date and ending date used as report parameters). Then follow these steps to save the workbook to Excel Services:

1. Click the Microsoft Office Button, click Publish, and then click Excel Services to open the Save As dialog box.

2. In the File Name box, type the path to the server with Excel Services and a file name (for example, http://servername/sitename/filename).

3. Click the arrow to the right of Save As Type, and select a file type: either Microsoft Office Excel 2007 XML-based file format (.xlsx) or Office Excel 2007 Binary file format (.xlsb).

4. Click the Excel Services Options button to open the Excel Services Options dialog box, shown in Figure 25-10.

5. On the Show tab, display the list to select Entire Workbook, Sheets, or Items In The Workbook.

6. If you select Sheets or Items In The Workbook, clear the check boxes for the worksheets or items you do not want to show in Excel Web Access.

7. If you want to define parameters, click the Parameters tab, shown in Figure 25-11.

8. Click the Add button to open the Add Parameters dialog box. Select the check boxes of the parameters you want to add.

9. Click OK to add the parameters. Click OK to close the Excel Services Options dialog box.

10. Click Save to save the workbook to Excel Services.

Figure 25-10: Use the Show tab in the Excel Services Options dialog box to show or hide worksheets or items in the workbook.

Figure 25-11: Use the Parameters tab in the Excel Services Options dialog box to specify named cells that are user editable.

As the owner of the workbook stored in Excel Services, you can open the workbook in Excel to modify it. When you save the workbook, the changes are automatically reflected in Excel Services and the Excel Web Access client. Excel Web Access users can view the workbook, worksheets, or items you specified. They can edit the values in any cells you set as parameters.