Software & Apps MS Office How to Find Variance in Excel Calculating the spread of your data using variance and standard deviation by Jody Emlyn Muelaner Writer Dr. Jody Muelander is a former freelance contributor to Lifewire who's writing has appeared in peer-reviewed journals and aerospace industry reports. our editorial process Facebook Twitter LinkedIn Jody Emlyn Muelaner Updated on September 21, 2020 reviewed by Jessica Kormos Lifewire Tech Review Board Member Jessica Kormos is a writer and editor with 15 years' experience writing articles, copy, and UX content for Tecca.com, Rosenfeld Media, and many others. our review board Article reviewed on Sep 20, 2020 Jessica Kormos MS Office Excel Word Powerpoint Outlook Tweet Share Email A sample of data is often summarized using two statistics: its average value and a measure of how spread out it is. Variance and standard deviation are both measures of how spread out it is. Several functions let you calculate variance in Excel. Below, we'll explain how to decide which one to use, and how to find variance in Excel. Instructions in this article apply to Excel for Microsoft 365, Excel 2019, 2016, 2013, 2010, 2007, and Excel Online. Summarizing Data: Central Tendency and Spread The central tendency tells you where the middle of the data is, or the average value. Some standard measures of the central tendency include the mean, the median, and the mode. The spread of data means how much individual results differ from the average. The most straightforward measure of spread is the range, but it's not very useful because it tends to keep increasing as you sample more data. Variance and standard deviation are much better measures of spread. The variance is simply the standard deviation squared. Standard Deviation and Variance Formula Both the standard deviation and the variance are a way of measuring how far, on average, each data point is from the mean. If you were calculating them by hand, you would start by finding the mean for all your data. You would then find the difference between each observation and the mean, square all those differences, add them all together, then divide by the number of observation. Doing so would give the variance, a kind of average for all the squared differences. Taking the square root of the variance corrects the fact that all the differences were squared, resulting in the standard deviation. You will use it to measure the spread of data. If this is confusing, don't worry. Excel does the actual calculations. Sample or Population? Often your data will be a sample taken from some larger population. You want to use that sample to estimate the variance or standard deviation for the population as a whole. In this case, instead of dividing by the number of observation (n), you divide by n-1. These two different types of calculation have different functions in Excel: Functions with P: Gives the standard deviation for the actual values you have entered. They assume your data is the whole population (dividing by n).Functions with an S: Gives the standard deviation for a whole population, assuming your data is a sample taken from it (dividing by n-1). It can be confusing, as this formula is giving you the estimated variance for the population; the S indicates the dataset is a sample, but the result is for the population. Using the Standard Deviation Formula in Excel To calculate the standard deviation in Excel, follow these steps. Enter your data into Excel. Before you can use the statistics functions in Excel, you need to have all your data in an Excel range: a column, a row, or a group matrix of columns and rows. You need to be able to select all the data without selecting any other values. For the rest of this example, the data is in the range A1:A20. If your data represents the entire population, enter the formula "=STDEV.P(A1:A20)." Alternatively, if your data is a sample from some larger population, enter the formula "=STDEV(A1:A20)." If you're using Excel 2007 or earlier, or you want your file to be compatible with these versions, the formulas are "=STDEVP(A1:A20)," if your data is the entire population; "=STDEV(A1:A20)," if your data is a sample from a larger population. The standard deviation will be displayed in the cell. How to Calculate Variance in Excel Calculating variance is very similar to calculating standard deviation. Ensure your data is in a single range of cells in Excel. If your data represents the entire population, enter the formula "=VAR.P(A1:A20)." Alternatively, if your data is a sample from some larger population, enter the formula "=VAR.S(A1:A20)." If you're using Excel 2007 or earlier, or you want your file to be compatible with these versions, the formulas are: "=VARP(A1:A20)," if your data is the entire population, or "=VAR(A1:A20)," if your data is a sample from a larger population. The variance for your data will be displayed in the cell.