Multi-Branch Sales Dashboard
Beginner
55 min
83 views
0 solutions
Overview
A retail company has 3 branch teams each maintaining their own Sales Sheet. Puneet Arora demonstrates live how to build a central HQ dashboard that pulls live data using IMPORTRANGE, displays product images via IMPORTIMAGE, generates business summaries using QUERY with GROUP BY, automates column calculations with ARRAYFORMULA, and recovers deleted data via Revision History.
Case Details
CONTEXT: RetailCo HQ receives sales data from 3 branches (North, South, East). Each branch maintains its own Google Sheet. HQ analyst must build a master dashboard without copy-pasting — all data must stay live.
INSTRUCTOR SETUP (before class): Pre-create 3 branch sheets with 20 rows each. Columns: Product ID, Product Name, Category, Qty Sold, Unit Price, Branch, Region. Share all 3 sheets publicly. These will be the IMPORTRANGE sources.
DEMO — STEP 1 (IMPORTRANGE): In the Dashboard tab, pull Branch 1 data into A2 using =IMPORTRANGE(url, "Sheet1!A1:G20"). Show the authorization popup. Demonstrate #REF! error if not authorized. Pull Branches 2 and 3 below.
DEMO — STEP 2 (IMPORTIMAGE): In a 'Product Images' section, use =IMPORTIMAGE(url) with 3 real product image URLs. Resize cells to display images properly. Show the difference between the raw URL and the rendered image.
DEMO — STEP 3 (QUERY): Write =QUERY(A2:G62, "SELECT F, SUM(D) GROUP BY F ORDER BY SUM(D) DESC", 1) to rank branches by units sold. Ask students: 'What does GROUP BY do? How is this like Excel pivot tables?'
STUDENT TASK (ARRAYFORMULA): Students write a single ARRAYFORMULA in column H to calculate Total Value = Qty × Unit Price for all rows. =ARRAYFORMULA(D2:D*E2:E). Instructor checks results.
DEMO — STEP 5 (REVISION HISTORY): Instructor intentionally deletes a column of data. Students watch. Then instructor uses File > Version History > See version history to restore the deleted column. Key lesson: nothing is permanently lost in Google Sheets.
CLASS DEBRIEF: Compare IMPORTRANGE with copy-paste. Why is live data better? When would you still use paste?
INSTRUCTOR SETUP (before class): Pre-create 3 branch sheets with 20 rows each. Columns: Product ID, Product Name, Category, Qty Sold, Unit Price, Branch, Region. Share all 3 sheets publicly. These will be the IMPORTRANGE sources.
DEMO — STEP 1 (IMPORTRANGE): In the Dashboard tab, pull Branch 1 data into A2 using =IMPORTRANGE(url, "Sheet1!A1:G20"). Show the authorization popup. Demonstrate #REF! error if not authorized. Pull Branches 2 and 3 below.
DEMO — STEP 2 (IMPORTIMAGE): In a 'Product Images' section, use =IMPORTIMAGE(url) with 3 real product image URLs. Resize cells to display images properly. Show the difference between the raw URL and the rendered image.
DEMO — STEP 3 (QUERY): Write =QUERY(A2:G62, "SELECT F, SUM(D) GROUP BY F ORDER BY SUM(D) DESC", 1) to rank branches by units sold. Ask students: 'What does GROUP BY do? How is this like Excel pivot tables?'
STUDENT TASK (ARRAYFORMULA): Students write a single ARRAYFORMULA in column H to calculate Total Value = Qty × Unit Price for all rows. =ARRAYFORMULA(D2:D*E2:E). Instructor checks results.
DEMO — STEP 5 (REVISION HISTORY): Instructor intentionally deletes a column of data. Students watch. Then instructor uses File > Version History > See version history to restore the deleted column. Key lesson: nothing is permanently lost in Google Sheets.
CLASS DEBRIEF: Compare IMPORTRANGE with copy-paste. Why is live data better? When would you still use paste?
Data Sources
https://docs.google.com/spreadsheets/d/1J1jdCe07qYCkI6_Plvj1DToU6lKHLVE3-eEtOuhYbi0/edit?usp=sharing
Solution Frameworks
Live Dashboard Architecture: Data Sources (branch sheets) → Aggregation (IMPORTRANGE) → Analysis (QUERY) → Insights (Charts). Each layer has one job.
QUERY Syntax Framework: SELECT → WHERE → GROUP BY → ORDER BY → LIMIT. Always build queries in this order.
Data Recovery Framework: Version History is your safety net. Name versions after major changes. Never permanently delete without checking history.
QUERY Syntax Framework: SELECT → WHERE → GROUP BY → ORDER BY → LIMIT. Always build queries in this order.
Data Recovery Framework: Version History is your safety net. Name versions after major changes. Never permanently delete without checking history.
Solver Guidance & Tutorials
Pre-authorize IMPORTRANGE connections before class — authorization prompts can delay demo unexpectedly.
Use a consistent dummy dataset across all 3 branch sheets. Same columns, same structure — only data values differ.
IMPORTIMAGE: Use publicly accessible image URLs (Google Images direct links do not work — use CDN or OpenWeatherMap icon URLs).
QUERY GROUP BY demo: Point out the LABEL clause — =QUERY(..., "SELECT F, SUM(D) GROUP BY F LABEL SUM(D) 'Total Units'", 1) — shows how to rename aggregated columns.
Revision History tip: Make 2-3 edits before the demo deletion so there are multiple named versions visible in history.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
Use a consistent dummy dataset across all 3 branch sheets. Same columns, same structure — only data values differ.
IMPORTIMAGE: Use publicly accessible image URLs (Google Images direct links do not work — use CDN or OpenWeatherMap icon URLs).
QUERY GROUP BY demo: Point out the LABEL clause — =QUERY(..., "SELECT F, SUM(D) GROUP BY F LABEL SUM(D) 'Total Units'", 1) — shows how to rename aggregated columns.
Revision History tip: Make 2-3 edits before the demo deletion so there are multiple named versions visible in history.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
What You'll Learn
- Problem-solving and analytical thinking
- Data-driven decision making
- Business strategy development
- Professional report writing
0
Solutions Submitted
Difficulty
Beginner
Estimated Time
55 minutes
Relevance
Fresh
Source
Google Sheets Module - Puneet Arora