## One of the most powerful tools to manipulate data

The QUERY function allows you to retrieve information from a range or entire datasheet using flexible query commands. Learning how to use the Google Sheets QUERY feature gives you access to powerful search tools.

If you’ve ever written SQL queries to retrieve data from a database, you’ll recognize the QUERY function. Even if you have no database experience, the QUERY function is still very easy to learn.

## What is the QUERY function?

This function has three main parameters.

These parameters are very simple.

• data: range of cells containing source data
• inquiry: A search statement that describes how to extract what you want from the source data.
• headline: An optional argument to combine multiple headers from the source sheet into a single header from the destination sheet.

The flexibility and power of the QUERY function comes from the Query argument, as shown below.

## How to create a simple QUERY formula

The QUERY formula is especially useful if you have very large data sets from which you need to extract and filter data.

The following example uses performance statistics from the US SAT High School. In this first example, you will learn how to write a simple QUERY formula that returns all high schools and dates with “New York” in their school name.

Create a new sheet on which to place the query results. top left cell type =query(. This will display a pop-up window with required arguments, examples, and useful information about the function. Next, assuming the source data is on Sheet1, enter the function as follows:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE B LIKE ‘%New York%'”).

This formula contains the following arguments:

• area of ​​the cell: Data range from A1 to F460 on Sheet1
• SELECT statement: A SELECT statement that retrieves all data from columns B, C, D, E, and F. Here, column B contains text containing the word “New York”. The “%” character is a wildcard that can be used to search for part of a string or number in any record. Omitting “%” at the beginning of the string returns the school name starting with the text “New York”.

To find the exact school name in the list, you can enter the following query:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE B = ‘New York Harbor High School’”).

use = The – operator can be used to find an exact match and find text or numbers that match in any column. The Google Sheets QUERY function is very easy to understand and use, so you can use a simple query statement like the one above to get any data from a large dataset.

## Using the QUERY function with comparison operators

Comparison operators allow you to use the QUERY function to filter out data that does not meet a condition.

All of the following operators are accessible from the QUERY function:

• =: The value is the same as the search value
• <: value is less than search value
• >: The value is greater than the search value.
• <=: Value is less than or equal to the search value
• >=: Value is greater than or equal to the search value
• <> and !=: The search value and the source value are not the same.

Using the same sample SAT data set up above, consider schools with an average math score of 500 or higher.

In the top left cell of a blank sheet, fill in the QUERY function as follows:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE E > 500″)

This formula requests all data in column E containing a value greater than 500. You can also search for multiple conditions using logical operators such as AND and OR. For example, to retrieve only results for schools with more than 600 test takers and a critical reading average of between 400 and 600, enter the following QUERY function:

=Query(Sheet1!A1:F460, “SELECT B,C,D,E,F WHERE C > 600 AND D > 400 AND D < 600") Comparison and logical operators provide a variety of ways to retrieve data from a source table. Even very large data sets can filter out important information.

## Advanced use of the QUERY function

There are several other functions that can be added to the QUERY function with a few additional commands. You can use these commands to aggregate values, calculate values, order data, and find maximums.

You can use GROUP in the QUERY function to aggregate values ​​across multiple rows. For example, you can use the GROUPS function to average each student’s test scores. To do this, type:

=Query(Sheet1!A1:B24,”SELECT A, GROUP AVG(B) BY A”) When using COUNT in the QUERY function, you can use the following QUERY function to count the number of schools with a WPA greater than 500.

=QUERY(SHEET1!A2:F460,”Select B, COUNT(F) GROUP by B”) You can use ORDER BY in the QUERY function to find the school with the highest average math score and sort the list by that score.

=QUERY(Sheet1!A2:F460,”B, MAX(E) GROUP BY B ORDER BY MAX(E)”) How to Use Google Sheets QUERY Function

One of the most powerful tools to manipulate your data

The QUERY function lets you pull information from a range or entire sheet of data using flexible query commands. Learning how to use the Google Sheets QUERY function gives you access to a powerful lookup tool.

If you’ve ever written SQL queries to get data out of a database, then you’ll recognize the QUERY function. If you don’t have database experience, the QUERY function is still very easy to learn.

What Is the QUERY Function?

The function has three main parameters:

These parameters are fairly straightforward.

Data: The range of cells that contain the source data
Query: A search statement describing how to extract what you want from the source data
Headers: An optional argument that lets you combine multiple headers in the source range into a single header in the destination sheet

The flexibility and power of the QUERY function comes from the Query argument, as you’ll see below.

How to Create a Simple QUERY Formula

The QUERY formula is especially useful when you have a very large data set from which you need to extract and filter data.

The following examples use U.S. SAT high school performance statistics. In this first example, you’ll learn how to write a simple QUERY formula that returns all high schools and their data where “New York” is in the name of the school.

Create a new sheet for placing the query results. In the upper left cell type =Query(. When you do this, you’ll see a pop-up window with require arguments, an example, and helpful information about the function.

Next, assuming you have the source data in Sheet1, fill in the function as follows:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE B LIKE ‘%New York%’”).

This formula includes the following arguments:

Range of Cells: The range of data in A1 to F460 in Sheet1
SELECT Statement: A SELECT statement that calls for any data in columns B, C, D, E, and F where column B contains text that has the word “New York” in it.

The “%” character is a wildcard that you can use to search for parts of strings or numbers in any data set. Leaving “%” off the front of the string would return any school name that starts with the text “New York”.

If you wanted to find the name of an exact school from the list, you could type the query:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE B = ‘New York Harbor High School’”).

Using the = operator finds an exact match and can be used to find matching text or numbers in any column.

Because the the Google Sheets QUERY function is very easy to understand and use, you can pull any data out of any large data set using simple query statements like the ones above.

Use the QUERY Function With a Comparison Operator

Comparison operators let you use the QUERY function to filter out data that doesn’t meet a condition.

You have access to all of the following operators in a QUERY function:

=: Values match the search value
<: Values are less than the search value
>: Values are greater than the search value
<=: Values are less than or equal to the search value
>=: Values are greater than or equal to the search value
<> and !=: Search value and source values are not equal

Using the same SAT example data set above, let’s take a look at how to see which schools had an average mathematics mean above 500 points.

In the upper left cell of a blank sheet, fill in the QUERY function as follows:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE E > 500″)

This formula calls for any data where column E contains a value that’s greater than 500.

You can also include logical operators like AND and OR to search for multiple conditions. For example, to pull scores only for schools with over 600 test takers and a critical reading mean between 400 and 600, you would type the following QUERY function:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE C > 600 AND D > 400 AND D < 600″)

Comparison and logical operators provide you with many different ways to pull data from a source spreadsheet. They let you filter out important pieces of information from even very large data sets.

There are a few other features you can add to the QUERY function with some additional commands. These commands let you aggregate values, count values, order data, and find maximum values.

Using GROUP in a QUERY function allows you to aggregate values in multiple rows. For example, you can average test grades for each student using the GROUP function. To do this, type:

=Query(Sheet1!A1:B24,”SELECT A, AVG(B) GROUP BY A”)

Using COUNT in a QUERY function, you could count the number of schools with a writing mean score over 500 using the following QUERY function:

=QUERY(Sheet1!A2:F460,”SELECT B, COUNT (F) GROUP BY B”)

Using ORDER BY in a QUERY function, you can find schools with maximum math mean scores and orders the list by those scores.

=QUERY(Sheet1!A2:F460,”SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)”)

How to Use Google Sheets QUERY Function

One of the most powerful tools to manipulate your data

The QUERY function lets you pull information from a range or entire sheet of data using flexible query commands. Learning how to use the Google Sheets QUERY function gives you access to a powerful lookup tool.

If you’ve ever written SQL queries to get data out of a database, then you’ll recognize the QUERY function. If you don’t have database experience, the QUERY function is still very easy to learn.

What Is the QUERY Function?

The function has three main parameters:

These parameters are fairly straightforward.

Data: The range of cells that contain the source data
Query: A search statement describing how to extract what you want from the source data
Headers: An optional argument that lets you combine multiple headers in the source range into a single header in the destination sheet

The flexibility and power of the QUERY function comes from the Query argument, as you’ll see below.

How to Create a Simple QUERY Formula

The QUERY formula is especially useful when you have a very large data set from which you need to extract and filter data.

The following examples use U.S. SAT high school performance statistics. In this first example, you’ll learn how to write a simple QUERY formula that returns all high schools and their data where “New York” is in the name of the school.

Create a new sheet for placing the query results. In the upper left cell type =Query(. When you do this, you’ll see a pop-up window with require arguments, an example, and helpful information about the function.

Next, assuming you have the source data in Sheet1, fill in the function as follows:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE B LIKE ‘%New York%’”).

This formula includes the following arguments:

Range of Cells: The range of data in A1 to F460 in Sheet1
SELECT Statement: A SELECT statement that calls for any data in columns B, C, D, E, and F where column B contains text that has the word “New York” in it.

The “%” character is a wildcard that you can use to search for parts of strings or numbers in any data set. Leaving “%” off the front of the string would return any school name that starts with the text “New York”.

If you wanted to find the name of an exact school from the list, you could type the query:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE B = ‘New York Harbor High School’”).

Using the = operator finds an exact match and can be used to find matching text or numbers in any column.

Because the the Google Sheets QUERY function is very easy to understand and use, you can pull any data out of any large data set using simple query statements like the ones above.

Use the QUERY Function With a Comparison Operator

Comparison operators let you use the QUERY function to filter out data that doesn’t meet a condition.

You have access to all of the following operators in a QUERY function:

=: Values match the search value
<: Values are less than the search value
>: Values are greater than the search value
<=: Values are less than or equal to the search value
>=: Values are greater than or equal to the search value
<> and !=: Search value and source values are not equal

Using the same SAT example data set above, let’s take a look at how to see which schools had an average mathematics mean above 500 points.

In the upper left cell of a blank sheet, fill in the QUERY function as follows:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE E > 500″)

This formula calls for any data where column E contains a value that’s greater than 500.

You can also include logical operators like AND and OR to search for multiple conditions. For example, to pull scores only for schools with over 600 test takers and a critical reading mean between 400 and 600, you would type the following QUERY function:

=Query(Sheet1!A1:F460,”SELECT B,C,D,E,F WHERE C > 600 AND D > 400 AND D < 600″)

Comparison and logical operators provide you with many different ways to pull data from a source spreadsheet. They let you filter out important pieces of information from even very large data sets.

There are a few other features you can add to the QUERY function with some additional commands. These commands let you aggregate values, count values, order data, and find maximum values.

Using GROUP in a QUERY function allows you to aggregate values in multiple rows. For example, you can average test grades for each student using the GROUP function. To do this, type:

=Query(Sheet1!A1:B24,”SELECT A, AVG(B) GROUP BY A”)

Using COUNT in a QUERY function, you could count the number of schools with a writing mean score over 500 using the following QUERY function:

=QUERY(Sheet1!A2:F460,”SELECT B, COUNT (F) GROUP BY B”)

Using ORDER BY in a QUERY function, you can find schools with maximum math mean scores and orders the list by those scores.

=QUERY(Sheet1!A2:F460,”SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)”)