(Microsoft Excel) How to Set Parameter Values within Microsoft Query

Microsoft Query is an useful tool that allows you to query a Microsoft Access table or query into Excel. It's able to handle SQL queries and also parameter values that can be set within Excel workbook cells. You achieve this by using parameters.

Parameters work the same way they do in Microsoft Access, in that they act as criteria to display relevant information that the end user wants to summarize or analyze.

This video shows you how to set up parameters within Microsoft Query.

First: decide which query/table you want to use. Then put the parameter values within cells. This makes setting everything up easier.

Navigate to the Data tab on the Ribbon and then click on go to the "Get External Data" section, click on "From Other Sources" then "Microsoft Query".

Find the database you want to use, then select the query you want to return. You have the option to include all columns or specific ones. Then make sure you click on "View data or edit query in Microsoft Query". This will make completing the parameter query easier.

From here, navigate to the View section and then click on "Criteria". You will then set your critieria, the same way you do in Microsoft Access. More importantly, Microsoft Query is able to handle layered criteria (i.e. and/or) and Boolean statements (i.e. equal to, less than or equal to, etc.) Make sure the parameters entered are in brackets and you can name them freely. Click off the criteria row and the query should then show blank data. This means the process is working as intended and once you enter in valid options, the query will update accordingly. Click on the "X" on the top, right side of the screen and you'll be prompted set the cell range location of the query.

After that you'll be asked to set your parameter values to a cell reference, if you want to keep the reference the same, and if you want the reference to update on change. You can also set multiple criteria by referring different cell values. You can essentially create a living lookup with these steps.

Insure your cell phone, TV, video game console, and much more with an AKKO protection plan. Click on the link below to learn more:

If you are in the market for the newest version of Microsoft Office, you can purchase a lifetime membership from Stack Social for Windows of Mac from the link below: