Descriptive statistics (Analysis ToolPak) in Excel - Dashboard Guide

Dashboard Mode - Descriptive statistics (Analysis ToolPak)

Dashboard Goal

Understand the basic descriptive statistics of monthly sales data to see average sales, variation, and distribution.

Sample Data

Month	Sales
January	1200
February	1500
March	1100
April	1700
May	1600
June	1400
July	1300
August	1800

Dashboard Components

• KPI Card: Average Sales
  Formula: =AVERAGE(B2:B9)
  Result: 1437.5
• KPI Card: Maximum Sales
  Formula: =MAX(B2:B9)
  Result: 1800
• KPI Card: Minimum Sales
  Formula: =MIN(B2:B9)
  Result: 1100
• KPI Card: Standard Deviation
  Formula: =STDEV.S(B2:B9)
  Result: 234.52 (rounded)
• Descriptive Statistics Table (using Analysis ToolPak)
  Output range example: D2
  Includes: Mean, Median, Mode, Standard Error, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, Count

Dashboard Layout

+----------------------+----------------------+----------------------+
| Average Sales (KPI)  | Maximum Sales (KPI)  | Minimum Sales (KPI)  |
+----------------------+----------------------+----------------------+
| Standard Deviation    |                      |                      |
+----------------------+                      |                      |
| Descriptive Stats Table (D2:G12)             |                      |
+----------------------------------------------+----------------------+

Interactivity

Currently static data. To explore further, add a filter for months or sales ranges to update KPIs and descriptive statistics dynamically.

Self Check

If you add a filter to show only months with sales above 1400, which components update?

• Average Sales KPI will recalculate for filtered months.
• Maximum and Minimum Sales KPIs will update accordingly.
• Standard Deviation will reflect filtered data variation.
• Descriptive Statistics Table will show stats only for filtered months.

Key Result

Dashboard shows key descriptive statistics for monthly sales using Analysis ToolPak outputs and formulas.