How to Use the Google Sheets QUERY Function to Extract Data

If you have a massive spreadsheet containing thousands of rows of sales data, extracting a specific subset of that data—such as “only show sales from London that exceed £500″—can be difficult. Using standard Google Sheets filters forces you to manually click through menus, and it alters the view of your original raw data.

A more powerful, automated approach is to use the QUERY function. This unique Google Sheets feature allows you to use a simplified version of SQL (Structured Query Language), the same code used by professional database administrators, to instantly extract, filter, and sort specific data into a completely new location without touching the original dataset.

Understanding the Syntax

The QUERY function requires two primary arguments to work: the data you want to search, and the instructions (the “query string”) telling it what to find.

The basic syntax looks like this:

=QUERY(data_range, "query_string")

• data_range: The cells containing your raw data (e.g., A1:F1000).
• query_string: The SQL command wrapped in double quotes (e.g., "SELECT A, B WHERE C > 100").

How to Write a Basic Query

Imagine you have a Master Dataset on “Sheet1”. Column A is the salesperson’s name, Column B is the city, and Column C is the total sales amount.

You want to create a new, separate report on “Sheet2” that only shows the names and sales amounts for the London office.

1. Open a blank tab in your spreadsheet (e.g., “Sheet2”).
2. Click into cell A1.
3. Type the start of the function, pointing it to your raw data:

   =QUERY(Sheet1!A1:C1000, 

4. Next, write the SQL command. You want to extract Column A and Column C, but only if Column B equals “London”. Type:

   "SELECT A, C WHERE B = 'London'")

5. Press Enter.

The QUERY function will instantly populate your new sheet with the exact data requested. If someone updates the Master Dataset on Sheet1, your queried report on Sheet2 will update automatically in real-time.

Note: Text strings inside a query (like ‘London’) must be wrapped in single quotation marks.

Advanced Query Commands

The true power of the QUERY function lies in its flexibility. You can combine multiple commands to perform complex data analysis instantly.

• ORDER BY: Sort your extracted data automatically.

  "SELECT A, C WHERE B = 'London' ORDER BY C DESC"

  (This extracts the London data and automatically sorts it so the highest sales numbers appear at the top).
• LIMIT: Restrict the number of results returned.

  "SELECT A, C WHERE B = 'London' ORDER BY C DESC LIMIT 5"

  (This extracts only the top 5 highest-performing salespeople in London).
• AND / OR: Combine multiple filtering conditions.

  "SELECT A, C WHERE B = 'London' AND C > 500"

  (This extracts the data only if the city is London AND the sales are greater than 500).