Descriptive statistics with excel is a popular way to describe your data. It makes the tons of formula became easier just by simple click and drop.
Years ago, I do the formula one by one and find the statistic value that I need. It is really uncomfortable, is not it?
You have to remember the correct formula of your data and choose the right formula because perhaps there is more than one similar formula for one statistic value.
Look at he picture I present below. This is what you will using descriptive statistics with excel by typing the formula one by one
There are 7 formulas that excel provide to you to generate the variance of your data. My question is, which one will you use?
Still, writing one by one formula to summarize the statistic value that you need? Well, keep reading!
Microsoft Excel is a phenomenal software developed by Microsoft helping Billions of human to solve their problems. Excel helps us to makes almost our calculation problem easier included statistics.
Contents
Why using descriptive statistics in excel?
If you are wondering why you should Microsoft Excel to process your statistical data, let me tell you these interesting facts!
1. The user interface is so friendly
Yes, Microsoft Excel interface is so friendly so almost every user could use it without any meaningful problem. You may use it through simple steps and clicks to produce the output that you want.
2. No coding needed
Usually, almost all statistical software needs to code the form. But, Excel does not require you to code anything. You just need to know the simple formula or use the toolbar that will help you to finish the job.
3. Easy to interpret
The output is served simply so we may see it and understand what the output is. If you did not strong statistical basics, do not worry. It’s just basic formula which you may learn in your study.
Before using Microsoft Excel to process your data, you must activate the data analysis toolpak to makes your job easier.
Follow these simple steps!
1. Activate the data analysis toolpak, go to file >> options
2. Choose add ins >> analysis toolpak
3. Ok
Now, you will have the tools that you need to make your works easier and faster. By using this toolpak, you do not have to input every single formula that you need. Now, let’s calculate the descriptive statistics in excel
Steps of Descriptive Statistics With Excel
Already have your data set? Let’s do the analysis. Here is the steps!
1. Go to Data >> data analysis
2. You’ll see many statistical options there, choose descriptive statistics >> ok
3. In the popup window, you have several fields that you have to fill
- Input range: block the data you want to analyze
- Grouped by: whether the data is grouped in columns or rows
- Labels in the first row: if the blocked data has labels in the first row, check this
- Output options: where the output will be displayed
- Summary statistics: if you want to do descriptive statistics analysis
- The confidence level for mean: if you want to show confidence level for mean
- Kth largest: if you want to show the data in “k”th largest
- Kth smallest: if you want to show the data in “k”th smallest
4. Click Ok
5. See the magic happens!
Interpretation of Descriptive Statistics Output in Excel
1. Mean = 7,434. In average, there are 7,434 poor people in these 12 areas
2. Standard error = 468.412. This value indicates that the sample we chose has a fairly high distribution of the population mean.
3. Median = 7,575. This value indicates that the middle numbers of poor people based on the sample we use are 7,575 people.
4. Mode = 8000. This value shows that the most number of poor people based on the sample we have is 8000 people.
5. Standard deviation = 1622. This value indicates that the sample values that we use are spread far enough from the mean value.
6. Kurtosis = -0.68485. Because the value of kurtosis is smaller than 3, we can conclude that the sample used is platicurtic distribution (tends to be flat).
7. Skewness = -0.12018. Because the skewness value is smaller than zero, we can conclude that the data tends to be left inclined or left skewed.
8. Range = 5100. This value indicates that the difference between the regions with the highest number of poor people and the lowest number of poor people is 5100 people.
9. Minimum = 4900. This value shows the lowest number of poor people is 4900 people in the L area.
10. Maximum = 10,000. This value shows that the highest number of poor people is 10,000 people in J area.
11. Sum = 89,210. This value indicates that the total number of poor people based on the data used was 89,210 people.
12. Count = 12. This value indicates the amount of data used is 12.
13. Confidence level = 1030,968. It’s quite difficult to understand, right? Okay, keep reading.
Confidence interval means we will predict a value in the form of a range. In this case, we need upper values and lower values.
In the descriptive statistics feature in Microsoft Excel, they only provide one value, and this figure is very far from the mean.
The confidence level value that appears is a value that can be used to get the upper and lower limits of the confidence interval you are using.
If you want to get the upper limit, you simply add an average to the value of the confidence level. The following calculations. Check the picture below!
If you want to get a lower bound value, you can simply reduce the average value with that confidence level. Consider the following picture.
Now, let’s make the interpretation of this value!
With a confidence level of 95 percent, the average number of poor people in the 12 regions is 6,403 to 8,465 people.
The Disadvantage of Using Excel For Descriptive Statistics
1. The formula is limited
Although the formula is super easy just by drag and click, the numbers of excel formula in the statistical process are limited.
Excel provided not much formula so the user can use to do data processing. But, Excel is the best tool to study statistical computing if you want to be advanced in the future.
2. It is only for numerical data
It’s sad but if you are using categorical or non-numeric data, probably excel is not for your research. Excel only read the data in numeric format. Even you transform it into numerical form, it’s quite difficult to read the output.
3. It is only for single variable analysis
Conclusion
Overall, the steps of using descriptive statistics in excel are:
1. Prepare the data set.
2. Activate analysis toolpak add-ins add options menu.
3. Choose the descriptive statistics at the data analysis menu.
4. Check the statistic value that you want to generate.
5. Click Ok.
6. Do not forget to make the output interpretation.
If you want to do more advanced analysis by software, I recommend you to check the descriptive statistics on spss article. You will find an easier way to produce the descriptive statistics even for the numerical or categorical data set.
Thank you so much. Very helpful