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.

Practice

1. What is the main purpose of using the Descriptive Statistics tool in Excel's Analysis ToolPak?

easy

A. To quickly summarize data with measures like mean and standard deviation

B. To create complex charts and graphs

C. To sort data alphabetically

D. To filter data based on conditions

Solution

1. Step 1: Understand the function of Descriptive Statistics tool

   This tool calculates summary numbers like mean, median, mode, variance, and standard deviation.

2. Step 2: Compare with other options

   Options B, C, and D describe other Excel features, not descriptive statistics.

3. Final Answer:

   To quickly summarize data with measures like mean and standard deviation -> Option A

4. Quick Check:

   Descriptive statistics = summary numbers [OK]

Hint: Descriptive stats = summary numbers like average and spread [OK]

Common Mistakes:

• Confusing descriptive statistics with chart creation
• Thinking it sorts or filters data
• Assuming it performs data entry or formatting

2. Which of the following is the correct way to start the Descriptive Statistics tool in Excel's Analysis ToolPak?

easy

A. Go to Data tab -> Data Analysis -> Select Descriptive Statistics -> Input range -> Check Summary statistics

B. Go to Insert tab -> PivotTable -> Select Descriptive Statistics

C. Go to Home tab -> Conditional Formatting -> Descriptive Statistics

D. Go to Review tab -> Protect Sheet -> Descriptive Statistics

Solution

1. Step 1: Recall the steps to access Descriptive Statistics

   It is accessed via Data tab -> Data Analysis -> Descriptive Statistics.

2. Step 2: Verify the input and options

   You must select the input range and check the box for summary statistics.

3. Final Answer:

   Go to Data tab -> Data Analysis -> Select Descriptive Statistics -> Input range -> Check Summary statistics -> Option A

4. Quick Check:

   Data tab + Data Analysis = Descriptive Statistics [OK]

Hint: Data Analysis tool is under Data tab, not Insert or Home [OK]

Common Mistakes:

• Looking for Descriptive Statistics under Insert or Home tabs
• Not enabling Analysis ToolPak add-in first
• Skipping the Summary statistics checkbox

3. Given the data in cells A1:A5 as 5, 7, 7, 10, 12, what is the mean value shown by the Descriptive Statistics tool?

medium

A. 7.2

B. 8.2

C. 8.0

D. 7.0

Solution

1. Step 1: Calculate the sum of the data

   Sum = 5 + 7 + 7 + 10 + 12 = 41

2. Step 2: Divide sum by number of values

   Mean = 41 / 5 = 8.2

3. Final Answer:

   8.2 -> Option B

4. Quick Check:

   (5+7+7+10+12)/5 = 8.2 [OK]

Hint: Mean = sum of values ÷ count of values [OK]

Common Mistakes:

• Adding incorrectly or missing a number
• Dividing by wrong count (e.g., 4 instead of 5)
• Confusing mean with median or mode

4. You used the Descriptive Statistics tool but the output shows an error or no results. Which of these is the most likely cause?

medium

A. You used the wrong font in the worksheet

B. You forgot to select the output range

C. You did not enable the Developer tab

D. The input range includes empty cells or text values

Solution

1. Step 1: Check input data validity

   Descriptive Statistics requires numeric data; empty or text cells cause errors.

2. Step 2: Review other options

   Output range selection is optional; Developer tab and font do not affect results.

3. Final Answer:

   The input range includes empty cells or text values -> Option D

4. Quick Check:

   Non-numeric input causes errors [OK]

Hint: Ensure input range has only numbers, no blanks or text [OK]

Common Mistakes:

• Including blank or text cells in input range
• Confusing output range with input range
• Thinking Developer tab is needed for Analysis ToolPak

5. You have sales data for 12 months in cells B2:B13. You want to find the mean, median, mode, and standard deviation quickly using the Analysis ToolPak. Which steps should you follow?

hard

A. Insert tab -> Chart -> Select B2:B13 -> Choose Descriptive Statistics -> OK

B. Formulas tab -> Insert Function -> Select Descriptive Statistics -> Input B2:B13 -> OK

C. Data tab -> Data Analysis -> Descriptive Statistics -> Input Range B2:B13 -> Check Summary statistics -> Choose Output Range -> OK

D. Review tab -> Protect Sheet -> Select B2:B13 -> Descriptive Statistics -> OK

Solution

1. Step 1: Access Descriptive Statistics via Data Analysis

   Go to Data tab, click Data Analysis, then select Descriptive Statistics.

2. Step 2: Set input range and options

   Input range is B2:B13, check Summary statistics, select output range, then click OK.

3. Final Answer:

   Data tab -> Data Analysis -> Descriptive Statistics -> Input Range B2:B13 -> Check Summary statistics -> Choose Output Range -> OK -> Option C

4. Quick Check:

   Data Analysis + Summary statistics = descriptive stats [OK]

Hint: Use Data Analysis tool, select input and check summary stats [OK]

Common Mistakes:

• Trying to find Descriptive Statistics under Insert or Formulas tabs
• Not checking Summary statistics box
• Not selecting correct input range