When you query data in Excel, you might want to use an input value - a parameter - to specify something about the query. To do this, you create a parameter query in Microsoft Query:
-
Parameters are used in the query’s WHERE clause – they always function as a filter for retrieved data.
-
Parameters can prompt the user for an input value when the query is run or refreshed, use a constant as the input value, or use the contents of a specified cell as the input value.
-
A parameter is part of the query it modifies, and cannot be reused in other queries.
Note If you want the other way to create parameter queries, see Create a parameter query (Power Query).
Procedure
-
Click Data > Get & Transform Data > Get Data > From Other Sources > From Microsoft Query.
-
Follow the Query Wizard steps. On the Query Wizard – Finish screen, select View data or edit query in Microsoft Query and then click Finish. The Microsoft Query window opens and displays your query.
-
Click View > SQL. In the SQL dialog box that appears, find the WHERE clause – a line starting with the word WHERE, typically at the end of the SQL code. If there is no WHERE clause, add one by typing WHERE on a new line at the end of the query.
-
After WHERE, type the field name, a comparison operator (=, <, >, LIKE, etc.), and one of the following:
-
For a generic parameter prompt, type a question mark (?). No helpful phrase is displayed in the prompt that appears when the query is run.
-
For a parameter prompt that helps people provide valid input, type a phrase enclosed in square brackets. The phrase displays in the parameter prompt when the query is run.
-
-
After you finish adding conditions with parameters to the WHERE clause, click OK to run the query. Excel prompts you to provide a value for each parameter, then Microsoft Query displays the results.
-
When you are ready to load the data, close the Microsoft Query window to return the results to Excel. The Import Data dialog box opens.
-
To review your parameters, click Properties. Then in the Connection Properties dialog box, on the Definition tab click Parameters.
-
The Parameters dialog box displays the parameters used in the query. Select a parameter under Parameter name to review or change How parameter value is obtained. You can change the parameter prompt, enter a specific value, or specify a cell reference.
-
Click OK to save your changes and close the Parameters dialog box, then in the Import Data dialog box click OK to display the query results in Excel.
Now your workbook has a parameter query. Whenever you run the query or refresh its data connection, Excel checks the parameter to complete the query’s WHERE clause. If the parameter prompts for a value, Excel displays the Enter Parameter Value dialog box to collect the input – you can type a value or click a cell that contains the value. You can also specify that the value or reference you provide should always be used, and if you use a cell reference you can specify that Excel should automatically refresh the data connection (i.e. run the query again) whenever the value of the specified cell changes.
