You can customize a Microsoft Query parameter query by changing the prompt, by using data from a cell as the prompt, or by using a constant.
For more information about creating parameter queries, see Use Microsoft Query to retrieve external data.
Note: The following procedures do not apply to queries created by using Power Query.
Change the custom prompt for a parameter query
-
On your worksheet, click a cell anywhere in the external data range that was created using the parameter query.
-
On the Data tab, in the Queries & Connections group, click Properties.
-
In the Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
-
In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
-
In the Prompt for value using the following string box, type the text that you want to use for the prompt, and then click OK. The custom prompt can contain up to 100 characters.
-
To use the new custom prompt and refresh the data, click the arrow next to Refresh All on the Data tab, in the Queries & Connections group, and then click Refresh.
-
In the Enter Parameter Value dialog box, the new prompt is displayed.
Note: Optionally, to prevent being prompted again during each refresh operation, you can select the Use this value/reference for future refreshes check box. If you use data from a cell as the parameter value, select the Refresh automatically when cell value changes check box.
-
On your worksheet, type the values that you want to use as criteria in the query.
-
Click a cell anywhere in the external data range that was created with the query.
-
On the Data tab, in the Queries & Connections group, click Properties.
-
In the Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
-
In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
-
Click Get the value from the following cell.
-
On the worksheet, click the cell that contains the value that you want to use.
If you want to refresh the data whenever you change the value in the cell, select the Refresh automatically when cell value changes check box.
-
Click OK.
-
To refresh the data, click the arrow next to Refresh All on the Data tab, in the Queries & Connections group, and then click Refresh.
-
On your worksheet, click a cell in the external data range that was created with the query.
-
On the Data tab, in the Queries & Connections group, click Properties.
-
In the Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
-
In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
-
Click Use the following value.
-
Type the value that you want to use for the parameter, and then click OK.
-
To refresh the data, click the arrow next to Refresh All on the Data tab, in the Queries & Connections group, and then click Refresh.
Change the custom prompt for a parameter query
-
On your worksheet, click a cell anywhere in the external data range that was created with the parameter query.
-
On the Data tab, in the Connections group, click Properties.
-
In the Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
-
In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
-
In the Prompt for value using the following string box, type the text that you want to use for the prompt, and then click OK. The custom prompt can contain up to 100 characters.
-
To use the new custom prompt and refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.
-
In the Enter Parameter Value dialog box, the new prompt is displayed.
Note: Optionally, to prevent being prompted again during each refresh operation, you can select the Use this value/reference for future refreshes check box. If you use data from a cell as the parameter value, select the Refresh automatically when cell value changes check box.
Note: If you want to save your changes to the custom prompt, save the workbook. You can also edit the query to change the prompt. For more information about editing a Microsoft Query query, see Microsoft Query Help.
-
On your worksheet, type the values that you want to use as criteria in the query.
-
Click a cell anywhere in the external data range that was created with the parameter query.
-
On the Data tab, in the Connections group, click Properties.
-
In the Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
-
In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
-
Click Get the value from the following cell.
-
On your worksheet, click the cell that contains the value that you want to use.
If you want to refresh the data whenever you change the value in the cell, select the Refresh automatically when cell value changes check box.
-
Click OK.
-
To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group, and then click Refresh.
-
In Excel, click a cell in the external data range that was created with a parameter query.
-
On the Data tab, in the Connections group, click Properties.
-
In the Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Parameters.
-
In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.
-
Click Use the following value.
-
Type the value that you want to use for the parameter, and then click OK.
-
To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group, and then click Refresh.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
