EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Statistical Functions in Excel Excel Variance
 

Excel Variance

Madhuri Thakur
Article byMadhuri Thakur

Updated August 23, 2023

Variance in Excel

 

 

Excel Variance (Table of Contents)
  • Introduction to Variance in Excel
  • How to Calculate Variance in Excel?

Introduction to Variance in Excel

We use Variance when we have some budgets, and we may know the variances observed in implementing the budgets. Sometimes, the term variance calculates the difference between the planned and the actual results. Variance calculation is a great way for data analysis as this lets us know the spread of variation in the data set.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Variance is nothing but information that shows how well the data is spread. Calculating variance is required, especially in cases where we have been sampling data. This is important because we use the correct function to calculate the variance, like VAR.S or VAR.P. We have a few cases to calculate variance in Excel where we have some data that has been projected for the period. We want to compare that with the actual figures.

How to Calculate Variance in Excel?

Let’s understand how to Calculate Variance in Excel with some examples.

You can download this Variance Excel Template here – Variance Excel Template

Example #1 – Calculating variance in Excel for the Entire Population

If the data set is for the complete population, then we need to use the VAR.P function of Excel. This is because, in Excel, we have two functions designed for different datasets.

We may have data collected based on sampling, which might be the entire world’s population.

VAR.P uses the following formula:

VAR.P Formula

Step 1 – Enter the data set in the columns.

variance example 1-1

Step 2 – Insert the VAR.P function and choose the range of the data set. Please note that we should ignore any cell with errors when calculating variance.

variance example 1-2

Step 3 – We will get the variance after pressing the Enter key.

variance example 1-3

We have calculated the variance of Set B by following the same steps above. The result of the variance of Set B is shown below.

variance example 1-4

Example #2 – Calculating the variance for sample size in Excel

If we have data set that represents samples, then we need to use the function of VAR.S instead of using the VAR.P

Considering the sampling method’s characteristics, this function has been designed to calculate the variance.

VAR.S uses the following formula:

VAR.S Formula

Step 1 – Enter the data set in the column.

variance example 2-1

Step 2 – Insert the VAR.S function and choose the range of the data set.

variance example 2-2

Step 3 – We will get the variance.

variance example 2-3

We have calculated the variance of Set B by following the same steps above. The result of the variance of Set B is shown below.

variance example 2-4

Example #3 – Calculating the quantum of variance for data in Excel

We may just want to calculate the variance in the data, and we may need the variance in quantity and not in data analysis.

If we need to check the change, we must use the following method.

Step 1 – Calculate the difference between the two of the data by using the subtraction function.

Calculating the quantum example 3-1

Step 2 – We will get the result after pressing the Enter key. We have to drag the formula applied to cell C2 to get the entire data variance.

Calculating the quantum example 3-2

Step 3 – The variance, which will be the calculated variance, can be positive or negative.

Example #4 – Calculating the percentage of variance for the data set in Excel

We may need to calculate the percentage change in the data over a period of time, and in such cases, we need to use the below method.

Step 1 – First, calculate the variance from method 3rd.

Step 2 – Now calculate the percentage by using the below function.

Change in the value/original value*100. This will be our percentage change in the data set.

variance example 4-1

Step 3 – To get the percentage of the entire data variance, we have to drag the formula applied to cell D2.

variance example 4-2

Things to Remember About Variance in Excel

  • If we have data set that represents the complete population, then we need to use the function of VAR.P.
  • If we have a data set representing the samples from the world data, then we need to use the function of VAR.S.
  • Here S represents the samples.
  • If we calculate the change in terms of quantum, a negative change means an increase in actual value, and a positive change means a decrease in value.
  • When using the VAR.P, the arguments can be numbers or names, arrays, or references containing numbers.
  • If any of the cells given as a reference in the formula contains an error, that cell will be ignored.

Recommended Articles

This is a guide to Variance in Excel. Here we discussed How to calculate Variance in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Top 25 Useful Advanced Excel Formulas and Functions
  2. FLOOR Function in Excel
  3. Excel Square Root Function
  4. Excel Variance

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Variance Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Variance Excel Template

EDUCBA

डाउनलोड Variance Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW