As an Excel expert, one of the essential calculations that you may need to perform is finding the variance. Variance is a statistical measure used to determine the degree of variance or deviation between the values in a given dataset. Excel has builtin functions that can help you calculate this measure of dispersion effortlessly. In this blog, we will discuss and demonstrate the simple steps involved in finding the variance in Excel. By the end of this post, you will understand the concept behind variance and be able to calculate it using Excel confidently.
Understanding Variance in Excel
Before we dive into the steps for finding variance in Excel, let’s discuss what variance is and why it’s essential. Variance is a measure of how spread out a group of numbers is from the mean (average) of those numbers. The larger the variance, the more spread apart the numbers are, and the smaller the variance, the closer the numbers are to the mean.
Calculating Variance in Excel Using the VAR Function
The easiest way to find the variance in Excel is by using the VAR function. The VAR function returns the variance of a data set consisting of one or more numbers.
Step 1: Enter Your Data
First, open a new or existing Excel spreadsheet and enter your data into a column or row. For example, let’s say you have a list of test scores:
Test Scores  
80.2  75.5  90.7  85.1 
Step 2: Use the VAR Function
Next, select a cell where you want to display the variance and enter the following formula:
=VAR(A1:A4)
The range A1:A4 refers to the cells that contain your test scores. Press enter, and the variance will be displayed in the selected cell.
Calculating Variance in Excel Using the VAR.S and VAR.P Functions
Excel also has two additional variations of the VAR function: VAR.S and VAR.P. The VAR.S function is used when your data set represents a sample of a larger population, while VAR.P is used when you have data for an entire population.
Step 1: Enter Your Data
Enter your data into a column or row, just like in Step 1 of the previous section.
Step 2: Use the VAR.S or VAR.P Function
Select a cell where you want to display the variance and enter either formula:
=VAR.S(A1:A4)
=VAR.P(A1:A4)
The range A1:A4 refers to the cells that contain your test scores, just like in Step 2 of the previous section. Press enter, and the variance will be displayed in the selected cell.
The Bottom Line
Calculating variance in Excel may seem intimidating at first, but with the right formula, it’s easy to do. Remember, the VAR function is used for a data set that represents an entire population, while VAR.S and VAR.P are used for sample data or when you have data for an entire population, respectively. By following the steps above, you can find the variance of any set of numbers quickly and accurately.
Understanding Variance and Standard Deviation
Although variance is a useful measure of dispersion, it has one significant drawback. It’s expressed in squared units of measure, which might not be easy to interpret. As a result, it’s common to take the square root of the variance to obtain the standard deviation of a dataset. The standard deviation is a more intuitive measure of spread, expressed in the same units as the original data.
Calculating Standard Deviation in Excel
To calculate the standard deviation in Excel, you can use the STDEV function. STDEV returns the standard deviation of a data set consisting of one or more numbers. The process of using the STDEV function is similar to finding the variance using the VAR function, as we will see in the following steps:
Step 1: Enter Your Data
First, open a new or existing Excel spreadsheet and enter your data into a column or row. For example, let’s say you have a list of salaries:
Salaries ($)  
40000  45000  50000  55000 
Step 2: Use the STDEV Function
Next, select a cell where you want to display the standard deviation and enter the following formula:
=STDEV(A1:A4)
The range A1:A4 refers to the cells that contain your salaries. Press enter, and the standard deviation will be displayed in the selected cell. In this case, the standard deviation is $6,708.20, which tells us that the salaries are spread out with an average distance of $6,708.20 from the mean.
Using Standard Deviation to Identify Outliers
Standard deviation is also helpful in identifying outliers in a dataset. An outlier is an observation that falls significantly outside the range of values represented by the bulk of the data. One way to identify outliers is to find all observations that are more than two standard deviations away from the mean.
Step 1: Calculate the Mean and Standard Deviation
First, calculate the mean and standard deviation of your data set using the AVERAGE and STDEV functions, respectively. For example:
=AVERAGE(A1:A4)
=STDEV(A1:A4)
Assuming that the mean is 47,500, and the standard deviation is 6,708.20 (as in the previous example), the two standard deviation range is calculated as follows:
Lower limit = 47,500  (2 x 6,708.20) = 34,083.60
Upper limit = 47,500 + (2 x 6,708.20) = 60,916.40
Step 2: Identify Outliers
Next, identify all observations that are outside the two standard deviation range. From our example data set, a salary of $40,000 falls outside the lower limit, which makes it an outlier.
The Bottom Line
Excel provides a simple and powerful tool for calculating variance, standard deviation, and identifying outliers. Knowing how to use these functions is essential for anyone who works with data regularly. By following the steps outlined in this post, you can quickly and easily calculate these measures and use them to gain insights into your data.
Frequently Asked Questions
Here are some common questions people ask about finding variance in Excel:
What is the difference between variance and standard deviation?
Variance measures how spread out a dataset is, while standard deviation is the square root of variance. Standard deviation represents the average distance of each value from the mean.
What is the formula for variance in Excel?
The formula for variance in Excel is “=VAR(number1,[number2],…).”
What is the formula for standard deviation in Excel?
The formula for standard deviation in Excel is “=STDEV(number1,[number2],…)”
What is an outlier?
An outlier is a value in a dataset that is significantly different from other values in the same dataset.
How do I identify outliers in Excel?
You can identify outliers in Excel by calculating the two standard deviation range (mean +/ 2 x standard deviation) and identifying any values outside of this range.
Featured Companies

Learn PowerPoint
Explore the world of Microsoft PowerPoint with LearnPowerpoint.io, where we provide tailored tutorials and valuable tips to transform your presentation skills and clarify PowerPoint for enthusiasts and professionals alike.

Learn Word
Your ultimate guide to mastering Microsoft Word! Dive into our extensive collection of tutorials and tips designed to make Word simple and effective for users of all skill levels.

Resultris Marketing
Boost your brand's online presence with Resultris Content Marketing Subscriptions. Enjoy highquality, ondemand content marketing services to grow your business.
Trending
Other Categories
 Basic Excel Operations
 Excel Addins
 Excel and Other Software
 Excel Basics and General Knowledge
 Excel Cell References and Ranges
 Excel Charts and Graphs
 Excel Data Analysis
 Excel Data Manipulation and Transformation
 Excel Data Validation and Conditional Formatting
 Excel Date and Time Functions
 Excel Errors
 Excel File Management
 Excel Formatting and Visual Adjustments
 Excel Formulas and Functions
 Excel Integration and Conversion
 Excel Linking and Merging
 Excel Macros and VBA
 Excel Printing
 Excel Settings
 Excel Tips and Shortcuts
 Excel Training
 Excel Versions
 Form Controls and User Interaction
 How To
 Pivot Tables
 Working with Text