This is the ultimate guide on how to perform univariate analysis in Excel.

##### Table of Contents

Excel has many built-in functions and tools we can utilize for different purposes. And one of the most popular ways to use Excel is by performing statistical calculations and data analysis.

Data analysis can be a long and tiring process which usually only takes one mistake for the entire calculation to be wrong. However, data analysis is really easy and simple to do in Excel.

In this case, we will learn how to perform univariate analysis in Excel. From the name itself, it is a data analysis type focusing only on one variable. And univariate analysis can be done by using summary statistics.

So summary statistics have two popular types. Firstly, we have the measures of central tendency, which describes the center of a data set. For example, the mean, median, and mode.

Secondly, we have the measures of dispersion which describes the spread of values in a data set. For instance, we have the standard deviation, the interquartile range, and the range.

Luckily, all of those statistical measures have a corresponding built-in function in Excel we can easily utilize to perform the analysis quickly.

Let’s take a sample scenario wherein we need to perform univariate analysis in Excel.

Suppose you have a data set containing the number of times a student has been absent and late. And you want to perform a univariate analysis on the number of times the students have been absent.

But, performing this manually would be time-consuming and inefficient. So you opted to do this in Excel with the help of its built-in functions.

Great! Now move on and dive into a real example of performing univariate analysis in Excel.

**A Real Example of Performing Univariate Analysis in Excel**

Let’s say we have a data set containing three columns. So we have a column containing the student IDs. Then, we have a column each for the number of times a student has been absent or late. And our initial data set would look like this:

Essentially, univariate analysis is the simplest form of data analysis since we only have one variable to analyze. And it explores each value in the data set separately and describes each variable on its own.

Furthermore, we can perform univariate analysis by describing the values using summary statistics. In other words, we can simply find the measures of central tendency and measures of dispersion of the data set.

Firstly, the measures of central tendency are used to describe the center of a data set. So we have the mean, which is the average value of the data set. Then, the media refers to the middle value of the data set. Lastly, the mode refers to the value appearing most in a data set.

Secondly, the measures of dispersion are used to describe the spread of values in a data set. So the standard deviation describes the dispersion of a data set relative to the mean.

Next, the interquartile range tells us the spread of the middle half of the data set. Finally, the range is the difference between the highest and lowest values.

Furthermore, we can calculate all of these statistical measures in Excel using the built-in functions. So we will simply use the functions that calculate these values and get summary statistics to perform a univariate analysis.

And our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below.

Amazing! Now let’s dive into the steps of how to perform univariate analysis in Excel.

**How to Perform Univariate Analysis in Excel**

In this section, we will explain the step-by-step process of how to perform univariate analysis in Excel. Furthermore, each step contains pictures and detailed instructions to guide you along the way.

1. Firstly, we will perform a univariate analysis of the number of times the students have been absent. So we will focus on the **Absent **column. Now we will first calculate the mean using the `AVERAGE`

function. Then, we will type in the formula “**=AVERAGE(C2:C11)**”. Lastly, press the **Enter **key to return the result.

2. Secondly, we will calculate the median using the `MEDIAN`

function. So input the formula “**=MEDIAN(C2:C11)**”. Next, we will press the **Enter **key to return the result.

3. Thirdly, we will calculate the mode using the `MODE.SNGL`

function. In this case, we will type in the formula “**=MODE.SNGL(C2:C11)**”. Finally, we will click the **Enter **key to get the mode value.

4. Afterward, we will get the standard deviation using the `STDEV.P`

function. To do this, we will input the formula “**=STDEV.P(C2:C11)**”. Lastly, press the **Enter **key to get the value.

5. Next, we will get the interquartile range using `QUARTILE.INC`

function. In this case, we will type in the formula “**=QUARTILE.INC(C2:C11,3)-QUARTILE.INC(C2:C11,1)**”. Next, we will press the **Enter **key to return the value.

6. Lastly, we will calculate the range of the data set. To do this, we will be using the `MAX`

and `MIN`

functions. So we will type in the formula “**=MAX(C2:C11)-MIN(C2:C11)**”. Finally, we will press the **Enter **key to get the range.

7. And tada! We performed a univariate analysis in Excel using summary statistics.

And that’s pretty much it! We have successfully performed a univariate analysis in Excel. Now you can perform the simplest form of data analysis in your work whenever you need to.

Are you interested in learning more about what Excel can do? You can now use the `MODE`

function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.