Why advanced charts tell better stories in Excel - Dashboard Impact

Dashboard Mode - Why advanced charts tell better stories

Dashboard Goal

Understand how advanced charts like combo charts and sparklines help tell clearer stories from sales data.

Sample Data

Month	Sales	Profit	Expenses
Jan	1000	300	700
Feb	1200	350	850
Mar	900	250	650
Apr	1400	400	1000
May	1300	370	930
Jun	1500	420	1080

Dashboard Components

• KPI Cards: Show total sales, total profit, and total expenses.
  Formulas:
  Total Sales: =SUBTOTAL(109,B2:B7) (Result: 7300)
  Total Profit: =SUBTOTAL(109,C2:C7) (Result: 2090)
  Total Expenses: =SUBTOTAL(109,D2:D7) (Result: 5210)
• Combo Chart: Displays Sales as columns and Profit as a line over months.
  Helps compare sales volume and profit trend together.
• Sparklines: Small line charts inside cells showing monthly sales trend.
  To create in E2: Select E2, Insert > Sparklines > Line, Data Range: B2:B7.

Dashboard Layout

+----------------------+----------------------+
|   KPI Cards (Sales,   |    Combo Chart:      |
|   Profit, Expenses)   |  Sales & Profit over  |
|                      |       Months          |
+----------------------+----------------------+
|          Sparklines (Monthly Sales Trend)    |
+----------------------------------------------+

Interactivity

Add a filter for Month to select specific months. When a month is selected, the KPI cards, combo chart, and sparklines update to show data only for those months. This helps focus on particular periods and see detailed trends.

Self Check

If you add a filter to show only months Jan to Mar, which components update?

• KPI Cards show totals for Jan, Feb, Mar only.
• Combo Chart redraws to show sales and profit for Jan, Feb, Mar.
• Sparklines update to show sales trend for Jan to Mar.

Key Result

Dashboard showing how combo charts and sparklines help visualize sales and profit trends alongside key totals.