Learn Query Function with Examples in Google Sheets

0
131
Learn Query Function in Google Sheets

You should learn QUERY function in order to manage your Google Sheet data efficiently. Query is simple to learn. Just use the Query function frequently to become an expert in using it. There may be two reasons why you didn’t try this function so far. First one you are not aware of this powerful function or you may think it’s something complicated. You can learn query function with examples. Find below few Google Sheet Query function examples to learn this so powerful function.

Below is the syntax of Query function. You can just read it and forget. It has nothing to do with learning Query.

QUERY(data, query, headers)

When you start to learn Query function with examples below, you can understand the various formations of this function. So let’s begin.

Learn Query Function in Google Sheets

Learn Query Function with Examples

Create the below sample data in a new Google Sheet file. We can use this sample data to learn different query function formations.

To easily follow our tutorial I suggest you to rename the file name to “Learn Query” and the sheet tab name to “DataList“. I may use these names in the Query formulas below.

Learn Query Function with Examples

If you have finished entering the above data, move to the next steps.

In this tutorial you can learn the following Query function formations.

  1. Choose, or you can call select, only desired data from a data like our sample above. For example, there are six columns in the above data. You can get the data of any selected columns in a new sheet.
  2. Select and sort. Here the above same function but with sorting the data.
  3. Filter data based on criteria.
  4. Basic Aggregation.
  5. Array in Query to Combine Multiple Sheets

The above core QUERY function variations you can learn from the below examples.

Note: Only use Query functions in a new sheet other than your master data. Let us begin.

1. Choose Only Desired Data from a Data Base.

=query(DataList!A1:F16, “select A,B,F”)

This query function selects only column A,B,F from sheet “DataList”. The result will be as follows.

basic google sheet query example

2. Learn Query Function to Select and Sort in Ascending or Descending Order

As you know there are two main aspects in sort – ascending and descending order. Query function can be used to sort your data in ascending and descending order in a new sheet. See the examples below.

Formula in above Example 1.

=query(DataList!A1:F16, “select A,B,F”)

Below is the new formula for sorting in ascending order. It has slight variation from the above Query formula.

=query(DataList!A1:F16, “select A,F,B order by A asc”)

To sort in descending order use the formula as below.

=query(DataList!A1:F16, “select A,F,B order by A desc”)

In the above Query function example, column A sorted in ascending order. You can include additional columns in the sorting by putting a comma like A,B.

3. Filter Data Using Criteria

Here there are two different variations we can use. Numerical and character.

=query(DataList!A1:F16,“select A,B,F where E=7”)

filter data using google sheet query

The above formula will filter data based on Column E value match with numeric value 7. Instead of directly typing the formula you can refer to any cell as criteria. If the criteria is in cell L5, the end part of the formula will be like below.

E=&L5

Now the second variation.

=query(DataList!A1:F16,“select A,B,C,E where C=’M'”)

Text filter in Google Sheet Query

In this case the formula will filter data based on Column C value if match with “M”.

Now see what happens here, similarly in the case of numeric value, when we use the filter criteria from cells outside the formula, here in Cell L2. Last part will look like;

C=‘”&L2&”’”

The single as well as double quotes have great importance in Query Function in Google Sheets.

4. Basic Aggregation.

5. Array in Query to Combine Multiple Sheets

The above four and five require some detailed tutorial and our above sample data is not enough for it. To cut short this Google Sheet tutorial, I’ve posted another tutorial where the 4 and 5 Query function features detailed. You may not find the above headings there. But the features detailed. Here is the link.

Consolidate Data from Multiple Sheets

Additional Information to Learn Query Function with Examples

I will keep updated this area when I post any new article related to Query. Do check often. Else you can go to the Query section in our site.

New! How to Use Date Criteria in Query Function in Google Sheets?

LEAVE A REPLY

Please enter your comment!
Please enter your name here