Using SQL in Google Sheets: Master the QUERY Function

Ever wished you could use SQL's powerful data retrieval capabilities right inside Google Sheets? You're not alone. While spreadsheets are fantastic for organizing data, they can feel limiting when you need to filter, sort, and analyze information the way you would in a database. The good news? Google Sheets has a hidden superpower called the QUERY function that lets you write SQL-like commands directly in your spreadsheet cells.

Whether you're a data analyst tired of creating complex nested formulas, a business user who knows a bit of SQL, or someone who just wants more control over their data, the QUERY function will transform how you work with spreadsheets. In this tutorial, you'll learn how to harness SQL-style querying without ever leaving Google Sheets—no database required.

By the end of this article, you'll be able to filter data with WHERE clauses, sort results with ORDER BY, and even create pivot table-style summaries using GROUP BY. Basic spreadsheet knowledge is all you need to get started, though familiarity with SQL concepts will help you move faster.

Why SQL in Spreadsheets Makes Sense

Google Sheets and databases have more in common than you might think. Both organize data in rows and columns, both let you filter and sort information, and both are designed to help you make sense of large datasets. The main difference? Databases use SQL (Structured Query Language), which has been the gold standard for data retrieval since the 1970s, while spreadsheets traditionally rely on functions and formulas.

SQL is powerful because it's declarative—you tell it what you want, not how to get it. Instead of building complicated nested IF statements and filter combinations, you write straightforward queries like "show me all products where price is greater than $50, sorted by name." This approach is cleaner, more readable, and often faster to write.

Not all data lives in databases, though. Many companies use CSV files, Excel spreadsheets, and Google Sheets to manage everything from inventory lists to sales reports. The QUERY function bridges this gap by bringing SQL's querying power to spreadsheet data. Since Google Sheets is free and can open various file formats including CSV and Excel files, learning to use SQL-style queries here gives you a versatile skill that works across platforms.

Understanding the QUERY Function

The QUERY function is your gateway to SQL-like data manipulation in Google Sheets. The syntax is straightforward:

=QUERY(data, query, [headers])

Let's break down each parameter:

• data: The range of cells containing your data (like A1:F23). This is your "table" in SQL terms.
• query: The SQL-like command you want to execute, written as a text string in quotes.
• headers: Optional. The number of header rows in your data. Google Sheets usually figures this out automatically, so we'll typically skip this parameter.

The query parameter uses Google Visualization API Query Language, which closely mirrors standard SQL syntax. If you know SQL, you're already 80% of the way there. If you don't, don't worry—the examples below will walk you through everything step by step.

One quick note about syntax: depending on your Google Sheets settings, you might need to use semicolons instead of commas to separate parameters. If you get an error, try swapping commas for semicolons.

Sample Data: Hotel Information

For our examples, we'll work with a simple dataset containing information about hotels in Indonesian cities. Here's what our Hotels table looks like:

Our table includes: - Id: Unique identifier for each hotel - Name: Hotel name - Stars: Star rating (3, 4, or 5 stars) - Rating: Guest rating from 0 to 10 - TwinRoomPrice: Base price per night for a twin room - City: Location (Bandung, Denpasar, or Surabaya)

Let's assume this data is in cells A1:F23, with row 1 containing the headers and rows 2-23 containing hotel records.

Example 1: Your First Query - Select Everything

Let's start with the simplest possible query: selecting all data from our table. In SQL, you'd write SELECT * FROM Hotels. In Google Sheets, it looks almost identical.

Click on an empty cell to the right or below your data table and enter:

=QUERY(A1:F23, "SELECT *")

Here's what's happening: - A1:F23 specifies our data range (the entire Hotels table) - "SELECT *" means "select everything" - the asterisk is a wildcard that represents all columns - We omit the FROM clause because we already specified the data range in the first parameter

When you press Enter, Google Sheets creates a complete copy of your original table. This might seem pointless now, but it demonstrates the basic structure we'll build on. Every QUERY function starts with these two core components: the data range and the SELECT statement.

Result: An exact replica of your Hotels table appears, with all 22 hotels and all 6 columns displayed.

Example 2: Selecting Specific Columns with Conditions

Now let's get practical. Suppose you want to see only the name, rating, and price for three-star hotels. This requires selecting specific columns and filtering rows.

=QUERY(A1:F23, "SELECT B, D, E WHERE C = 3")

Here's the breakdown: - SELECT B, D, E - Instead of column names, we use spreadsheet column letters. Column B is Name, D is Rating, and E is TwinRoomPrice - WHERE C = 3 - Column C contains Stars, so this filters to show only 3-star hotels - Notice there's no comma after the last column in SELECT

Important: In Google Sheets QUERY, you reference columns by their spreadsheet letters (A, B, C, etc.), not by their header names. This is different from SQL, where you'd write SELECT Name, Rating, TwinRoomPrice WHERE Stars = 3.

Result: A table showing only three columns (Name, Rating, TwinRoomPrice) for all three-star hotels in your dataset.

SQL Equivalent: SELECT Name, Rating, TwinRoomPrice FROM Hotels WHERE Stars = 3

Example 3: Multiple Conditions and Sorting

