The QUERY Function

08 Jun 2017
functions 2 minute read

Transform, filter and aggregate sets of data using the QUERY function.


query does exactly what it says on the tin. More powerful and sophisticated than the filter function, query allows you to execute complex queries to help answer specific questions about your data.


Let us look briefly at the simplified table of data below. It is trival to use the average function to find the arithmetic mean of all the numeric values in column F, as illustrated below. The same outcome could also be achieved using the query function too, albeit with a little more complexity.

INDEX(QUERY(F1:F5, "select avg(F)", 1), 2, 1)
IdGiven NameFamily NameDisplay NameClassMark
1000JohnDoeJohn Doe11EN30
1001JaneDoeJane Doe11EN35
1002APersonA Person11EN45
1003AnotherPersonAnother Person11EN23

Using query we can also select all the students from the table above who beat the class average, outputting them as a new list.

QUERY(A1:F5, CONCATENATE("select D, F where F > ", INDEX(QUERY(F1:F5, "select avg(F) label avg(F) ''", 1), 2, 1)), 1)

Most powerfully, we can use use a variety of complex query constructs, such as group by, max, min, limit and pivot. It is also fairly trivial to use the function to operate on data sets imported using the importrange function. When using query function on data that is in the same spreadsheet, you have to address the columns using column letters (as shown above). These need to refer to the actual column letters, rather than letters that are relative to the range you are using, e.g. if you are querying range C:F, use the letters C, D, E & F - rather than A-D. When using it on remote / imported sources of data, you need to use the nomenclature of Col1, Col2 etc. These numbers are relative to the range you are importing (as the function won’t have any context of where the source data is positioned within a sheet).

Tagged Google, Sheet

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)