Change workbook parameters in Excel Services

Although you cannot directly edit cells in a Microsoft Office Excel workbook by using Excel Services, before saving the Excel workbook, a workbook author can define one or more parameters, each of which corresponds to a named cell. You can then indirectly change the cell by using the Parameters Task Pane to enter a new value, or by using Filter Web Parts.

For example, a workbook author can create a simple mortgage calculator that requests the following cell values: interest rate, loan period, and loan amount. You can then enter these values in the Parameters Task Pane to calculate a monthly payment.

1. For each parameter, the Parameters Task Pane displays a label, text box, and optional tooltip.

2. Enter the parameter values, and then click Apply.

3. The values are displayed and the results are calculated.

Note:  Another way to set a parameter value is to pass the value from one Web Part, such as a Filter Web Part, to the Excel Web Access Web Part by using a Web Part connection. For more information, see Connect Filter Web Parts to Excel Web Access.

What do you want to do?

Learn more about workbook parameters

Change a workbook parameter

Learn more about workbook parameters

You can use workbook parameters to do simple "what-if" analysis (such as comparing returns on an investment (ROI) with different input values), enter variable input to a calculation model (such as a monthly payment mortgage calculator), and synchronize connected Web Parts on a Web Part Page (such as a dashboard that contains multiple Excel Web Access Web Parts and that simultaneously updates based on a selected value in a Filter Web Part).

Keep in mind the following considerations when you define workbook parameters in Microsoft Office Excel 2007 and use them in Excel Services:

Defined names and cells    

• A named cell must contain a value, not a formula.

• A named cell can be a single cell or a merged cell. There is one cell for each parameter; you cannot use a range that contains two or more cells.

• The cell cannot be located in a PivotTable. However, a named cell can contain a report filter field from a PivotTable report, but you can only modify it by using a Filter Web Part, not by using the Parameters Task Pane.

• The defined name must be an absolute reference to a single cell; you cannot use a relative reference. The defined name must not reference another name.

• The cell reference must be a cell within the workbook; you cannot use an external cell reference.

• The cell cannot have data validation defined, cannot be locked, and cannot be on a protected worksheet.

  Parameters    

• The maximum number of parameters for each workbook is 255.

• The maximum length of a parameter value is 1024 characters.

• The parameters are displayed in the Parameters Task Pane in alphabetical order.

• The name of the parameter is the defined name of the cell.

• The optional parameter description is derived from the comment of the defined name, and is used as a tooltip to assist users when they enter parameters.

For more information on defining parameters, see Microsoft Office Excel 2007 Help.

Note: When a workbook is saved with one or more selected named items and you display the workbook in Named Item view, you can still view the Parameters Task Pane and set parameters, whether or not the named cell of the parameter is contained in those named items. For example, you might want to display a chart item, but allow a user to update the chart by entering a parameter value for a cell value that is not located in any of those named items.

Top of Page

Change a workbook parameter

1. If the workbook Parameters Task Pane is not displayed, expand the pane to see all of the parameters.

   Note: If the workbook Parameters Task Pane is not available, the workbook author did not specify any parameters in the Parameters tab of the Excel Services Options dialog box before saving the workbook, the Web Part author has cleared the Parameter Modification property, or the Web Part author has cleared the All Workbook Interactivity property.

2. For each parameter that you want to use, enter a value in the text box. If the workbook author has defined the parameter with a description, hover over the parameter name or text box to see additional information about the parameter.

3. To see the results, click Apply at the bottom of the Parameters Task Pane. You may have to scroll to the bottom to see the Apply button.

4. To remove any values in the Parameters Task Pane and enter new values, click Clear at the bottom of the Parameters Task Pane.

Note: When you change a workbook parameter, the changes to the workbook are seen only by you, and not other users. When you close Microsoft Office Excel Web Access, the changed values are not saved with the workbook.

Top of Page