Excel Descriptive Statistics Guide

[nerdslife_team]

Descriptive statistics are super handy for summarizing your data, giving you key stats like the average (mean), most common value (mode), middle value (median), spread (variance, standard deviation), and how skewed your data is, plus counts, maximum and minimum values. If you’re wrestling with a big dataset, these stats can really help you spot trends.

Excel has a bunch of functions for calculating these stats individually. But there’s also a super useful add-in called the Analysis Toolpak that does it all in one go. Let’s walk through how to get those descriptive statistics in Excel.

Getting the Data Analysis Toolpak

The Data Analysis tool is a powerful add-in for crunching numbers and generating lots of statistical info from your data. You’ll find it in the Data tab, but first you might need to install it. Here’s how:

First, click on the ‘File’ tab located in the top left corner of the Excel window.

Next, click ‘Options’ at the bottom of the left-hand menu.

When the Excel Options window pops up, click ‘Add-ins’ in the left panel.

At the bottom, you’ll see a ‘Manage’ dropdown. Make sure ‘Excel Add-ins’ is selected and click ‘Go…’.

A new window will open. Check the box for ‘Analysis ToolPak’ and click ‘OK’.

Now, the ‘Data Analysis’ tool should be in the ‘Data’ tab on the right side of the ribbon.

Getting Your Descriptive Stats

With the Data Analysis tool ready, let’s get those stats!

Imagine you have a dataset with the number of books sold in a few different cities and you want to calculate some descriptive statistics. Here’s what you do:

Go to the ‘Data’ tab and click on ‘Data Analysis’.

In the Data Analysis dialog, choose ‘Descriptive Statistics’ and click ‘OK’.

A new Descriptive Statistics dialog will appear. Now you need to tell it what data to use and where to put the results.

Let’s break down each of these options:

• Input Range: This is the most important part. Select the range of data you want to analyze.

  

  You can type the range or use the little arrow button at the end of the field to choose it manually.

  

  Select the range in your sheet and click the down arrow to confirm.

  

• Grouped By: Choose if your data is organized by rows or columns.

  

• Labels in first row: Check this if your selected range includes headers. In our example, we didn’t, so we leave it unchecked.

  

• Output options: You’ve got a few choices here for where to put your results:

  • Output Range: Select this to put the results right in your current sheet or another sheet and then specify the location.

    

  • New Worksheet Ply: Choose this to put the results in a brand new sheet within the same workbook.

    

  • New Workbook: Pick this one to put your results in a completely new Excel file.

    

• Summary statistics: This is what you want for most cases. Check this to get a whole range of stats like mean, median, standard deviation, etc.

  

• Confidence Level for Mean: Select this to get a confidence interval for the mean. The default is 95%, but you can change it.

  

• Kth Largest: If you want to see the nth largest value in your data, check this and enter the ‘k’ value. The default is 1 (the largest), but you can enter 2, 3, etc. for the 2nd, 3rd, largest and so on.

  

• Kth Smallest: Similar to Kth Largest, but this shows the nth smallest value. The default is 1 (the smallest), but you can enter other values to find the second, third smallest, etc.

  

Once you’ve set all the options, click ‘OK’.

You will get a summary of Descriptive Statistics like this:

Understanding the results

Here’s what those stats mean:

• Mean - The average value of your data.
• Standard error - Measures how far off your sample mean is likely to be from the true population mean.
• Median - The middle value of your data when it’s ordered.
• Mode - The most frequently occurring value. If there is no single most common value, it will show as #N/A.
• Standard deviation - How spread out your data is from the mean. Higher means more spread.
• Sample variance - The average of the squared standard deviations.
• Kurtosis - Measures the peakedness of your distribution compared to a normal one (0 means normal).
• Skewness - How symmetrical your distribution is. 0 is perfectly symmetrical.
• Range - The difference between the largest and smallest values in your data.
• Minimum - The smallest value in your dataset.
• Maximum - The largest value in your dataset.
• Sum - The sum of all the values in your dataset.
• Count - The number of values in your dataset.
• Largest - The nth largest value, based on what you put in the ‘Kth Largest’ field.
• Smallest - The nth smallest value, based on what you put in the ‘Kth Smallest’ field.
• Confidence Level (95.0%) - The range of values that likely contains your population mean.

You can also find the upper and lower limits of the confidence interval using the Confidence Level value from the results.

To get the upper limit, add the mean and the confidence level using the formula =B3+B18, resulting in 95.8891.

To find the lower limit, subtract the confidence level from the mean using the formula =B3-B18, which results in 48.3109.

Calculate Individual Statistics using Formulas

The Data Analysis tool is great for getting all the stats at once, but sometimes you just want to calculate one or two specific stats. You can do this with individual Excel formulas to calculate each measure. The results will match what you get from the Data Analysis tool.

Here’s a list of formulas:

Method 1: Using formulas

• Mean:

  =AVERAGE(B2:B21)
  

  Replace B2:B21 with your data range.
  

  

• Standard Deviation Error:

  =STDEV.S(B2:B21)/SQRT(COUNT(B2:B21))
  

  

• Median:

  =MEDIAN(B2:B21)
  

  

• Standard Deviation:

  =STDEV.S(B2:B21)
  

  

• Sample Variance:

  =VAR.S(B2:B21)
  

  

• Mode:

  =MODE.SNGL(B2:B21)
  

  

• Kurtosis:

  =KURT(B2:B21)
  

  

• Skewness:

  =SKEW(B2:B21)
  

  

• Range:

  =MAX(B2:B21)-MIN(B2:B21)
  

  

• Minimum:

  =MIN(B2:B21)
  

  

• Maximum:

  =MAX(B2:B21)
  

  

• SUM:

  =SUM(B2:B21)
  

  

• Count:

  =COUNT(B2:B21)
  

  

• Kth Largest Value:

  =LARGE(B2:B21,1)
  

  Change the 1 to 2 to find the second largest, 3 for the third largest and so on.
  

  

• Kth Smallest Value:

  =SMALL(B2:B21,2)
  

  Change the 2 to 1 to find the smallest value, 3 for the third smallest and so on.