How to use the query function in Google Sheets

A query function allows you to use data commands to model your data in Google Sheets, making it an important feature.

The query function is very useful because it can replace the work of many other commands and replicate the functionality of pivot tables (a table that allows you to group, compare, and summarize larger datasets). A query might seem complicated or overwhelming if you are not familiar with the “Google Sheets” functions. However, that is not the case, and you can start using the query function right away to quickly filter and search data in your preferred format.

What is a query in Google Sheets?

The word query comes from Structured Query Language or SQL, a domain-specific language used in programming to simplify the management of large or small datasets. A query in Google Sheets allows you to manipulate and analyze data with a single query formula. With the query function, you can specify complex conditions to filter, sort, and group data. You can also use various built-in functions to calculate and transform your data. The output of the query is a new table that includes only the rows and columns that meet the specified conditions.

What syntax do I need to understand to use the query function?

Here is a basic syntax of a query function in Google Sheets:

=QUERY(data, query, [headers])

• “data” refers to the range of cells (called a range) that contain the data you want to work with.
• “query” is the actual query you want to run on the data.
• “[headers]” is an optional parameter that specifies whether the first row of the data range contains column headers. If set to 1, the first row is treated as headers. If set to 0 or omitted, the first row is treated as data. I recommend creating headers in the first row and setting it to 1 for an easier experience.

Queries are particularly useful when you need to extract a subset of data from a large dataset and when you want to perform complex calculations or transformations on the data.

How to use queries in Google Sheets in three simple steps

Create a numbered list of steps on how people can do this. Include screenshots.

1. Name your data.

First, you’ll want to create your first named range, so you don’t have to copy cells every time you want to use them. Using Command for Mac or CTRL for Windows, highlight every cell with data. Click “Data” and then click “Named ranges” (in the photo below).

You will then name the entire dataset, so since this spreadsheet focuses on students’ names, gender, and extracurricular activities, I called it “class.” Be sure not to use spaces when naming.

Then, you will enter a blank cell to test the named range. Type “=query(INSERT YOUR DATA NAME)” and see if it highlights all the cells for you (like the image below).

If you close the parentheses and hit enter, an identical copy of your data should be generated to the right.

2. Specify what data you want.

You can now specify which headers you want your query to address. Use the following formula to specify which columns you want displayed. I chose columns A and D on my chart so that I could see the students’ names and their home states.

=QUERY(class,  "select A,D", 1)

After filling out this formula, go ahead and hit enter. If you run into any issues, make sure you have it written exactly and aren’t missing any commas or parentheses. You can add any columns you want to the equation by adding a comma and then the letter of the column. So, if you wanted the student’s name, gender, and home state, you would write:
=query(class, “select A, B, D”, 1).

3. Get more detailed data.

Let’s say another column includes the student’s GPAs (see below), and we want to know which students have a GPA greater than 3.0.

You can use the following formula to sort GPAs and generate a report that includes the names, gender, and GPAs of students who are above 3.0.

=QUERY(class,  "select A, B, G where G > 3", 1)

Let’s say you want to know how many students choose art as an extracurricular activity. You could use this formula to generate that data:

=QUERY(class,  "select A, E  where E = 'Art' ", 1)

A report (like the one below) would then be generated, so you can know that there are seven students in art.

If you want to know which female students have the highest GPA, you can use the following formula:

=QUERY(class,  “select A, B, G where B = ‘Female’ order by G asc”, 1)

And the following report will be generated.

Query Examples in Google Sheets

1. Save time

Pretend that you have a gigantic Google Sheet with the name, gender, role, and age of everyone who works at a company. Your boss asks you to give them the name of every person who identifies as a woman at your company so that they can be invited to a special women-led conference.

You could spend all afternoon copying the names and roles of these women, or you could save a lot of time by using a query that generates a report in less than a second, which would look something like this:

=query(staff, “seleziona A, B dove B = 'Femmina'”, 1)

Here, column “A” is their name, and column “B” is their gender.

2. Get more accurate results

Human error is inevitable, especially when you stare at the same screen for hours trying to filter data. Anyone who deals with large datasets can attest to how difficult it is to make copy-and-paste errors. Using queries in Google Sheets is an exact formula; if you input the precise data that you want, you will generate an accurate report.

3. Reuse the same formulas

Once you have a formula that you know you’ll use often, you don’t have to type it out over and over. You can simply click in your report to edit the formula or you can save a template of that formula in a separate document.

For example, if you work in sales and every quarter you are asked to present quarterly earnings from highest to lowest performance, you can ask them to add their revenue into a Google Sheet.

Then, when it comes time for the presentation, you only need to generate a report using a formula such as this:

=query(venditori, “seleziona A, C ordine per C asc”, 1)

Then “A” would be the salespersons’ names, “C” would represent just the revenue for a specific quarter, and “ascending” would go from highest to lowest, allowing you to identify top performers.

Queries allow you to analyze and visualize your data in various ways, making it easier to process insights and make informed decisions. Have fun experimenting with different query functions to see what you can do with your data! 

Pubblicato in Google