Blog

Google Sheets Query: The Ultimate Beginner's Guide

March 16, 2020

What problem this tutorial solves

If you have a lot of data sets you want to analyze, aggregate, filter, or transform with calculations, you might find that pivot tables, simple functions like SUM, and the filtering feature might not be enough to get all this done neatly. Your tables can get messy with so many layers of filters and functions all over the place.

 

Wouldn’t it be nice to manipulate your data with just one easy to learn function? Let’s dive into Google Sheets query so that you begin using it right away.

 

What you’ll learn

Luckily, Google Sheets query function is a simple and powerful tool (even if you don’t know SQL). It’s intuitive to learn as it uses English words like “SELECT”, “WHERE”, “ORDER BY”, “LIMIT” and others.

 

I’ll walk you through the layout/set up of the query function and SQL keywords to know. Then I’ll show you some beginner examples and then some more advanced functions.

 

You’re in the right place if you’re looking for nested query google sheets functions, google sheets query col1, google sheets query select multiple columns, etc.

 

Resources I built to help you use Google Sheets query

  1. You can see all the formulas I’ve used in action on this Google Sheet. The function is written above the table so you can see it clearly but it is active in each of the purple boxes.
  2. You can also watch the 8-minute video tutorial here. It is also embedded at the bottom of this article.



Google Sheets QUERY Syntax

QUERY(data, query, [headers])

data - The range of cells to perform the query on.

  • Each column of data can only hold boolean, numeric (including date/time types) or string values.
  • In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

query - The query to perform, written in the Google Visualization API Query Language.

headers - [ OPTIONAL ] - The number of header rows at the top of data. If omitted or set to -1, the value is guessed based on the content of data.

How to use Google Sheets Query Function First Argument: DATA

query 1

 

 

The Query Function is set up in two main parts.

 

The first being, DATA, meaning which data set are you querying. You can choose between:

  1. Highlighting the cells you are interested in, or
  2. Naming the data set and type in the name of your set. You can do this by (see screenshot below):
  1. Selecting the cells containing your data
  2. Clicking “Data” in the menu above
  3. Clicking “Named Range”
  4. Naming your range

 

Query 2

 

How to use Google Sheets Query Function Second Argument: QUERY

The second part of the query function is called QUERY. This basically translates to “what are you trying to query in the range of cells you just selected?” Using keywords in a structured phrase like “SELECT X Y Z WHERE xxx = xxx”, you can tell the query function what data you want to be retrieved. By the way, this section is always wrapped in quotation marks.

 

Query Function Clauses

The syntax of the query language is composed of the following clauses. Each clause starts with one or two keywords. All clauses are optional. Clauses are separated by spaces. The order of the clauses must be as follows:

Clause Usage

SELECT Selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order.
WHERE Returns only rows that match a condition. If omitted, all rows are returned.
GROUP BY Aggregates values across rows.
PIVOT Transforms distinct values in columns into new columns.
ORDER BY Sorts rows by values in columns.
LIMIT Limits the number of returned rows.
OFFSET Skips a given number of first rows.
LABEL Sets column labels.
FORMAT Formats the values in certain columns using given formatting patterns.
OPTIONS Sets additional options.

In this article, we'll cover the first 4 keywords you should know:

  • SELECT
  • WHERE
  • ORDER BY
  • LIMIT

 

How to use Google Sheets Query SELECT

 Every basic query starts off with SELECT. It returns rows that match the specified condition using the SELECT clause.

How to use Google Sheets Query Select All Columns

If you want to select all the data in the data set (meaning the table retrieved will have all the columns) then put an * after SELECT:

=QUERY(A1:G15, “SELECT *”)

 

How to use Google Sheets Query Select Multiple Columns

But if you only want to retrieve data from certain columns within the dataset, put the column letter after SELECT (see screenshot below):

=QUERY(A1:G15, “SELECT B, C, G”)

 

query 3

 

Once you’ve chosen which columns you are interested in, using SELECT, it’s now time to filter what type of data you want from those columns.

 

The example data set above shows Company ID, Name, Founded Year, and Stripe Revenue. Maybe you only want to retrieve data from companies based in the United States. In the new table you will create in your query, you are only interested in having 3 columns: Name, Founded Year and Stripe Revenue.

 

You could write:

=QUERY(Range1,"SELECT B,D,G WHERE C='United States'")

☝️Note that you don’t need to include the column you are filtering by in your selected columns.

☝️Remember to put single quotes around text: ‘United States’.

 

As a result, Columns B (Name), D (Founded Year) and G (Stripe Revenue) would be returned, but only the United States based companies would be included (see screenshot below).

Query 4

 

 

How to use Google Sheets Query WHERE

 It returns rows that match the specified condition using the WHERE clause. You might need multiple WHERE clauses.

Let’s say your data sheet has some missing information and looks something like this:

query 5

 

 

You only want to retrieve data where there are filled cells but also want to retrieve data from only US companies. You would separate the two WHERE filters by just writing AND between the two (see screenshot below): 

=QUERY(Range1, "SELECT B, D, G WHERE A is not null and C = ‘United States’”)

 

query 6

 

How to use Google Sheets Query ORDER BY (to Sort Data in Ascending or Descending Order)

 

Now the next step would be if you wanted to sort these companies. Maybe you’re interested in the company with the most monthly revenue. You would add the phrase “ORDER BY G DESC” at the end of the query.

DESC is short for ‘descending’ and would mean the lowest number to the highest number.

ASC is short for ‘ascending’ and would mean highest to lowest (see screenshot below):

=QUERY(Range1, "SELECT B, D, G WHERE A is not null and C = ‘United States’ ORDER BY G DESC”)

 

query 7

 

 

How to use Google Sheets Query LIMIT

You next might only want to limit yourself to seeing the top 5 US companies with the highest monthly revenue. You would just add LIMIT 5 to the end of the query (see screenshot below):

=QUERY(Range1, “SELECT B, D, G WHERE A is not null and C = ‘United States’ ORDER BY G DESC LIMIT 5”)

 

 

query 8

 

 

It’s important to remember to structure your query criteria in this order: SELECT > WHERE > ORDER BY > LIMIT.

 

 

How to Query Multiple Sheets / Ranges / Tabs At The Same Time

 

If you want to query multiple sheets / ranges / tabs within the same spreadsheet, you can do that using the query function.

 

⚠️You’ll need to make sure the data is consistent though ; the data type needs to correspond throughout each sheet. For example, Col1 across all the sheets should all be company names, Col2 as Company ID, Col3 as Revenue etc. As soon as you are using more than one table in a query, column letterheads (A, B, C, D, etc.) become irrelevant in the query criteria (But they are still relevant when you indicate range). You now need to refer to the columns as Col1 for the first column in the selected range and Col2 for the second and so forth.

 

To select multiple tabs / sheets, refer to the tabs by:

  • Their name
  • Then follow the sheet name with !
  • And then the range of cells on that sheet
  • Then add a semicolon to stack the data on top of each other
  • And then Sheet name followed by !
  • And the range

 

Here’s an example:

=QUERY({Sheet1!A:D;Sheet2!B:E} “SELECT *”)

 

☝️The most important thing to remember is putting the curly brackets around the totality of your selected data range whenever you are querying data from different sheets or different spreadsheets.

 

Now continue as you normally would but refer to columns as Col1, Col2, etc.

Example of How to Query Multiple Sheets in a Same Spreadsheet

 

Let’s say I want to aggregate data from two sheets (Growth1 and Growth2) but exclude growth rates that are less than 0.

 

Here is Growth 1:

advanced query 1

 

 

And here is Growth 2:

advanced query 2

 

 

 

You can see that they each have four columns in the order:

  1. Name
  2. # of transactions this week
  3. # of transactions last week
  4. Growth Rate

 

As you remember, you need to make sure the data is consistent, with the data type needing to correspond throughout each sheet. So here:

  • Name: Column A from Growth1 lines up with Column B from Growth2
  • # of transactions this week: Column B from Growth1 lines up with Column C from Growth2
  • # of transactions last week: Column C from Growth1 lines up with Column D from Growth2
  • Growth Rate: Column D from Growth1 lines up with Column E from Growth2

 

Now I’ll use the function (see screenshot below):

=QUERY({Growth1!A:D; Growth2!B:E} “Select * WHERE Col 4 >0”)

 

advanced Query 3

 

Here is the result:

advanced query 4

 

Example of How to Query Multiple Sheets in Different Spreadsheets

 

You can also query from a completely different spreadsheet. 

 

Let’s say Growth2 wasn’t on this spreadsheet and it was on a whole other spreadsheet and we still wanted to aggregate it. We would take the same function: 

=QUERY({Growth1!A:D; Growth2!B:E} “Select * WHERE Col 4 >0”) 

 

But we would use the “import range” method to add: 

=QUERY({Growth1!A:D; ImportGrowth2!B:E} “Select * WHERE Col 4 >0”) 

 

Keep Your Data Live on Google Sheets

The query function is great because as you change the data on your main data set, all other tables that are made from querying that table will update too.

 

Want to know more about keeping your data live on Google Sheets no matter where you export it from? Check out this article on importing data to Google Sheets and keeping it live here.

 

 

Oh, and if you think this whole thing is a painful process, feel free to check out our product Actiondesk, it's a spreadsheet software that lets you import and refresh data seamlessly from SQL, Stripe, Salesforce, Hubspot and more.

👇 Request for early access to Actiondesk 👇