Dynamically filtering data before importing from SQL Server in Excel

You can absolutely do this with Tables, as well as Powerpivot and Data Explorer. The easiest, and my preferred method currently, is to create a parameterized query in Excel. To do this use the Query Wizard and at the end add constraints for each parameter you want to pass. Once this is done, you can edit the Connection Properties and assign the Parameter to be returned as a static value, dialog box or a spreadsheet cell.

1. Select your data source using MS Query-this is required to be able to pass parameters back to SQL; Data>Other Sources>Microsoft Query.
2. Choose Data Source, select your SQL data source here. I'll generally leave all of these options blank until I get to the Finish window, then I'll select View data or edit query in MS Query.
   • Note: I typically pre-process the info I want from SQL into a single view that I can connect to here, but you can generally pass any select statement. I haven't tried calling a Stored Procedure, so I'm not sure how that would work.
3. In the MS Query Window, click the Show/Hide Criteria button and choose a Criteria field. This will be what calls your parameters to pass back to SQL.
   • Example: If you have a date field and select it in the Criteria field, then add a Value of Between [start] and [end]. This will create a parameter and MS Query will ask you to fill both values and immediately return the appropriate data set. If you check the SQL statement it has created, it will have added a WHERE(datasource.date Between ? and ?) clause.
4. Select File>Return Data to Excel, then Choose Table.
5. To have the table pull your query parameter automatically, select a cell in your table, the go to Data>Properties>Connection Properties>Definition>Parameters.
6. For each parameter, you can choose for it to
   1. Prompt you to provide a value; or
   2. Use a specific value; or
   3. Get a value from a spreadsheet cell, with the option to automatically update your table data whenever that cell value changes.
      • As an additional option, you can use Data Validation on the cell you choose for your parameter, allowing you to limit the possible values to choose from. You could even have the validation cells come from a table that is fed from values from your database, ensuring that only values held in the database could be selected.
7. Set your paramater value for each parameter and click OK through all of the dialog boxes.
8. Finally, to minimize the file size, in the Connection Properties>Usage tab, you can select Refresh Data when opening file and Remove data from the external range before saving the workbook. This will dump the connected data when the file isn't actively in use, minimizing file size.

I'll often create a Pivot Table based upon a Table fed from a paramterized query to return values for automatically updated, specific date ranges (e.g. prior 6 months).

EDIT: To return multiple values for use in a SQL IN clause, you just need to modify the Criteria window in the MS Query box. For example, if you have a field name of Country, select it in the Criteria Field, then for Value enter IN([First],[Second],[Third]). Choose Return Data to Excel and MS Query will ask to manually input values. Once you're back in Excel you can modify the parameters again, and point them to three cells in your spreadsheet, each of which can be constrained by Data Validation. While there's probably a limit to how many parameters you can pass like this, Excel easily handles three, so a few more isn't likely out of the question.