A workbook parameter is a special variable that represents a single cell in a worksheet. Workbook authors can define parameters so that users can enter new values for that cell whenever they want a new calculation. This is helpful when you want to calculate simple "what-if" scenarios, or when the workbook calculations depend on values that might change according to the situation.
For example, suppose that you want to use a workbook to calculate monthly payment amounts for a mortgage loan. In this case, the payment amount depends on factors that might change, such as the interest rate and the amount of the loan. Thus, the author might specify parameters to enable users to input values. Each time the user enters new information for the parameters, the workbook can calculate a new monthly payment amount automatically.
When you open a workbook that contains parameters in the browser, the Web-based viewer displays all the parameters for the workbook in a special Parameters pane. The following illustration shows the Parameters pane. It contains the parameters Interest_Rate, Length_of_Loan, and Loan_Amount for a mortgage calculator.
-
The Parameters pane contains a labeled text box for each parameter. The user can enter values for each parameter in the associated text box. In this example, the author also created a tooltip to explain the requirements for the Interest_Rate text box.
-
The user enters values for each parameter, and then clicks Apply.
-
The workbook uses these values to calculate results (in this case, the Monthly Payment amount.)
In this article
Use a parameter in a workbook
When you open a workbook that contains parameters in an Excel Web Part, the Web-based viewer displays a Parameters pane. The Parameters pane contains any parameters that the workbook author defined in Excel.
You are the only user who can see the values that you enter for parameters, and the only one who can see any calculation changes that result from those values. The changes are not visible to any other user. In addition, the parameter values and resulting changes are temporary. The Web-based viewer does not save parameter values with the workbook.
-
For each parameter that you want to use, enter a value in the text box. The maximum length for a parameter value is 1024 characters.
-
To see the results, click Apply at the bottom of the Parameters pane.
-
To remove values in the Parameters pane, click Clear at the bottom of the Parameters pane.
What if I do not see a Parameters pane?
There are several reasons why you might not see a Parameters pane:
-
The workbook does not contain parameters.
-
All the parameters in the workbook are on report field filters from one or more PivotTables.
-
The workbook is part of an Excel Web Access Web Part that does not enable displaying the Parameter pane.
-
The workbook is part of an Excel Web Access Web Part that does not enable workbook interactivity, or does not enable parameter modification.
Learn more about authoring parameters
When a workbook author saves or publishes a workbook to a SharePoint document library, the author can specify parameters for use in the published workbook. If the workbook is in Excel Services, you can use parameters to pass information between different Web Parts by using Web Part connections. For example, you can use parameters to help synchronize different Web Parts in a dashboard.
You can use workbook parameters to provide input in many situations. For example, you might want to explore several “what-if” scenarios. You might compare different returns on an investment (ROI) with different input values, or enter variable input to a calculation model (such as a monthly payment mortgage calculator).
A workbook parameter must represent a single named cell (not a named range or table) in a worksheet. The name of the cell becomes the name of the parameter.
Learn more about named cells for parameters
A workbook parameter represents a specific named cell in the workbook. For best results, consider the following rules when you define workbook parameters in Excel 2010:
-
A named cell can be a single cell or a merged cell. There is one cell for each parameter; a parameter cannot represent a range that contains two or more cells.
-
The named cell must be a cell in the workbook. The cell cannot be located in a table or in a PivotTable, and you cannot use an external cell reference.
-
A named cell can contain a report filter field from a PivotTable report. However, the report filter field will not appear in the Parameters pane. To change the range filter field, you can use a Filter Web Part.
The named cell must be an absolute reference to a single cell. You cannot use a relative reference.
-
The named cell must not reference another named cell.
-
The cell cannot have data validation defined, cannot be locked, and cannot be on a protected worksheet.
