How to use Descriptive Statistics in Excel

Facebook0 Tweet0 LinkedIn0 Email0

Excel has a pretty large range of statistics functions built in to it, including a lot of the standard measures you would expect when working with data – SUM, MODE, MEDIAN, Standard Deviation and so on. Descriptive Statistics in Excel in essentially an all in one tool that let’s us very quickly and easily measure a range of statistics for a dataset.

In this simple guide, we will run through the process for running Descriptive Statistics. We will cover:

How to enable the Data Analysis menu
How to run Descriptive Statistics

First let’s run through the Data Analysis tool in Excel

Table of Contents

How to enable Data Analysis

If you already have the Data Analysis ToolPak enabled you can skip through to the next section.

For those who do not or are not sure, in order to run Descriptive Statistics in Excel we will first need to enable this.

The Data Analysis button sits on the very right of the top ribbon in Excel under the Data sub heading, and looks like the below:

If you do not have this button available, we will need to activate the Data Tookpak.

To do so, enter the Options menu under the File header found below:

Next, under the Add-Ins section, select Go under the Manage Excel Add-ins button at the bottom:

Finally, select the check box for Analysis Toolpak and press OK:

Now that we have the Analysis ToolPak enabled, let’s move on to running the Descriptive Statistics.

Running Descriptive Statistics in Excel

For the purposes of this example we are going to work with a pretty basic sample set – a series of student’s exam scores. We have 17 different scores from the class, with possible scores of 0 through to 100. See below:

We could just set up a table to the side of this and individually run every function we want, but theres a good chance that a lot of these are covered by Descriptive Statistics.

To get started, first click into the Data Analysis menu that we enabled earlier, and select Descriptive Statistics:

From here in the box that appears we will have a whole range of options we can choose from:

Input: This is the range of cells, so from the header to the bottom value

Grouped By: In our example we group by columns as the data goes down a column. Sometimes we may be working with multiple columns, or data that goes across rows instead so make sure to select whichever is relevant.

Labels in First Row: In our case we are ticking this because we have a clear header of what the column contains.

Output Options: New worksheet is usually easiest and cleanest, but you can also export to a new workbook or a specific part of the sheet you are already on

Summary Statistics: Tick this one. Otherwise we won’t really get a whole lot of info!

Confidence Level for Mean: Leaving this at 95% is fine as it is a good default to run with. Feel free to change it though depending on your use case.

Kth Largest and Smallest: This determines the highest and lowest values in our dataset – leaving at 1 includes everything, but if we changed to 2 or 3 it would be 2nd highest, 2nd lowest, 3rd highest, 3rd lowest and so on. We are leaving it at 1.

From here simply press ok, and we will be taken to a new worksheet with our summary:

As we can see, the Descriptive Statistics function has presented a series of key statistics in a nice easy to read way! This has saved us a fair bit of time in running 16 different calculations at once. From here if there are further calculations required we can then just run those separately.

This sums up our simple guide on how to run Descriptive Statistics in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.