Descriptive Statistics Excel (Table of Contents)
What is Descriptive Statistics in Excel?
Data Analysis becomes a part all the analyst’s day to day work. By looking the data we may need to find the average value, mean value, variance, standard deviation, maximum value, minimum value and much more related to statistics.
Finding each one of the statistics results one by one will take a lot of time. Descriptive Statistics in excel will help us to describe and understand the features of a specific data in summary way. The most common types of descriptive statistics in excel are mean, median, mode, variance, standard deviation etc.
We can do these descriptive statistics in excel under Data Analysis option. Data Analysis is available under the data tab. Even though it is a built-in tool in excel you may not see this at the first go you need to unhide it. Follow below steps to unhide it.
Step 1: Go to File > Options.
Step 2: Go to Add-ins
Step 3: Under Add-ins on the right-hand side you will see all the inactive Applications. Select Analysis Toolpak and click on GO.
Step 4: Now you will all the add-ins available for your excel. Select Analysis Toolpak and click on OK.
Now you must see the Data Analysis option under the Data tab.
Click on Data Analysis you will see all the available analysis techniques like Anova, T-Test, F-test, Correlation, Histogram, Regression, Descriptive Statistics and many more under this tool.
In this article, we will concentrate only on Descriptive Statistics.
How to Use Descriptive Statistics in Excel?
Below are the Examples of using Descriptive Statistics in Excel.
Descriptive Statistics in Excel Example #1
Now, look at the simple data from a test which includes the scores of 10 students. Using this data of scores we need to the Descriptive Statistics data analysis.
Copy this data to your excel sheet.
Step 1: Go to Data > Data Analysis.
Step 2: Once you click on Data Analysis you will list of all the available analysis techniques. Scroll down and select Descriptive Statistics.
Step 3: Under Input Range select the range of Scores including heading, Check Labels in the first row, Select Output range and give cell reference as D1 and check Summary statistics.
Step 4: Click on OK to complete the task. In D1 cell you will see the summary report of Descriptive Statistics data analysis.
We got all kind of statistical results pertaining to the data we have selected i.e. scores.
Average Score (Mean) is 70.2, Standard Deviation is 15.97, Minimum Score is 46, the maximum score is 91, total scores sum id 702 and the total number of students this sample is 10. Like this, we have all kinds of statistical results.
Descriptive Statistics in Excel Example #2
We have learned how descriptive statistics works in the previous example. Download the workbook to this descriptive statistics in Excel.
I have a list of Students, their age, gender, height, weight, weekly hours study, and recent examination score details for few students.
By looking at this above data the common questions are what is the average age of the student group? , average weight, average exam score, average height, Maximum value in each category, Minimum value etc…
We have as many as 5 different categories to tell the statistical results. We can conduct a Descriptive Statistical Analysis to find all these.
Step 1: Go to Data > Data Analysis.
Step 2: Once you click on Data Analysis you will list of all the available analysis techniques. Scroll down and select Descriptive Statistics.
Step 3: Under Input Range select all the category range including headings i.e. C1:G26.
We can get the summary result in the same worksheet, different worksheet, and different workbook as well. Based on the selection we give it will show the summary report. In this example, I have taken the option of showing the summary in the same worksheet i.e. from J1 cell
Since we have selected the headings we need to tick the checkbox Labels in the first row. Because we have selected the headings it will be helpful while showing the results otherwise it will be confusing to understand each category results.
And then tick the Summary Statistics option
Step 4: Click on OK to compete for the test. We will get the descriptive statistics results from J1 cell.
It has shown up all the statistical results for all five categories. A total number of students is 25, the average age is 26.64, average height is 5.244, average weight is 67.44, and average exam score is 57.8 which is relatively low compared to modern day standards and many other results.
Things to Remember About Descriptive Statistics in Excel
- Descriptive Statistics in Excel is a bundle of many statistical results.
- Label as the first row means data range we have selected includes headings as well.
- We can find the average value using an AVERAGE function like this maximum value by MAX, minimum value by MIN functions.
- SUMMARY will be displayed based on the selection we make.
You can download this Descriptive Statistics Excel template here –Descriptive Statistics Excel Template
Recommended Articles
This has been a guide to Descriptive Statistics in Excel. Here we discuss how to use descriptive statistics tool in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
Leave a Reply