Excel Drop-down Field Filter

Question

An Excel table is populated via a query to a SQL view.  The data table is large and grouped by the field 'Batch'  We need to add a drop-down filter above the Excel table to select by 'Batch' such that by selecting a particular 'Batch' only that Batch's data shows in the Excel table.  How is this done? Thanks in advance for any help help anyone can offer here.  

Evan

Answer 1

This is the case: the SQL query populates a table on the Excel spreadsheet with drop-downs.  The problem with this route is that all the data must be brought over to the table first in order to filter this way.

We need to filter via the SQL query by going into the Data tab -> Refresh All -> Connection Properties -> Definition tab -> Command Text field -> then type in the specific field's criteria in the SQL query's Where clause.

This limits the amount of data sent to the Excel table -- sending over only that data pertaining to the criteria -- this keeps the table small, which is important for people who need to access it out in the field.

So I'd like to put a Drop-down box onto the Excel Spreadsheet above the table -- the drop-down box needs to access the SQL query -- populating 'DISTINCT' Batch #'s from the 'Batch' field -- instead of going the circuitous route above.

Not sure if this question should really be posted on a SQL forum but as the End table is in Excel - thought it best to post here.

Thanks for your responses -- Evan

Answer 2

Hi,

If the Excel Table is coming from a query, then the Excel Table should be in Table format with Auto filter drop down arrows appearing in all column headings.

You can simply filter on any value in the Batch column.

Hope this helps.

Answer 3

Click in the row with the field names.

On the Home tab of the ribbon, in the Editing group, click Sort & Filter > Filter.

This will add a dropdown arrow to each field name. Click the dropdown arrow in the Batch cell to select a value.