Why built-in tools accelerate analysis in Excel - Dashboard Impact

Dashboard Mode - Why built-in tools accelerate analysis

Dashboard Goal

Understand how Excel's built-in tools speed up data analysis by summarizing sales data quickly and clearly.

Sample Data

Region	Salesperson	Product	Units Sold	Unit Price	Sale Date
East	Alice	Widget	10	15	2024-01-05
West	Bob	Gadget	5	20	2024-01-07
East	Charlie	Widget	7	15	2024-01-10
North	Dana	Gizmo	3	25	2024-01-12
West	Eva	Widget	8	15	2024-01-15
South	Frank	Gadget	6	20	2024-01-18
East	Grace	Gizmo	4	25	2024-01-20

Dashboard Components

• Total Sales (KPI Card): Shows total revenue from all sales.
  Formula: =[Grand Total cell from PivotTable] (updates dynamically with slicer)
  Result: 10*15 + 5*20 + 7*15 + 3*25 + 8*15 + 6*20 + 4*25 = 150 + 100 + 105 + 75 + 120 + 120 + 100 = 770
• Sales by Region (Pivot Table): Summarizes total sales revenue by region.
  Built-in tool: Insert > PivotTable with Region in Rows and Sum of (Units Sold * Unit Price) in Values.
  Example values:
  East: 150 + 105 + 100 = 355
  West: 100 + 120 = 220
  North: 75
  South: 120
• Sales Trend (Line Chart): Shows total sales over time by Sale Date.
  Built-in tool: Insert > Line Chart using Sale Date and total sales per date.
  Data points:
  2024-01-05: 150
  2024-01-07: 100
  2024-01-10: 105
  2024-01-12: 75
  2024-01-15: 120
  2024-01-18: 120
  2024-01-20: 100
• Filter by Product (Slicer): Allows quick filtering of all components by product type (Widget, Gadget, Gizmo).
  Built-in tool: Insert > Slicer connected to the PivotTable and chart.

Dashboard Layout

+----------------------+-----------------------+
| Total Sales (KPI)    | Filter by Product      |
|       770            | (Slicer)              |
+----------------------+-----------------------+
| Sales by Region      (Pivot Table)           |
| +------------------------------------------+ |
| | East: 355                                | |
| | West: 220                                | |
| | North: 75                                | |
| | South: 120                               | |
| +------------------------------------------+ |
+----------------------------------------------+
| Sales Trend (Line Chart)                      |
| +------------------------------------------+ |
| | Dates on X-axis, Sales on Y-axis         | |
| +------------------------------------------+ |
+----------------------------------------------+

Interactivity

The Filter by Product slicer lets you pick one or more products. When you select a product, the Pivot Table and Line Chart update to show only sales for that product. The Total Sales KPI also recalculates to show total revenue for the selected product(s). This connection happens automatically when the slicer is linked to the Pivot Table and chart.

Self Check

If you select Widget in the Product slicer, which components update and what changes?

• Total Sales: Updates to sum sales only for Widget products: 10*15 + 7*15 + 8*15 = 150 + 105 + 120 = 375
• Sales by Region: Shows only Widget sales by region:
  East: 150 + 105 = 255
  West: 120
  North: 0
  South: 0
• Sales Trend: Shows sales over time only for Widget sales dates.

Key Result

Dashboard showing total sales, sales by region, and sales trends with product filtering using Excel's built-in tools.