Why QUERY is Google Sheets' most powerful function - Business Case Study

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants a clear summary of sales data filtered by region and product category, showing total sales and average price.

📊 Data: You have a sales dataset with columns: Date, Region, Product Category, Product Name, Units Sold, Unit Price, and Total Sales.

🎯 Deliverable: Create a summary table using the QUERY function that shows Region, Product Category, total Units Sold, average Unit Price, and total Sales, filtered to only include regions with sales above 1000 units.

Sample Data

Date	Region	Product Category	Product Name	Units Sold	Unit Price	Total Sales
2024-01-05	East	Electronics	Headphones	150	50	7500
2024-01-07	West	Electronics	Speakers	200	80	16000
2024-01-10	East	Furniture	Desk	100	120	12000
2024-01-12	North	Electronics	Monitor	300	150	45000
2024-01-15	South	Furniture	Chair	80	75	6000
2024-01-18	West	Furniture	Table	120	200	24000
2024-01-20	North	Furniture	Shelf	90	100	9000
2024-01-22	East	Electronics	Camera	130	300	39000
2024-01-25	South	Electronics	Smartphone	250	400	100000
2024-01-28	West	Electronics	Tablet	180	350	63000

1

Step 1: Select the entire data range including headers (A1:G11).

No formula needed.

Expected Result

Data range selected for QUERY function.

2

Step 2: Enter the QUERY formula to summarize total Units Sold, average Unit Price, and total Sales by Region and Product Category.

=QUERY(A1:G11, "select B, C, sum(E), avg(F), sum(G) group by B, C", 1)

Expected Result

Table showing Region, Product Category, sum of Units Sold, average Unit Price, and sum of Total Sales.

3

Step 3: Modify the QUERY formula to include only regions where total Units Sold is greater than 1000.

=QUERY(A1:G11, "select B, C, sum(E), avg(F), sum(G) group by B, C having sum(E) > 1000", 1)

Expected Result

Filtered summary table excluding regions with total Units Sold 1000 or less.

4

Step 4: Format the summary table headers for clarity: Region, Product Category, Total Units Sold, Average Unit Price, Total Sales.

Manually rename headers in the output or use QUERY label clause.

Expected Result

Clear headers on the summary table for easy understanding.

5

Step 5: Use the QUERY label clause to rename columns in the formula for better readability.

=QUERY(A1:G11, "select B, C, sum(E), avg(F), sum(G) group by B, C having sum(E) > 1000 label sum(E) 'Total Units Sold', avg(F) 'Average Unit Price', sum(G) 'Total Sales'", 1)

Expected Result

Summary table with friendly column names.

Final Result

Region    | Product Category | Total Units Sold | Average Unit Price | Total Sales
----------|------------------|------------------|--------------------|------------
East      | Electronics      | 280              | 175                | 46500
West      | Electronics      | 580              | 170                | 79000
West      | Furniture        | 120              | 200                | 24000
North     | Electronics      | 300              | 150                | 45000
South     | Electronics      | 250              | 400                | 100000

✓East and West regions have strong sales in Electronics and Furniture.

✓South region has high sales in Electronics but lower in Furniture.

✓North region has balanced sales but lower total units compared to West.

✓Regions with total units sold above 1000 are included, filtering out smaller sales.

Bonus Challenge

Create a dynamic QUERY formula that updates the summary automatically when new data rows are added below the current data.

Show Hint

Use an open-ended range like A1:G or use named ranges to include new data automatically.