Multi-Branch Sales Dashboard
Beginner
55 min
16 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?
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