Filtering data quickly and easily using the QUERY Function

• Introduction
• Filtering Imported Data
• Enter Query
• Creating Drop-Downs
• Dynamic Querying

How to create simple ‘drop-down’ filters in sheets to easily and quickly filter an imported range of data rows.

Introduction

Viewing large datasets is something to which a tabular spreadsheet is brilliantly suited. You can use column filters or filter views to help highlight or focus on relevant rows, but sometimes these can be quite clunky for end-users. Filter views are also not available on mobile devices/apps, limiting their use.

Filtering Imported Data

If you have a relatively complex spreadsheet that generated a final output that you wish to share with others, a good way of doing this is by using the importrange and query functions. You can create a new sheet, grant others edit permissions to it, and use it to present data from your original complex sheet to them. Remember that filters live within the sheet itself, so sometimes it’s annoying having other people filter your sheet! Providing them with an output sheet using range imports removes this problem, as the filter is on the imported range in the new sheet, not in your source sheet. It also hides away the complexity of your spreadsheet from people who don’t need to see all the inner workings, and it can speed things up as well because the importing of ranges doesn’t seem to trigger a recalculation of the source sheet. So, if you have a lot of complex formulas that generate a final table - this technique will make it fast and responsive, while still preserving your control of the data model.

A typical range import might look like the following:

IMPORTRANGE("ID_OR_URL_OF_SOURCE_SHEET","Output Data!A:Z")

The user only needs access to the sheet containing the importrange function, not the sheet containing the source data. Once you have been through the authorisation process to ‘Allow Access’ to the source sheet, this access is retained with the sheet.

Enter Query

If you want to provide a pre-filtered view of the data, you can wrap the import with a query function. This trick would allow you to have multiple tabs showing different ‘parts’ of your data from your master (source) sheet. By using ‘1’ as the final parameter for the query function, it will also import the first row of your data as a ‘header’ row. The ‘*’ in your selection criteria means that you would like to import all columns, although you can restrict it if you prefer to by specifying the exact columns (e.g. SELECT Col1, Col2, Col5, Col6). Often it is easier to restrict the columns being imported rather than have to hide them afterwards.

QUERY(IMPORTRANGE("ID_OR_URL_OF_SOURCE_SHEET","Output Data!A:Z"), "SELECT * WHERE Col1 = 10 AND Col2 = 'A'", 1)

Creating Drop-Downs

While the solution above is perfect for fixed views, you can also create dynamic views by leveraging data validation to create a drop-down list that then updates your query formula. To do this, you can either use a fixed list of options or generate a dynamic list from your source data. By way of an example, if you wanted to filter on the contents of Column 5 (E) in your source data, you will first need to build a list of the available values in that column (excluding the header column). The formula below shows this and wraps the query with a sort and unique function to create a list of possible values.

=SORT(UNIQUE(QUERY(IMPORTRANGE("ID_OR_URL_OF_SOURCE_SHEET","Output Data!A2:Z", "SELECT Col5 WHERE (Col5 <> '' AND Col5 is not null)", 0)))

To generate a drop-down for a cell with these possible options, sheets must reference an actual range in your spreadsheet (rather than taking the list from a custom formula). So you need to create a ‘LISTS’ tab in your sheet, or something similar, containing each list you want to use in the columns (e.g. use the formula above for each list/column). Then you can set the criteria for your ‘Data Validation’ to ‘List from a range’ and the range to your column (e.g. ‘LISTS’!A:A). This will then create a drop-down under your filtering criteria cell.

To help you keep track of these filtering cells, it’s a good idea to make them prominent (e.g. large emboldened fonts and a yellow background). This helps users visually locate them, making using the sheet faster. You should also name them so you can directly reference them in your formulas. Names such as FILTER_GROUP or FILTER_NAME will help identify to which columns the filters apply.

Dynamic Querying

Once you have added your filter selection cells, with their associated drop-down lists (dynamically populated from your source), you can then adapt your importing query to use these dynamic parameters. This is achieved by a simple conditional statement (using an [if][8] function) to test whether the filter cell has a length that is greater than zero (e.g. if a filter has been selected from the drop-down). If it does, then this value is used to filter that column, if not, a wildcard is filter is used to return all rows. If you would prefer to use an or filter, just change the statement (or even use another dropdown to allow the user to select this). The wildcard filter means that the number of conditions in the query remains the same, regardless of how many filters are used, which makes writing the query statement a lot easier.

QUERY(IMPORTRANGE("ID_OR_URL_OF_SOURCE_SHEET","Output Data!A:Z"), "SELECT * WHERE " & IF(LEN(FILTER_GROUP)>0, "Col5='"&FILTER_GROUP&"'", "Col5 like '%'") & " AND " & IF(LEN(FILTER_NAME)>0, "Col1='"&FILTER_NAME&"'", "Col1 like '%'"), 1)