Descriptive statistics (Analysis ToolPak) in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants a summary report showing key descriptive statistics for monthly sales data to understand sales performance.

📊 Data: You have monthly sales figures for 12 months in a single column.

🎯 Deliverable: Create a descriptive statistics report using Excel's Analysis ToolPak that shows mean, median, mode, standard deviation, variance, minimum, maximum, range, and count.

Sample Data

Month	Sales
Jan	1200
Feb	1350
Mar	1280
Apr	1420
May	1380
Jun	1500
Jul	1600
Aug	1550
Sep	1480
Oct	1520
Nov	1580
Dec	1650

1

Step 1: Enable the Analysis ToolPak add-in in Excel.

Go to File > Options > Add-ins > Manage Excel Add-ins > Go > Check 'Analysis ToolPak' > OK

Expected Result

Analysis ToolPak is enabled and available under the Data tab.

2

Step 2: Select the sales data range for analysis.

Select cells B2:B13 which contain the sales figures.

Expected Result

Sales data range B2:B13 is selected.

3

Step 3: Open the Data Analysis tool and choose Descriptive Statistics.

Go to Data tab > Data Analysis > Select 'Descriptive Statistics' > OK

Expected Result

Descriptive Statistics dialog box opens.

4

Step 4: Configure the Descriptive Statistics input range and output options.

Input Range: B2:B13; Check 'Labels in first row' if applicable; Output Range: select a cell like D2; Check 'Summary statistics'; Click OK

Expected Result

Excel generates a summary table with descriptive statistics starting at the chosen output cell.

5

Step 5: Review the generated descriptive statistics report.

No formula needed; review output table.

Expected Result

Report shows count=12, mean=1445, standard deviation≈140.3, minimum=1200, maximum=1650, range=450, median=1485, mode=No mode, variance≈19684.

Final Result

Descriptive Statistics Report
-----------------------------
Count: 12
Mean: 1445
Standard Deviation: 140.3
Variance: 19684
Minimum: 1200
Maximum: 1650
Range: 450
Median: 1485
Mode: No mode

✓The average monthly sales is 1445 units.

✓Sales vary with a standard deviation of about 140 units.

✓Sales range from 1200 to 1650 units, showing moderate variation.

✓There is no repeating sales value (no mode).

Bonus Challenge

Create a line chart to visualize the monthly sales trend alongside the descriptive statistics report.

Show Hint

Select the Month and Sales columns, then insert a Line Chart from the Insert tab.