How to Calculate Variance in Excel

This article explains how to calculate Variance and run a Regression Analysis in Excel for Windows and Mac. It includes information on running a regression analysis in Excel Online. These variance functions work in Excel 2019, Excel 2016, and Excel 2010 on Windows; Excel 2016 and Excel 2011 on macOS; and Microsoft Excel on Android and iOS as well as Microsoft 365.

Calculate Sample or Population Variance

Excel provides functions to calculate variance and supports add-ins that enable regression analysis.

Variance indicates how widely a set of numbers diverges from the average of the numbers. When comparing variance calculations, the higher the variance, the more widely distributed are the numbers in a data set. A variance of 0, for example, indicates that all the numbers in the selected data set are the same. (Standard deviation is the square root of the variance and also measures how to spread out a data set is.) You can run a variance on any set of numbers in Excel.

1. In the cell where you want to calculate variance, type: =VAR.S(

   The VAR.S function assumes that the data set is a sample, not the entire population.

2. Then enter the range of cells to include, such as B2:B11. (If you prefer, you may click or tap to select a cell range.)

3. Then type: )

   

The result displays in the cell. The equation should look something like: =VAR.S($B$2:$B$11)

If you are certain you are working with a complete population data set, you may alternatively use the VAR.P function. That would look like: =VAR.P($B$2:$B$11)

Run a Regression Analysis in Excel on Windows or macOS

Regression analysis helps you understand the relationship between variables. It provides analysis that mathematically identifies if and how one variable affects another in a statistically significant way. To run a regression in Excel, you need two sets of numbers, with one set that serves as the Y variable and the other as the X variable. Most often, these numbers are entered in two adjacent columns.

To run regressions on Windows or macOS systems, you need to install the Analysis ToolPak add-in for Excel. The ToolPak works on Excel 2007 or newer on Windows systems and on Excel 2016 or newer on macOS systems.

On recent versions of Microsoft Excel on Windows, type add-in in the search box in Excel and press enter. Then select the result with the gear to the left of the words Add-in that displays. (For other versions of Excel on Windows, select File > Options > Add-Ins. Then in the Manage box, choose Excel Add-ins and Go.) Next, select the check box next to Analysis ToolPak, then select OK.

On macOS versions of Excel, select Tools > Excel Add-ins. Then select the check box next to Analysis ToolPak, and choose OK.

For additional ways to install the Analysis ToolPak, follow Microsoft’s Load the Analysis ToolPak in Excel help page. Once installed, the ToolPak gives you access to data analysis tools.

1. Select the Data tab, then look for the Analysis area and select Data Analysis.

   

2. Select Regression from the list and then choose OK.

   

3. In the Input Y Range field, enter (or select) the range of cells that contain the Y variables. For example, this might be $B$2:$B$10.

   

4. In the Input X Range field, enter (or select) the range of cells that contain the X variables. For example, this might be $A$2:$A$10.

   

5. Optionally, select the box for Labels or make any other adjustments desired to the regression calculation options displayed.

6. In the Output options section, select the output location. Most often, you’ll want to choose the New Worksheet Ply: button, while also leaving the box unfilled.

   

7. Choose OK.

The regression results display in a new sheet.

Run a Regression Analysis in Excel Online

In a browser, including the Safari browser on an iPad, you can do a linear regression in Excel online with the help of an add-on.

1. Open the Excel spreadsheet with your data in a browser using Excel Online.

2. Choose Insert > Office Add-ins.

   

3. In the search box that displays, type “XLMiner Analysis ToolPak” and press enter.

4. Select Add on the XLMiner Analysis ToolPak screen to add several potential data tools on the right side of the screen.

   

5. Select Linear Regression in the XLMiner Analysis ToolPak menu.

   

6. In the Input Y Range field, enter (or select) the range of cells that contain the Y variables. For example, this might be B2:B11.

   

7. In the Input X Range field, enter (or select) the range of cells that contain the X variables. For example, this might be A2:A11.

   

8. Optionally, select the box for Labels or make any other adjustments desired to the regression calculation options displayed.

9. For Output Range, select a cell location far enough to the right or bottom of your sheet to ensure that it is outside the range of other data in your Excel document. For example, if all of your data is in columns A through C, you might enter F2 in the output range box.

   

10. Choose OK.

The regression results display in your Excel sheet, starting at the cell you selected.