A Comprehensive Guide to Descriptive Statistics in Excel

Key Takeaways:

• Descriptive Statistics helps summarize large data quickly.
• It shows values like mean, median, mode, and standard deviation.
• The Analysis ToolPak must be enabled to use this feature.
• It helps understand data trends, spread, and patterns.
• Descriptive Statistics is useful for finance, sales, research, and reports.

 

Introduction to Description Statistics in Excel

Description Statistics is an in-built tool available in Excel for data analysis. It is part of the Analysis ToolPak in Excel.

These statistics help us understand the central tendency, variability, and distribution of data points. It enables users to get insights into their data without the need for advanced statistical software.

The different statistical values that are displayed in descriptive statistics are as follows –

• Mean: The arithmetic average of the dataset values.
• Standard Error: The measure of the variability between predicted and actual values in the dataset.
• Median: The central value that separates the higher and lower halves of the dataset.
• Mode: The most frequently occurring value in the dataset.
• Standard Deviation: A measure of how much the dataset values deviate from the mean.
• Sample Variance: The squared standard deviation, indicates the dataset’s variability.
• Kurtosis: The descriptor of the tail behavior in the dataset’s distribution.
• Skewness: The measure of asymmetry in the dataset’s distribution.
• Range: The difference between the highest and lowest values in the dataset.
• Minimum: The smallest value present in the dataset.
• Maximum: The largest value present in the dataset.
• Sum: The total obtained by adding all the values in the dataset.
• Count: The number of values in the dataset.
• Largest(X): The highest X value.
• Smallest(X): The lowest X value.
• Confidence Level(X) Percentage

 

How to Calculate Descriptive Statistics

Suppose you are an investor who has been tracking the returns of an investment portfolio over the past year. The portfolio consists of various stocks and bonds. You have collected the monthly returns (in percentage) for each month, resulting in the following dataset:

We can easily follow the steps below to display Descriptive Statistics in Excel for this data –

STEP 1: Go to Search Bar and type Add-Ins.

STEP 2: Select Analysis ToolPak and Click OK.

STEP 3: Go to Data Tab > Data Analysis.

STEP 4: In the Data Analysis dialog box, select Descriptive Statistics and click OK.

STEP 5: In the Descriptive Statistics dialog box, select cell range as Input Range.

STEP 6: Select a cell for Output Range.

STEP 7: Check the Summary Statistics checkbox and click OK.

The output will now be displayed!

 

Interpretation of Results

• Mean (Average) Return: The portfolio’s average monthly return is approximately 2.5%.
• Median: The median return of 3.4% lies close to the mean.
• Mode: No mode indicates a diverse set of returns.
• Standard Deviation: Standard deviation suggests that the portfolio’s performance has been relatively stable.
• Range: The range of 8.3% showcases the spread between the highest and lowest returns.
• Minimum Return: The lowest return indicates the worst-performing month.
• Maximum Return: The highest return represents the best-performing month.
• Sum of Returns: It indicates the overall performance of the portfolio.
• Count: It returns the count of returns.

 

FAQs