Why QUERY is Google Sheets' most powerful function - Dashboard Impact

Dashboard Mode - Why QUERY is Google Sheets' most powerful function

Goal

Answer the question: How can we use the QUERY function in Google Sheets to quickly analyze and summarize sales data?

Sample Data

Order ID	Product	Region	Sales	Quantity
1001	Notebook	East	120	3
1002	Pen	West	80	5
1003	Notebook	East	150	4
1004	Pen	North	90	6
1005	Notebook	West	200	7
1006	Pen	East	70	2
1007	Notebook	North	130	3

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(D2:D8)
  Shows total sales amount from all orders.
• KPI Card: Total Quantity Sold
  Formula: =SUM(E2:E8)
  Shows total quantity of products sold.
• Summary Table: Sales by Region
  Formula: =QUERY(A1:E8, "select C, sum(D), sum(E) group by C label sum(D) 'Total Sales', sum(E) 'Total Quantity'", 1)
  Shows total sales and quantity grouped by region.
• Filtered Table: Notebook Sales in East Region
  Formula: =QUERY(A1:E8, "select A, B, D, E where B = 'Notebook' and C = 'East'", 1)
  Lists all notebook sales in the East region.

Dashboard Layout

+----------------------+-----------------------+
|   Total Sales (KPI)  | Total Quantity (KPI)  |
+----------------------+-----------------------+
|         Sales by Region Summary Table          |
+-----------------------------------------------+
|      Filtered Notebook Sales in East Region   |
+-----------------------------------------------+

Interactivity

Adding a filter dropdown for Region lets users select a region. When a region is selected:

• The Sales by Region summary updates to show only the selected region's data.
• The Filtered Notebook Sales table updates to show notebook sales only in the selected region.
• The KPI cards update to reflect totals for the selected region.

This shows how QUERY formulas dynamically adjust to filter inputs, making the dashboard interactive and powerful.

Self Check

If you add a filter for Region = West, which components update and what changes?

• Answer: The KPI cards will show total sales and quantity only for West region orders.
• The Sales by Region summary table will show only the West region row.
• The Filtered Notebook Sales table will list only notebook sales from the West region.

Key Result

A dashboard showing total sales, quantity, and filtered summaries using QUERY to analyze sales data by region and product.