QUERY function in Google Sheets is used to query data from a specified dataset. The function allows users to filter and sort data based on specific criteria. The function is commonly used in data analysis and reporting, as well as in creating dynamic dashboards.
- How to use
- Examples of using
QUERYformula not working?
- Similar formulas to
QUERY formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- data (required):
The range of cells or the name of the sheet that contains the data to be queried.
- query (required):
The query to run on the specified data. The query can be in SQL-like language or in the Google Visualization API Query Language format.
- headers (optional):
A number representing the number of header rows to include in the output. If headers are not specified, the function will assume that the data contains headers and will include them in the output.
ExamplesHere are a few example use cases that explain how to use the
QUERY formula in Google Sheets.
Filter data by specific criteria
Users can use the
QUERY function to filter data based on specific criteria, such as a date range or a specific value.
Sort data based on multiple criteria
QUERY function allows users to sort data based on multiple criteria, such as sorting by date and then by value.
Create dynamic dashboards
QUERY function can be used to create dynamic dashboards that update automatically based on user input, such as selecting a specific date range.
QUERY not working? Here are some common mistakes people make when using the
QUERY Google Sheets Formula:
Wrong data range
One common mistake is specifying the wrong data range as the first parameter. Make sure the range includes all the data you want to query.
Incorrect query syntax
Another common mistake is using incorrect syntax in the query parameter. Be sure to use the correct syntax and check for typos.
Missing column headers
If your data does not have column headers, you need to set the headers parameter to false. Otherwise, the first row of data will be used as headers, which may not be what you want.
Conflicting data types
If your data includes conflicting data types, such as text and numbers in the same column, the query may not work as expected. Make sure your data is consistent and formatted correctly.
Unnecessary quotation marks
Sometimes users add unnecessary quotation marks around the query parameter, which can cause errors. Make sure to only use quotation marks when necessary.
The following functions are similar to
QUERY or are often used with it in a formula:
The FILTER formula in Google Sheets is used to filter a range of data based on specified conditions. It returns a new range that only includes the rows that meet the specified conditions. This formula is commonly used to extract specific data from a larger dataset.
SORTformula in Google Sheets is used to sort a range of data based on one or more columns. This function can be used to sort data in ascending or descending order, and can also sort by multiple columns. The sorted data can be used for further analysis or presentation.
VLOOKUPfunction is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.
You can learn more about the
QUERY Google Sheets function on Google Support.