How to Use Google Sheets Query

Written by Coursera Staff • Updated on

Learn what the Google Sheets Query function is, how to use it, and why it may benefit your data management.

[Featured Image] A man is showing two woman a spreadsheet he has created to organize and group their inventory using the query function in google sheets.

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.

Placeholder

professional certificate

Preparing for Google Cloud Certification: Cloud DevOps Engineer

Advance your career as an SRE & DevOps Engineer

4.8

(2,586 ratings)

38,645 already enrolled

Beginner level

Average time: 1 month(s)

Learn at your own pace

Skills you'll build:

SRE Culture, Business Value, Organizational Culture, Google Compute Engine, Google App Engine (GAE), Google Cloud Platform, Cloud Computing, Continuous Delivery, Kubernetes, Jenkins (Software), Disaster Recovery, Site Reliability Engineering

What is the Google Sheets 'QUERY' syntax?

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.

'QUERY' functions to know

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.

How to use the Google Sheets 'QUERY' function

In this example, we will be writing a QUERY function to extract the data from columns B and D.

1. Open Google Sheets.

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.

Google Sheet with data organized in rows and columns

2. Select your data.

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.

Query formula including the data text

3. Choose your 'QUERY' function.

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.

Query formula including the data and query function text

4. Input the number of headers.

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. 

 

Query formula including the data, query function, and header text

5. Observe your 'QUERY' function results.

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.

Query results including movie titles and directors from columns B and D

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.

Step summary

When using the QUERY function, you will follow these general steps and alter them based on your end goal:

  1. Open your Google Sheet with organized data.

  2. Select your data.

  3. Enter the QUERY function according to the =QUERY(data, query, [headers]) syntax.

  4. 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.

Query results including movie titles and box office earnings above 10000000000

Common errors

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.

Learn more with Coursera.

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.

Placeholder

professional certificate

Google Data Analytics

Get on the fast track to a career in Data Analytics. In this certificate program, you’ll learn in-demand skills, and get AI training from Google experts. Learn at your own pace, no degree or experience required.

4.8

(151,362 ratings)

2,588,501 already enrolled

Beginner level

Average time: 6 month(s)

Learn at your own pace

Skills you'll build:

Data Analysis, Creating case studies, Data Visualization, Data Cleansing, Developing a portfolio, Data Collection, Spreadsheet, Metadata, SQL, Data Ethics, Data Aggregation, Data Calculations, R Markdown, R Programming, Rstudio, Tableau Software, Presentation, Data Integrity, Sample Size Determination, Decision-Making, Problem Solving, Questioning

Updated on
Written by:
Coursera Staff

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.