Selecting data for charts in Excel - Dashboard Guide

Dashboard Mode - Selecting data for charts

Dashboard Goal

See sales trends by product and region to understand which products sell best in each area.

Sample Data

Month	Product	Region	Sales
Jan	Apples	North	120
Jan	Bananas	South	150
Feb	Apples	North	130
Feb	Bananas	South	160
Mar	Apples	North	140
Mar	Bananas	South	170

Dashboard Components

• Line Chart: Shows monthly sales trend for Apples in North region.
  Data selected: Months (A2:A7), Sales for Apples in North (D2:D7 filtered by Product='Apples' and Region='North').
• Bar Chart: Shows total sales by product in South region.
  Data selected: Products (B2:B7 filtered by Region='South'), Sales summed by product.
• Summary Table: Shows total sales by product and region.
  Formula example for Apples North total sales: =SUMIFS(D2:D7,B2:B7,"Apples",C2:C7,"North")

Dashboard Layout

+----------------------+----------------------+
|      Line Chart      |      Bar Chart       |
|  (Apples North Sales) | (South Region Sales) |
+----------------------+----------------------+
|                 Summary Table                |
|          (Total Sales by Product & Region)  |
+---------------------------------------------+

Interactivity

Adding a filter for Region updates both charts and the summary table to show data only for the selected region. Selecting a different region changes the bar chart products and the line chart data accordingly.

Self Check

If you add a filter for Region = South, which components update and what data do they show?

• The Line Chart will show no data for Apples in North because the filter excludes North region.
• The Bar Chart will show total sales by product in South region.
• The Summary Table will update to show totals only for South region.

Key Result

Dashboard shows monthly sales trends and total sales by product and region with interactive region filtering.