Let's level up. Now we want to find all hotels in Bandung with ratings above 7.0, sorted from least to most expensive. This introduces two new concepts: combining conditions with AND, and sorting with ORDER BY.

=QUERY(A1:F23, "SELECT * WHERE D > 7.0 AND F = 'Bandung' ORDER BY E")

Breaking it down: - D > 7.0 - Column D (Rating) must be greater than 7.0 - AND F = 'Bandung' - Combines conditions. Column F (City) must equal 'Bandung' - Notice the single quotes around 'Bandung' - text values must be quoted, but numbers don't need quotes - ORDER BY E - Sorts results by column E (TwinRoomPrice) in ascending order (lowest to highest)

Watch out: Forgetting quotes around text values is a common mistake. If you write F = Bandung instead of F = 'Bandung', you'll get an error.

Result: A filtered table showing only Bandung hotels rated above 7.0, arranged from cheapest to most expensive.

To sort in descending order (highest to lowest price), you'd use ORDER BY E DESC.

SQL Equivalent: SELECT * FROM Hotels WHERE Rating > 7.0 AND City = 'Bandung' ORDER BY TwinRoomPrice

Example 4: Counting Grouped Rows (Pivot Table Alternative)

One of QUERY's most powerful features is creating summaries similar to Excel pivot tables. Let's count how many hotels are in each city.

=QUERY(A1:F23, "SELECT F, COUNT(A) GROUP BY F")

This query introduces aggregation: - SELECT F - Show the City column - COUNT(A) - Count the number of rows in each group. We use column A (Id) because IDs are unique - GROUP BY F - Divide all rows into groups based on City values

How it works: GROUP BY organizes your data into categories (in this case, by city), and COUNT tallies how many hotels fall into each category. Without GROUP BY, COUNT would just count all rows in the dataset.

Result: A two-column table showing each city and the number of hotels located there:

You can use COUNT with any column, but convention suggests using the ID column since it contains unique values. Alternatively, you could write COUNT(B) to count hotel names.

SQL Equivalent: SELECT City, COUNT(Id) FROM Hotels GROUP BY City

Example 5: Calculating Averages by Category

Let's create a more sophisticated summary. We'll calculate the average rating and average price for each star category (3-star, 4-star, 5-star hotels).

=QUERY(A1:F23, "SELECT C, AVG(D), AVG(E) GROUP BY C")

Here's what's new: - GROUP BY C - Groups hotels by Stars column - AVG(D) - Calculates the average Rating for each group - AVG(E) - Calculates the average TwinRoomPrice for each group

Result: A three-column summary table:

This type of analysis replaces what you'd traditionally do with pivot tables, but the QUERY function gives you more control and keeps your analysis visible in the formula itself.

Other aggregate functions you can use include: - SUM() - Total of all values - MIN() - Minimum value - MAX() - Maximum value - COUNT() - Count of rows

SQL Equivalent: SELECT Stars, AVG(Rating), AVG(TwinRoomPrice) FROM Hotels GROUP BY Stars

Advanced Tips and Tricks

Combining Multiple Conditions: You can chain conditions with AND/OR:

=QUERY(A1:F23, "SELECT * WHERE C >= 4 AND D > 8.0 AND E < 200")

Using LIKE for Pattern Matching: Search for text that contains specific words:

=QUERY(A1:F23, "SELECT * WHERE B LIKE '%Resort%'")

This finds all hotels with "Resort" in their name.

Limiting Results: Show only the top 5 results:

=QUERY(A1:F23, "SELECT * ORDER BY D DESC LIMIT 5")

Label Your Columns: Give aggregated columns better names:

=QUERY(A1:F23, "SELECT F, COUNT(A) GROUP BY F LABEL COUNT(A) 'Number of Hotels'")

Handling Errors: If your query returns an error, check: - Are text values wrapped in single quotes? - Are you using column letters (A, B, C) not column names? - Is your data range correct? - Are you using commas or semicolons consistently?

When to Use QUERY vs. Other Functions

The QUERY function shines when you need to: - Filter and sort data simultaneously - Create quick summary reports - Work with multiple conditions - Aggregate data without pivot tables - Make your analysis transparent and reproducible

However, for simple tasks, traditional functions might be easier: - Single filter condition: Use FILTER() - Basic sorting: Use SORT() - Simple lookups: Use VLOOKUP() or XLOOKUP()

Think of QUERY as your go-to tool when you need to combine multiple operations in one formula.

Wrapping Up

You've just learned how to bring SQL's querying power into Google Sheets using the QUERY function. We covered selecting specific columns, filtering with WHERE clauses, sorting with ORDER BY, and creating aggregated summaries with GROUP BY and aggregate functions like COUNT and AVG.

The best way to master these concepts is to practice with your own data. Start with simple SELECT statements, then gradually add WHERE conditions, sorting, and grouping. Before you know it, you'll be writing complex queries that would take dozens of clicks in traditional spreadsheet tools.

The QUERY function is just the beginning—Google's query language includes additional features like PIVOT, OFFSET, and more complex text matching. For complete documentation, check out the Google Visualization API Query Language reference.

Now go forth and query your data like a pro!