Written and maintained by CASRAI Editorial Board
Published · Last updated
On this page
To find covariance in Excel, use =COVARIANCE.S(array1, array2) for a sample or =COVARIANCE.P(array1, array2) for a whole population. Both take two equal-length ranges of numbers and return one value: the average of the products of each pair’s deviations from its own mean. Pick the function that matches your data, because the two differ by the divisor (n−1 versus n). This page is for researchers and analysts who have two columns of paired measurements and need a covariance they can check by hand.
Ask CASRAI · free to tryQuestion about this guide? Ask CASRAI answers with linked sources. 2 free questions a day, no account.
What covariance is used for in research
Covariance measures whether two variables tend to move together. A positive value means that when one variable is above its mean the other tends to be above its mean too; a negative value means they tend to move in opposite directions; a value near zero means no consistent linear co-movement. Covariance carries the units of both variables multiplied together, so its size is hard to compare across studies. When you need a unit-free measure, scale it into a correlation: see correlation coefficient in SPSS and correlation coefficient vs coefficient of determination.
Which function to use: COVARIANCE.S or COVARIANCE.P
| Function | Use when | Microsoft’s description | Worked example from Microsoft |
|---|---|---|---|
COVARIANCE.S |
Your data are a sample drawn from a larger population (most research data) | Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets | =COVARIANCE.S({2,4,8},{5,11,12}) returns 9.666666667 |
COVARIANCE.P |
Your data are the entire population of interest | Returns population covariance, the average of the products of deviations for each data point pair in two data sets | For data1 = 3, 2, 4, 5, 6 and data2 = 9, 7, 12, 15, 17, =COVARIANCE.P(A2:A6,B2:B6) returns 5.2 |
The choice of “sample” or “population” follows from your design, not from Excel. Checking the arithmetic for the first example: the deviations of x from its mean are about −2.667, −0.667 and 3.333, those of y are about −4.333, 1.667 and 2.667, and the products sum to about 19.333. Dividing by n−1 = 2 gives 9.667 (the sample value); dividing by n = 3 would give about 6.444 (the population value).
Access: which Excel versions have the functions
According to Microsoft’s documentation (read 2026-11-12), COVARIANCE.S is available in Microsoft 365 for Windows and Mac, Excel 2024, 2021, 2019 and 2016, and Excel for the web. COVARIANCE.P is documented for Microsoft 365, Excel 2024, 2021, 2019 and 2016. Neither needs an add-in. The Analysis ToolPak, which has its own Covariance tool for producing a whole matrix, is a separate add-in; enabling it is covered in our Excel Analysis ToolPak guide.
Step by step: how to calculate covariance in Excel
How to compute covariance in Excel from two columns
- Enter the two variables in adjacent columns with one observation per row, for example X in A2:A6 and Y in B2:B6. Each row must be a matched pair.
- Click an empty cell for the result.
- Type
=COVARIANCE.S(A2:A6,B2:B6)for a sample, or=COVARIANCE.P(A2:A6,B2:B6)for a population, and press Enter. - Compare the result with your expectation of sign and order of magnitude (see the next section).
Excel covariance formula with typed-in values
The arguments can be ranges, named ranges, references or array constants, so =COVARIANCE.S({2,4,8},{5,11,12}) works without any cells. Microsoft’s example returns 9.666666667, and the same data entered in A3:A5 and B3:B5 with =COVARIANCE.S(A3:A5,B3:B5) gives the identical value.
Output: how to read the result
- Sign: positive means the variables tend to rise together; negative means one tends to rise as the other falls.
- Magnitude: depends on the units and spread of both variables, so 5.2 is not “strong” or “weak” on its own. Convert to a correlation to compare across datasets.
- Self-check: reproduce Microsoft’s published examples (9.666666667 for the sample case, 5.2 for the population case) in a blank sheet before applying the function to your own data.
Troubleshooting
| What you see | Cause (per Microsoft’s documentation) |
|---|---|
#N/A |
The two arrays contain different numbers of data points. |
#DIV/0! |
Either array is empty, or (for COVARIANCE.S) there is only one data point. |
| Result looks wrong with text or blanks in the range | Text, logical values and empty cells in a reference are ignored, but cells containing the value 0 are counted. Zeros that actually mean “missing” will distort the result. |
Free alternatives
If you do not have Excel, the same quantity can be computed in free tools. Check each tool’s own documentation for whether it divides by n or n−1 before comparing numbers with Excel’s COVARIANCE.S or COVARIANCE.P.
| Tool | Function | What the documentation says | Source (read 2026-11-12) |
|---|---|---|---|
| Google Sheets | COVAR(data_y, data_x) |
Calculates the covariance of a dataset. Text in the value arguments is ignored. Google notes that covariance magnitude is hard to interpret and points to CORREL or PEARSON as normalized versions. |
Google Docs Editors Help, COVAR |
| R | cov(x, y) |
Returns the covariance of two vectors, or a covariance matrix for matrices and data frames. The denominator is n−1, so it matches the sample covariance (COVARIANCE.S), not the population version. Missing values are controlled by the use argument. See also our guide to cor() in R. |
R documentation, cor/cov |
For other spreadsheet statistics, see how to find a slope in Excel.
FAQ
What is the covariance formula in Excel?
There is no operator, only two functions: =COVARIANCE.S(array1,array2) for samples and =COVARIANCE.P(array1,array2) for populations.
How do I find covariance on Excel for more than two variables?
The two functions compare one pair at a time. Use one formula per pair, or the Covariance tool in the Analysis ToolPak (set up as described in our ToolPak guide).
Why does Excel return #N/A?
The two ranges have different lengths. Make both cover the same number of rows.
Is Excel covariance the same as correlation?
No. Covariance keeps the original units; correlation rescales it to a unit-free value between −1 and 1.
Sources
- Microsoft Support, COVARIANCE.S function (read 2026-11-12).
- Microsoft Support, COVARIANCE.P function (read 2026-11-12).
- Google Docs Editors Help, COVAR (read 2026-11-12).
- R Documentation, cor, var, cov (read 2026-11-12).







