Subtotals in Excel - Dashboard Guide

Dashboard Mode - Subtotals

Dashboard Goal

Show total sales and subtotals by Region and Product Category to understand which areas and products perform best.

Sample Data

Region	Product Category	Sales
East	Furniture	100
East	Office Supplies	150
East	Technology	200
West	Furniture	120
West	Office Supplies	180
West	Technology	220
South	Furniture	130
South	Office Supplies	170

Dashboard Components

• Total Sales: A KPI card showing total sales.
  Formula: =SUBTOTAL(9,C2:C9)
  Result: 1270
• Sales by Region: A subtotal table showing sales grouped by Region.
  Formula: Use Excel's Subtotal feature or formula:
  =SUBTOTAL(9, C2:C4) for East,
  =SUBTOTAL(9, C5:C7) for West,
  =SUBTOTAL(9, C8:C9) for South.
  Results:
  East: 450
  West: 520
  South: 300
• Sales by Product Category: A subtotal table showing sales grouped by Product Category.
  Formula example for Furniture:
  =SUMIF(B2:B9, "Furniture", C2:C9)
  Results:
  Furniture: 350
  Office Supplies: 500
  Technology: 420

Dashboard Layout

+----------------+-----------------------+
| Total Sales    | Sales by Region       |
| (KPI Card)     | (Subtotal Table)      |
+----------------+-----------------------+
| Sales by Product Category Subtotal Table |
+------------------------------------------+

Interactivity

Add a filter for Region. When you select a specific region, the Sales by Product Category table updates to show sales only for that region. The Total Sales and Sales by Region components also update to reflect the filtered data.

Self Check

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

• Total Sales: Updates to 450
• Sales by Region: Shows only East with 450
• Sales by Product Category: Shows East region sales:
  Furniture: 100
  Office Supplies: 150
  Technology: 200

Key Result

Dashboard showing total sales and subtotals by Region and Product Category with filtering.