Customize a parameter query - Microsoft Support (2024)

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 More...Less

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.

Newer versionsOffice 2016

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.
See Also
How 15-Minute Delayed Stock Prices Impact Market Analysis | Intrinio Excel performance - Improving calculation performance Excel Power Query tutorial for beginners: how to get, use and auto-refresh
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.