QUERY function basics in Google Sheets - Dashboard Guide

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

Product	Category	Price	Sold
Apple	Fruit	1.2	50
Banana	Fruit	0.8	80
Carrot	Vegetable	0.5	40
Broccoli	Vegetable	1.0	30
Orange	Fruit	1.1	60
Potato	Vegetable	0.3	100

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.