Bird
Raised Fist0
Google Sheetsspreadsheet~8 mins

QUERY function basics in Google Sheets - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - QUERY function basics
Goal

Find out how to use the QUERY function in Google Sheets to filter and sort data easily.

Sample Data
ProductCategoryPriceSold
AppleFruit1.250
BananaFruit0.880
CarrotVegetable0.540
BroccoliVegetable1.030
OrangeFruit1.160
PotatoVegetable0.3100
Dashboard Components
  • KPI Card: Total Sold
    Formula: =SUM(D2:D7)
    Shows total units sold across all products.
    Result: 360
  • Table: Fruits Only
    Formula: =QUERY(A1:D7, "select A, C, D where B = 'Fruit'", 1)
    Shows only products in the Fruit category with their price and sold units.
    Result rows: Apple, Banana, Orange
  • Table: Products Sold More Than 50
    Formula: =QUERY(A1:D7, "select A, B, D where D > 50 order by D desc", 1)
    Shows products with sold units greater than 50, sorted from highest to lowest.
    Result rows: Potato (100), Banana (80), Orange (60)
Dashboard Layout
+----------------------+--------------------------+
|      Total Sold       |      Fruits Only Table    |
|       (KPI)           |                          |
|        360            |  Product | Price | Sold  |
+----------------------+--------------------------+
| Products Sold > 50 Table                      |
|  Product | Category | Sold                      |
|  Potato  | Vegetable| 100                       |
|  Banana  | Fruit    | 80                        |
|  Orange  | Fruit    | 60                        |
+-------------------------------------------------+
Interactivity

Add a filter dropdown for Category (Fruit, Vegetable). When you select a category, the Fruits Only Table and Products Sold > 50 Table update to show only products from that category. The Total Sold KPI also updates to sum only the sold units for the selected category.

Self Check

If you add a filter to show only Vegetables, which components update and what do they show?

  • Total Sold KPI: Updates to sum sold units of vegetables (Carrot 40 + Broccoli 30 + Potato 100 = 170)
  • Fruits Only Table: Shows no rows because category is Vegetable
  • Products Sold > 50 Table: Shows Potato only (sold 100)
Key Result
A dashboard showing how to use QUERY to filter and sort product sales data by category and sales volume.

Practice

(1/5)
1. What does the QUERY function do in Google Sheets?
easy
A. It deletes rows based on conditions.
B. It creates charts automatically from your data.
C. It lets you ask questions to your data using a simple language.
D. It sorts data alphabetically only.

Solution

  1. Step 1: Understand the purpose of QUERY

    The QUERY function is designed to let you ask questions to your data using a language similar to SQL.

  2. Step 2: Compare options with QUERY's function

    The other options describe other functions or actions, but QUERY specifically helps to filter, select, and group data by asking questions.

  3. Final Answer:

    It lets you ask questions to your data using a simple language. -> Option C

  4. Quick Check:

    QUERY = ask questions to data [OK]
Hint: QUERY lets you ask questions to your data in words [OK]
Common Mistakes:
  • Thinking QUERY creates charts
  • Confusing QUERY with sorting only
  • Assuming QUERY deletes data
2. Which of these is the correct syntax to select column A and B from range A1:C10 using QUERY?
easy
A. =QUERY(A1:C10, "SELECT Col1, Col2")
B. =QUERY(A1:C10, SELECT A, B)
C. =QUERY(A1:C10, 'SELECT A, B')
D. =QUERY(A1:C10, "SELECT 1, 2")

Solution

  1. Step 1: Check the query string format

    The query must be a text string inside double quotes, so "SELECT Col1, Col2" is correct.

  2. Step 2: Validate the column references

    Columns are referenced by ColN (Col1 for column A, Col2 for B), not plain numbers in QUERY, so "SELECT 1, 2" is invalid.

  3. Final Answer:

    =QUERY(A1:C10, "SELECT Col1, Col2") -> Option A

  4. Quick Check:

    Query string in quotes + ColN = correct syntax [OK]
Hint: Always put query inside double quotes with Col1, Col2 [OK]
Common Mistakes:
  • Forgetting quotes around the query
  • Using single quotes instead of double quotes
  • Using numbers instead of ColN
3. Given this data in A1:B4:
A | B
1 | 10
2 | 20
3 | 30
4 | 40

What is the result of =QUERY(A1:B4, "SELECT A WHERE B > 20")?
medium
A. [3, 4]
B. [1, 2]
C. [2, 3, 4]
D. [10, 20, 30, 40]

Solution

  1. Step 1: Understand the query condition

    The query selects column A where column B is greater than 20. Values in B are 10, 20, 30, 40.

  2. Step 2: Filter rows where B > 20

    Only rows with B=30 and B=40 satisfy this, so corresponding A values are 3 and 4.

  3. Final Answer:

    [3, 4] -> Option A

  4. Quick Check:

    Filter B > 20 gives A = 3,4 [OK]
Hint: Filter rows by condition, then pick column values [OK]
Common Mistakes:
  • Including rows where B equals 20
  • Returning values from wrong column
  • Confusing output format
4. What is wrong with this QUERY formula?
=QUERY(A1:C10, "SELECT A WHERE B = 'Apple')"
medium
A. Using single quotes inside double quotes is not allowed.
B. Missing closing double quote for the query string.
C. Column B cannot be used in WHERE clause.
D. The range A1:C10 is too large for QUERY.

Solution

  1. Step 1: Check the quotes in the query string

    The query string starts with a double quote but ends with a single quote before the closing double quote, causing a syntax error.

  2. Step 2: Identify correct quote usage

    Inside the query string, single quotes are allowed around text values, but the entire query must be enclosed properly in double quotes.

  3. Final Answer:

    Missing closing double quote for the query string. -> Option B

  4. Quick Check:

    Query string must start and end with double quotes [OK]
Hint: Check matching quotes around the whole query string [OK]
Common Mistakes:
  • Confusing inner single quotes with outer quotes
  • Thinking column B can't be used in WHERE
  • Assuming range size causes errors
5. You have sales data in A1:C6:
Product | Region | Sales
Apple   | East   | 100
Banana  | West   | 150
Apple   | West   | 200
Banana  | East   | 120
Apple   | East   | 130
Banana  | West   | 170

Which QUERY formula correctly shows total sales per product?
hard
A. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY A")
B. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY B")
C. =QUERY(A1:C6, "SELECT A, SUM(C) WHERE Sales > 100")
D. =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product")

Solution

  1. Step 1: Identify correct column labels and aggregation

    Headers are Product, Region, Sales. To get total sales per product, select Product and SUM(Sales) grouped by Product.

  2. Step 2: Check query syntax and grouping

    =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product") uses correct header labels and groups by product. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY A") uses letters A,C which do not match headers. =QUERY(A1:C6, "SELECT A, SUM(C) WHERE Sales > 100") misses GROUP BY. =QUERY(A1:C6, "SELECT A, SUM(C) GROUP BY B") groups by Region (B).

  3. Final Answer:

    =QUERY(A1:C6, "SELECT Product, SUM(Sales) GROUP BY Product") -> Option D

  4. Quick Check:

    Use header labels + GROUP BY product for totals [OK]
Hint: Use column header names and GROUP BY to sum per group [OK]
Common Mistakes:
  • Using column letters instead of header names
  • Forgetting GROUP BY clause
  • Grouping by wrong column
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard