Use parameters to make worksheets and dashboards more interactive. Parameters are used to dynamically replace values used in calculations. Parameters are always exposed on the worksheets they are defined for, and can be exposed on any dashboard that references the worksheet.
What-If analysis and User Input Analysis are made easier using parameters. Viewers can change the value of the parameter, changing the value everywhere that the parameter is used.
Parameters can be Text, Number or a Date. They can be left as a box that accepts any input of the correct type, or can be defined in a list.
How to Create a Parameter
- Click on the parameters tab on the top right of your worksheet.
- Click the + button.
- Define your parameter.
Reference a Parameter in a Formula
Parameters can be referenced by name in any formula. Simply type in the name of the parameter when writing your formula in the formula bar.
One way to use parameters is to take the place of constants. This is helpful when you want it to be obvious what value is used or when you want to be able to change the input value quickly. Parameters are helpful when performing “what if” analysis. What if sales grew by 7% instead of 5%? With parameters, you just have to change the 5 to a 7 in one place.
Filtering Based on Parameters
Setting up a filter based on a parameter requires two steps: creating a column with the filter conditions, filtering based on the results of the column.
Setting up a filter to only show customers that have spent more than X dollars takes just a few steps. First, set up a parameter with a number as the input. We’ll call it [Spend]. Next, determine how much each customer spent. Then, set up a column with the following formula.
[Customer Spend] >= [Spend]
Now, filter your new column to only show answers where the result is True. You can set whatever spend limits you like using the parameter, and the worksheet will re-filter. This parameter is always visible on the Worksheet, and can also be added to any dashboard built with the worksheet.
Add a Parameter to a Dashboard
When you add a visualization to a dashboard from a worksheet that uses parameters, you have the option to make the parameter visible on the dashboard.
Available parameters will automatically appear on the right-hand side of the screen when editing the dashboard. You can choose to show or hide each available parameter by clicking on the eye icon in the top right of each parameter box.