Why external data expands analysis in Google Sheets - Dashboard Impact

Dashboard Mode - Why external data expands analysis

Dashboard Goal

Understand how adding external data, like regional population, helps analyze sales performance better.

Sample Data

Product	Region	Sales	Population
Apples	North	150	1000
Apples	South	200	1500
Bananas	North	180	1000
Bananas	South	220	1500
Cherries	North	90	1000
Cherries	South	130	1500

Dashboard Components

• Total Sales: =SUM(C2:C7) Result: 970
• Average Sales per Region: =AVERAGEIF(B2:B7,"North",C2:C7) Result: 140
• Sales per 1000 People (New KPI): =ARRAYFORMULA(C2:C7 / (D2:D7 / 1000)) Result: [150, 133.33, 180, 146.67, 90, 86.67]
• Summary Table: Shows Product, Region, Sales, Population, and Sales per 1000 People

Dashboard Layout

+----------------------+-------------------------+
|      Total Sales      |   Average Sales (North)  |
+----------------------+-------------------------+
|        Summary Table (Product, Region, Sales, Population, Sales per 1000)        |
+---------------------------------------------------------------------------------+

Interactivity

Filter by Region to update all KPIs and the summary table. Selecting 'North' or 'South' shows sales and population data only for that region, recalculating totals and averages accordingly.

Self Check

If you add a filter for Region = South, which components update?

• Total Sales changes to 550
• Average Sales per Region shows sales only for South (average 183.33)
• Sales per 1000 People recalculates for South rows
• Summary Table shows only South region rows

Key Result

Shows how adding population data helps analyze sales per capita by region.