Google Sheets Query Function

What Is Google Sheets Query Function?

QUERY function in Google sheets is an inbuilt function which is considered as the most powerful formula in the spreadsheet. It is especially used while working with huge datasets. While using QUERY function in Google sheets, we need to remember to use the apt clauses and notes to get the data, we want to find.

For example, consider the below table showing the targets with region, date, location, population, orders, completed projects and rating in columns A,B,C,D,E,F and G columns, respectively.

Now, let us use the QUERY function to receive the columns A, B, and C.

To begin with, insert the cell range in the spreadsheet. In this example, the cell range is A1:G5. Next, select the cell where we want to find the result. In this example, select the cell A9. Next, insert the QUERY formula in Google sheets.

So, the complete formula is =QUERY(A1:G5,”select A,B,C”)

Press Enter key. We can see the result in the cell range A9:C13, as shown in the below image.

In this article, let us learn the use of QUERY function with different clauses and detailed examples.

Query() Google Sheets Formula

The formula or syntax of QUERY Google sheets function is =QUERY(data,query,[headers])

where,

• Data is the mandatory argument showing cell range where we want to find, look or do the process.
• Query is the mandatory argument that explains what we want from the data.
• Headers is the optional argument which shows the number of headers in the data.

QUERY Function Notes/Clauses

While using QUERY function in Google sheets, we need to use certain clauses in the Query argument. The clauses are:

• Select
• Group by
• Limit
• Pivot
• Order by

Let us learn how to use each of the clause with the following examples.

How To Use Google Sheets Query Function?

We can use Google sheets QUERY function to find the desirable result using the below steps.

Step 1: To begin with, insert the cell range in the spreadsheet. Next, select the cell where we want to find the result.

Step 2: Next, insert the QUERY formula in Google sheets.

Step 3: Press Enter key.

We can see the result in the cell range.

Likewise, we can use QUERY Function in Google sheets to find the desired result.

Examples

Consider the below table showing the employee data of an organization, along with the work experience, project details and rating of each employee in columns A,B,C,D,E,F and G columns, respectively.

Now, let us use the QUERY function and learn how to use select, order by, limit, pivot, and group by with the following examples.

Example #1 – Select

The steps to use the ’Select’ clause are:

Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the cell range is A1:G5. Next, select the cell where we want to find the result. In this example, select the cell A10.

Step 2: Next, insert the QUERY formula in Google sheets.

So, the complete formula is =QUERY(A1:G6,”select A,B,C”)

Step 3: Press Enter key.

We can see the result in the cell range A10:C15, as shown in the below image.

Likewise, we can use QUERY Function in Google sheets to find the desired result.

Example #2 – Order By

The steps to use the ‘Order By’ clause are:

Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the cell range is A1:G5. Next, select the cell where we want to find the result. In this example, select the cell A11.

Step 2: Next, insert the QUERY formula in Google sheets.

So, the complete formula is =QUERY(A1:G6,”select A,B,C order by B”)

Step 3: Press Enter key.

We can see the result in the cell range A11:C16, as shown in the below image.

Likewise, we can use QUERY Function in Google sheets to find the desired result.

Example #3 – Limit

The steps to find the clause ‘Limit’ are:

Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the cell range is A1:G5. Next, select the cell where we want to find the result. In this example, select the cell A10.

Step 2: Next, insert the QUERY formula in Google sheets.

So, the complete formula is =QUERY(A1:G6,”select A,B,C,G where G is not null limit 5″)

Step 3: Press Enter key.

We can see the result in the cell range A10:C14, as shown in the below image.

Likewise, we can use QUERY Function in Google sheets to find the desired result.

Example #4 – Pivot

The steps are:

Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the cell range is A1:G5. Next, select the cell where we want to find the result. In this example, select the cell A10.

Step 2: Next, insert the QUERY formula in Google sheets.

So, the complete formula is =QUERY(A1:G6, “select count (G) pivot A”)

Step 3: Press Enter key.

We can see the result in the cell range A10:E11, as shown in the below image.

Likewise, we can use QUERY Function in Google sheets to find the desired result.

Example #5 – Group By

The steps to use the clause ‘group by’ are:

Step 1: To begin with, insert the cell range in the spreadsheet. In this example, the cell range is A1:G5. Next, select the cell where we want to find the result. In this example, select the cell A9.

Step 2: Next, insert the QUERY formula in Google sheets.

So, the complete formula is =QUERY(A1:G6, “select A,min(G) group by A”)

Step 3: Press Enter key.

We can see the result in the cell range A9:B14, as shown in the below image.

Likewise, we can use QUERY Function in Google sheets to find the desired result.

Important Things To Note

• Google sheets Query function is one of the powerful questions which can return data form multiple columns based on the command or clause.
• We need to make sure to enter the arguments with punctuations and spaces correctly to avoid errors.
• The QUERY function is a must know function while working with huge datasets.

Frequently Asked Questions

Recommended Articles

Guide to What is Google Sheets Query Function. Here we discuss the how to use Google Sheets Query Function along with examples. You can learn more from the following articles. –

Tree Map Chart In Google Sheets

SMALL Function In Google Sheets

Extract Number From String Google Sheets