Why structured data enables analysis in Excel - Dashboard Impact

Dashboard Mode - Why structured data enables analysis

Business Question

How does organizing sales data in a structured table help us quickly analyze total sales, average sales, and sales by product?

Sample Data

Product	Region	Sales
Apples	North	100
Bananas	South	150
Apples	East	200
Bananas	North	120
Cherries	South	180
Cherries	East	160

Dashboard Components

• Total Sales: A KPI card showing total sales.
  Formula: =SUM(C2:C7)
  Result: 910
• Average Sales: A KPI card showing average sales per entry.
  Formula: =AVERAGE(C2:C7)
  Result: 151.67
• Sales by Product: A summary table showing total sales for each product.
  Formula in E3 (for Apples): =SUMIF(A2:A7, "Apples", C2:C7)
  Results:
  Apples: 300
  Bananas: 270
  Cherries: 340

Dashboard Layout

+----------------+----------------+--------------------+
| Total Sales    | Average Sales  | Sales by Product    |
|    910         |    151.67      | Apples: 300         |
|                |                | Bananas: 270        |
|                |                | Cherries: 340       |
+----------------+----------------+--------------------+

Interactivity

Add a filter for Region to select one or more regions (North, South, East). When you choose a region, all components update to show sales only for that region. For example, selecting North updates total sales, average sales, and sales by product to reflect only North region data.

Self Check

If you add a filter to show only South region sales, which components update and what are their new values?

• Total Sales: 330
• Average Sales: 165
• Sales by Product:
  Bananas: 150
  Cherries: 180
  Apples: 0 (no sales in South)

Key Result

This dashboard shows how structured sales data enables quick calculation of total, average, and product-wise sales with easy filtering by region.