Customize a parameter query

You can customize a Microsoft Query or Web 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 Microsoft Query Help or Get external data from a Web page.

Change the custom prompt for a parameter query

Use data from a cell as a parameter value

Use a constant parameter value for a query

Change the custom prompt for a parameter query

  1. On an Excel worksheet, click a cell anywhere in an external data range that was created with a parameter query.

  2. On the Data tab, in the Connections group, click Properties.

    Excel Ribbon Image

  3. In the Properties dialog box, click Connection Properties Button image.

  4. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.

  5. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.

  6. 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.

  7. 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.

  8. 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   The preceding procedure changes the custom prompt only for the current session of the workbook. If you want to permanently save your changes to the custom prompt, save the workbook or change the prompt for the query. For more information about changing a query, see Microsoft Query Help or Get external data from a Web page.

Top of Page

Use data from a cell as a parameter value

  1. On your worksheet, type the values that you want to use as criteria in the query.

  2. Click a cell anywhere in the external data range that was created with a parameter query.

  3. On the Data tab, in the Connections group, click Properties.

    Excel Ribbon Image

  4. In the Properties dialog box, click Connection Properties Button image.

  5. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.

  6. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.

  7. Click Get the value from the following cell.

  8. 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.

  9. Click OK.

  10. To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.

Top of Page

Use a constant parameter value for a query

  1. In Excel, click a cell in the external data range that was created with a parameter query.

  2. On the Data tab, in the Connections group, click Properties.

    Excel Ribbon Image

  3. In the Properties dialog box, click Connection Properties Button image.

  4. In the Connection Properties dialog box, click the Definition tab, and then click Parameters.

  5. In the Parameters dialog box, in the Parameter name list, click the parameter that you want to change.

  6. Click Use the following value.

  7. Type the value that you want to use for the parameter, and then click OK.

  8. To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh.

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language