Learn what the Google Sheets Query function is, how to use it, and why it may benefit your data management.
The Google Sheets QUERY function is one of the most useful and diverse functions in Google Sheets. This function allows you to order, extract, group, and manipulate data in your spreadsheet. You can use this tool in ways similar to filters and pivot tables. Experimenting with the QUERY function can help you learn the intricacies of this tool. In this article, we will cover basic QUERY syntax, along with a step-by-step guide to making introductory QUERY functions.
Before you can use the QUERY function effectively, you will need to understand the Query syntax. The QUERY function uses a specific syntax to define your data queries and perform specified operations. This syntax is similar to Structured Query Language (SQL), a standard programming language for database management. When you begin, you can follow this syntax.
=QUERY(data, query, [headers])
data: Specify the range of cells containing your data, including the headers. For example, A1:D100.
query: Construct your query using SQL-like syntax to define the criteria for data retrieval. This includes selecting columns, filtering rows, sorting data, and more.
headers: (optional) Specify the number of header rows in your data range. By default, the function assumes headers are the first row.
You can construct your query by utilizing the SQL-like syntax. Here are some examples to become familiar with as you begin:
Select all columns: To select all columns in your data, use the asterisk (*) symbol. For example, SELECT *.
Specify columns: To select specific columns, list their headers separated by commas. For example, SELECT A, C, E.
Filter rows: Use the WHERE clause to filter rows based on specific conditions. For example, WHERE B > 100.
Sort data: Use the ORDER BY or GROUP BY clause to sort data based on one or more columns.
Aggregate functions: Utilize aggregate functions like SUM, COUNT, and AVERAGE to perform calculations on selected data.
In this example, we will be writing a QUERY function to extract the data from columns B and D.
Start by opening Google Sheets in your web browser. Sign in to your Google account if necessary. Make sure you organize your data in columns and rows, with headers in the first row. This will help you identify and reference your data accurately when using the QUERY function.
In the cell where you want the query results to appear, begin by typing the equal sign (=) followed by the QUERY function. Inside the function, you will provide the necessary arguments: the data range, the query itself, and the number of header rows (if applicable). In this example, we start by selecting all of our data.
Next, you will choose your QUERY function. In this example, we are extracting the data from columns B and D. To do this, we use the Select QUERY function and type Select B, D.
Lastly, you will type in the number of header rows that appear in your data range. In this case, there is one. Type , 1 after your Select QUERY function.
Once you've constructed your query, you'll immediately see the results in the cell where you wrote the QUERY function. The data displayed will match the criteria and format defined in your query.
As you can see in this example, the information from columns B and D was copied into the columns where the QUERY function was written. You will want to make sure you do not have any data in the columns where you are typing your QUERY function and expecting new data to go.
When using the QUERY function, you will follow these general steps and alter them based on your end goal:
Open your Google Sheet with organized data.
Select your data.
Enter the QUERY function according to the =QUERY(data, query, [headers]) syntax.
View your output and adjust as needed.
You can also combine the QUERY functions. For example, a QUERY function for our sample data could read:
=QUERY(B1:G47, "SELECT B, D, G WHERE G > 10000000000", 1)
The output of this function would look like the below image. As you can see, only the movies with box office earnings above $10,000,000,000 are selected.
One of the most common errors is a syntax error in the query itself. You may experience this if you don't use the correct syntax for the query language, such as incorrectly specifying your data range or not using quotation marks around query functions. Forgetting to include the necessary quotation marks or adding extra ones can lead to errors, and you should be careful to match opening and closing quotation marks correctly.
You can build your data organization, management, and analysis skills on Coursera with top-rated courses, Specializations, and Professional Certificates. If you want to build your data analytics skills in an organized way, the Google Data Analytics Professional Certificate is a good place to start. With this Professional Certificate, you can build job-ready skills in under six months, including techniques to utilize Google Sheets, debug code, analyze data, and use Google Cloud.
Editorial Team
Coursera’s editorial team is comprised of highly experienced professional editors, writers, and fact...
This content has been made available for informational purposes only. Learners are advised to conduct additional research to ensure that courses and other credentials pursued meet their personal, professional, and financial goals.
Whether you're starting your career or trying to advance to the next level, experts at Google are here to help.
Save money and learn in-demand skills from top companies and organizations at your own pace.