How to Calculate Variance in Excel

Use Excel to run your regression analysis

Laptop with Excel and Data > Data Analysis > Linear Regression box open, ready to accept selections

Excel provides functions to calculate variance, and also 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 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 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’ll need two sets of numbers, with one set that serves as the Y variable, 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 Office 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: )

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

The result will display in the cell. 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’ll want to install the Analysis ToolPak add-in for Excel. The ToolPak will work on Excel 2007 or newer on Windows systems, and Excel 2016 or newer on macOS systems.

On recent versions of Microsoft Excel on Windows, type “add-in” in the search box in Excel, 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 checkbox next to “Analysis ToolPak”, then select OK.

Screenshots of Search for Add-ins (left) and Selection of Analysis ToolPak (right) with cursor hovering over OK

On macOS versions of Excel, select Tools > Excel Add-ins. Then select the checkbox 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.

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

    Screenshot of Data Analysis > Regression selection
  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.

    Screenshot shows Input Ranges and other Regression analysis options
  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.

  8. Your regression results will 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 spreadsheet with your data in your browser.

  2. Choose Insert > Office Add-ins.

    Shows Excel Online, Insert menu with Office Add-ins visible
  3. In the search box that displays, type “XLMiner Analysis ToolPak” and press enter.

    Screenshot of XLMiner Analysis ToolPak with Add button displayed
  4. Select Add. Several potential data tools should display on the right-side portion of the screen.

  5. Select Linear Regression.

    XLMiner Analysis ToolPak displayed on right-side of Excel screen, with Linear Regression highlighted (with cursor hover)
  6. 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.

  7. 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.

    Linear Regression fields displayed in the XLMiner Analysis ToolPak
  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 all other data in your Excel document. For example, if all of your data is in columns A through C, you might enter $F$1 in the output range box.

  10. Choose OK.

  11. Your regression results will display in your Excel sheet, starting at the cell you selected.