Software & Apps MS Office How to Calculate Variance in Excel Use Excel to run your regression analysis Share Pin Email Print MS Office Excel Word Powerpoint Outlook By Andy Wolber Freelance Contributor Andy Wolber is a former Lifewire writer who has been writing about technology for 15+ years. His focus is G Suite, iOS, and nonprofit sector apps. our editorial process Twitter LinkedIn Andy Wolber Updated January 21, 2020 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 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’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 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. 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.) Then type: ) 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. 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. Select the Data tab, then look for the Analysis area and select Data Analysis. Select Regression from the list, then choose OK. 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. 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. Optionally, select the box for Labels, or make any other adjustments desired to the regression calculation options displayed. 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. Choose OK. Your regression results will 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. Open the spreadsheet with your data in your browser. Choose Insert > Office Add-ins. In the search box that displays, type “XLMiner Analysis ToolPak” and press enter. Select Add. Several potential data tools should display on the right-side portion of the screen. Select Linear Regression. 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. 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. Optionally, select the box for Labels, or make any other adjustments desired to the regression calculation options displayed. 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. Choose OK. Your regression results will display in your Excel sheet, starting at the cell you selected.