How to Calculate Variance in Excel

Use Excel to run your regression analysis

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.

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.

The following 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

  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: )

    Variance of a set equation, =VAR.S(B2:B11) in Cell B13

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

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.

Add-Ins selected with Analysis ToolPak chosen

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.

    Data Analysis Tools added to Excel
  2. Select Regression from the list and then choose OK.

    Data Analysis > Regression selection in Excel
  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.

    Excel Regression options with Input Y Range highlighted
  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.

    nput Ranges and other Regression analysis options with Input X Range highlighted
  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.

    Excel Regression Output options with New Worksheet Ply selected
  7. Choose OK.

The regression results display in a new sheet.

Screenshot of Regression Analysis results placed in a new Sheet in Excel

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.

    Shows Excel Online Insert menu with Office Add-ins highlighted
  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.

    XLMiner Analysis ToolPak with Add button highlighted
  5. Select Linear Regression in the XLMiner Analysis ToolPak menu.

    XLMiner Analysis ToolPak with Linear Regression chosen
  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.

    Linear Regression fields displayed in the XLMiner Analysis ToolPak with Input Y Range highlighted
  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.

    Linear Regression fields displayed in the XLMiner Analysis ToolPak with Input X Range highlighted
  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.

    Output Range in XLMiner Analysis Toolpak
  10. Choose OK.

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