Create a parameter query

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. The way you create parameter queries and how they behave depends on whether you use Microsoft Query or Power Query.

Tip: Power Query parameters are very different from parameters used in SQL-based queries. Moreover, you can use a query instead of an actual parameter if all you need is to filter data. Consider reading the Power Query Example sections before you create parameters in Power Query.

Microsoft Query

Power Query

How parameters affect queries

Parameters are used in the query’s WHERE clause – they always function as a filter for retrieved data.

Parameters can be used in any query step. In addition to functioning as a data filter, parameters can be used to specify such things as a file path or a server name.

Parameter input options

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.

Parameters don’t prompt for input. Instead, you can change their value using the Power Query Editor. Or, instead of a bona fide parameter you can use a query that refers to an external location with a value you can easily edit.

Parameter scope

A parameter is part of the query it modifies, and cannot be reused in other queries.

Parameters are separate from queries - once created, you can add a parameter to queries as needed.

  1. Click Data > Get & Transform Data > Get Data > From Other Sources > From Microsoft Query.

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

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

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

      SQL view of MS Query emphasizing the WHERE clause

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

      SQL view of MS Query emphasizing the WHERE clause

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

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

    Import Data dialog box in Excel

  7. To review your parameters, click Properties. Then in the Connection Properties dialog box, on the Definition tab click Parameters.

    Connection Properties dialog box

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

    MS Query Parameter dialog box

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

Note: This topic assumes you know how to create a connection to an Access database by using Power Query. For more information, see Connect to an Access database.

You can use parameters in more Power Query scenarios than just filtering data – any step of a Power Query query can have parameters. For example, you can use a parameter to specify parts of the connect string in the Source step, such as a file name.

Power Query parameters have names. To use a parameter, you refer to it by name in the formula for a step. For example, suppose you want to review data about web pages you maintain, and you want to filter the data by publication date. Although you can always just use the built-in filters in the query preview, using a parameter to provide a date for filtering will save time and give you more flexibility. Let’s walk through this example.

In an empty workbook, we create a connection to the Access database that has the web traffic records we want – including fields that indicate when each page was originally published. Loaded into Power Query, it looks like this:

Power Query editor depicting loaded data

Since we want to filter by date, we change the data type of the column we’re using, FirstPublishDate. It’s Date/Time data in the source, but we don’t care what time of day publication occurred and having to specify it might get tiresome – so we’ll change it to the Date data type.

Power Query Editor displaying results

Next, we create a parameter for limiting the results by the date the page was originally published. Click Home > Parameters > Manage Parameters to open the Parameters dialog box.

Power Query Parameters dialog

Click New, and the form displays a new parameter named Parameter1 with no other information.

We change some parameter properties:

  • Change Name to FirstPubD

  • Change Description to The date the page was first published.

  • Change Type to Date so that the parameter only accepts date values

  • Set Current Value so the parameter doesn’t filter out all the rows when we haven't provided input - we use 1/1/2010.

Tip: The name and description should provide enough context to help people understand how and why to use the parameter. Even if you're the only person who will use the parameter, you might need a reminder from time to time.

We click OK to create the parameter and see it in the Power Query Editor.

Power Query Editor displaying a parameter

Now our parameter is listed in the Queries panel – we can select it there to display it in the main panel, or we can right-click it for more options. When a parameter is selected, we can edit the Current Value in the main panel or click Manage Parameter to change its other settings.

Now we can use this parameter in our original query. We click the original query in the Queries panel to display it. We want to use our parameter to filter the results based on the date of first publication, so next we’ll select the FirstPublishDate column, click the filter/sort arrow at the right edge of the column heading, point to Date Filters, and then click After….

Power Query Editor displaying a date filter menu

In the Filter Rows dialog box, we select Parameter from the list of choices in the filter.

Filter Rows dialog box

Enter or select a value is replaced with a list of available parameters. There’s only one, the one we just created, FirstPubD.

Filter Rows dialog box displaying a selected parameter

We select it and click OK. The Power Query Editor loads the query using the new parameter as a filter.

Power Query Editor displaying filtered results

To test the parameter, we change its value to 1/1/2018.

Power Query Editor displaying a parameter

We refresh the query, which now only shows rows having a FirstPublishDate after 1/1/2018.

Power Query Editor displaying filtered results

Now we have a query that filters by date using a parameter. To filter the results by FirstPublishDate we no longer have to find the field, click the filter/sort arrow, choose the After… filter type, and enter a date value – we can just change the value of FirstPubD and refresh our query. Moreover, we can reuse the new parameter, for example, if we decide to pull a different set of fields from the original data source into a new worksheet but still want to include FirstPubDate and use it to filter results.

Parameters are clearly very useful, but we still must use the Power Query Editor to change the parameter’s value. We’d like to be able to change the filter value without opening the Power Query Editor. To do this, we’ll create a table on the worksheet where the query loads and a new Power Query connection to the table, and then use the new query to filter our main query.

On the worksheet where our query loads, we insert some rows above the imported data. Then we create an Excel table with one row to hold our parameter value.

Excel workbook displaying a parameter table and data loaded from Power Query

To use the new table to filter queries, we need to connect to it in Power Query. We create a connection to the table by selecting it and then clicking From Table/Range on the Data tab. The new connection opens and displays the new table in the Power Query Editor.

Excel table data loaded in the Power Query Editor

Because the data loaded as the Date/Time data type, we need to change it to the Date data type so it matches our parameter, so we click Home > Transform > Data Type > Date.

Mouse over the Data Type command in the Transform group on the Home tab of the Power Query Editor ribbon.

We also rename our query to something more meaningful than Table2. To make it clear what it's for, we name it FirstPubDate.

Power Query Editor with the Name box highlighted

Because we want to pass a value, not the table itself, we need to drill down to the date value. To do so, we right-click the value in the previewed data and then click Drill Down.

Power Query Editor context menu for a field value

The preview now displays the value instead of the table.

Power Query Editor displaying a single date value

We don’t need the new query’s data to load anywhere – its data is already on the worksheet where we want it. We just need the connection so Power Query can get the parameter value. So, we click File > Close & Load To… to open the Import Data dialog box, and then we select Only Create Connection.

Import Data dialog box with Only Create Connection option selected

Now we have a query called “FirstPubDate” that pulls a single date value from a table on the worksheet just above where our main query loads. Now we just need to use this query as a parameter for filtering our main query. So, we open the main query and edit the step that filters rows using the FirstPublishDate column. We expand the formula bar and select the parameter we created previously (FirstPubD). Then, we type an “a” after FirstPubD - because the new query’s name starts with the same letters as the parameter, Power Query displays it as an option to pick.

Power Query Editor Formula bar expanded

We select it, then click outside the formula bar to apply the step.

Power Query Editor with data loaded

Everything looks correct, so we exit the Power Query Editor and save our changes. To test the parameter, on the report worksheet we change the value of the cell in the table at the top to 5/4/2019, then we refresh the connection to see the filtered data.

Filtered data in Excel

Our new filter works! So we save and close the workbook. Now anyone who uses the workbook can specify a date of first publication to use as a query filter – right there on the same worksheet where the query loads.

  1. Click Data > Get & Transform Data > Get Data > Launch Power Query Editor.

  2. In the Power Query Editor, click Home > Parameters > Manage Parameters.

  3. In the Parameters dialog box, click New.

  4. Set the following as needed:

    • Name - this should reflect the parameter's function, but keep it as short as possible.

    • Description - this can contain any details that will help people correctly use the parameter.

    • Required - select to make this parameter require a value.

    • Type - this specifies the data type the parameter requires.

    • Suggested Values - if desired, add a list of values or specify a query to provide suggestions for input.

    • Default Value - this only appears if Suggested Values is set to List of values, and specifies which list item is the default.

    • Current Value - depending on where you use the parameter, if this is blank the query might return no results. If Required is selected, Current Value cannot be empty.

  5. Click OK to create the parameter.

  1. Open a query in the Power Query Editor.

  2. Click the arrow at the right edge of the header of a column you want to use to filter your data, and then choose a filter from the menu that appears.

  3. In the Filter Rows dialog box, click the button to the right of the filter condition, and then do one of the following:

    • To use an existing parameter, click Parameter, and then select the parameter you want from the list that appears on the right.

    • To use a new parameter, click New Parameter..., and then create a parameter.

  1. On the worksheet where the query you want to filter is loaded, create a table with two cells: a header and a value.

  2. Click the value, then click Data > Get & Transform Data > From Table/Range.

  3. In the Power Query Editor, make any adjustments to the table connection (for example, changing the data type or the name), then click Home > Close > Close & Load > Close & Load To....

  4. In the Import Data dialog box, click Only Create Connection, optionally select Add to Data Model, and then click OK.

  5. Open the query you want to filter in the Power Query Editor.

  6. Click the arrow at the right edge of the header of the column you want to use to filter your data, and then choose a filter from the menu that appears.

  7. Do one of the following:

    • Select a value from the drop-down list of values (these come from the queried data).

    • Select a value using the button on the right edge of the filter condition.

  8. Click the arrow at the right edge of the formula bar to display the whole query.

  9. The filter condition follows the word each:

    • The name of the column that is being filtered appears in square brackets.

    • The comparison operator immediately follows the column name.

    • The filter value immediately follows the comparison operator, and ends at the closing parenthesis. Select this entire value.

  10. Start typing the name of the table connection you just created, and then select it from the list that appears.

  11. Click Home > Close > Close & Load.

    Your query now uses the value in the table you created to filter the query results. To use a new value, edit the cell contents and then refresh the query.

See Also

Create a drop-down list

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×